function DatabaseSelectSubqueryTestCase::testFromSubquerySelect

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

File

core/modules/simpletest/tests/database_test.test, line 1682

Database tests.

Class

DatabaseSelectSubqueryTestCase

Test case for sub-selects 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);

  for ($i = 0; $i < 2; $i++) {
    // 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');
    if ($i) {
      // Use a different number of conditions here to confuse the subquery
      // placeholder counter, testing http://drupal.org/node/1112854.
      $select->condition('name', 'John');
    }
    $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, 'Returned the correct number of rows.');
  }
}