function DatabaseSelectSubqueryTestCase::testFromSubquerySelect in SimpleTest 7

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

File

tests/database_test.test, line 1487

Class

DatabaseSelectSubqueryTestCase

Test case for subselects in a dynamic SELECT query.

Code

function testFromSubquerySelect() {

  // Create a subquery, which is just a normal query object.
  $subquery = db_select('test_task', 'tt');
  $subquery
    ->addField('tt', 'pid', 'pid');
  $subquery
    ->addField('tt', 'task', 'task');
  $subquery
    ->condition('priority', 1);

  // Create another query that joins against the virtual table resulting
  // from the subquery.
  $select = db_select($subquery, 'tt2');
  $select
    ->join('test', 't', 't.id=tt2.pid');
  $select
    ->addField('t', 'name');
  $select
    ->condition('task', 'code');

  // The resulting query should be equivalent to:
  // SELECT t.name
  // FROM (SELECT tt.pid AS pid, tt.task AS task FROM test_task tt WHERE priority=1) tt
  //   INNER JOIN test t ON t.id=tt.pid
  // WHERE tt.task = 'code'
  $people = $select
    ->execute()
    ->fetchCol();
  $this
    ->assertEqual(count($people), 1, t('Returned the correct number of rows.'));
}