function DatabaseSelectSubqueryTestCase::testJoinSubquerySelect in Drupal 7
Test that we can use a subquery in a JOIN clause.
File
- modules/
simpletest/ tests/ database_test.test, line 1826
Class
- DatabaseSelectSubqueryTestCase
- Test case for subselects in a dynamic SELECT query.
Code
function testJoinSubquerySelect() {
// Create a subquery, which is just a normal query object.
$subquery = db_select('test_task', 'tt');
$subquery
->addField('tt', 'pid', 'pid');
$subquery
->condition('priority', 1);
// Create another query that joins against the virtual table resulting
// from the subquery.
$select = db_select('test', 't');
$select
->join($subquery, 'tt', 't.id=tt.pid');
$select
->addField('t', 'name');
// The resulting query should be equivalent to:
// SELECT t.name
// FROM test t
// INNER JOIN (SELECT tt.pid AS pid FROM test_task tt WHERE priority=1) tt ON t.id=tt.pid
$people = $select
->execute()
->fetchCol();
$this
->assertEqual(count($people), 2, 'Returned the correct number of rows.');
}