Test that we can use a subquery in a JOIN clause.

File

modules/simpletest/tests/database_test.test, line 1951

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.');
}

---