function DatabaseSelectSubqueryTestCase::testConditionSubquerySelect

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

File

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

Database tests.

Class

DatabaseSelectSubqueryTestCase

Test case for sub-selects in a dynamic SELECT query.

Code

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

  // Create another query that joins against the virtual table resulting
  // from the subquery.
  $select = db_select('test_task', 'tt2');
  $select->addField('tt2', 'task');
  $select->condition('tt2.pid', $subquery, 'IN');

  // The resulting query should be equivalent to:
  // SELECT tt2.name
  // FROM test tt2
  // WHERE tt2.pid IN (SELECT tt.pid AS pid FROM test_task tt WHERE tt.priority=1)
  $people = $select->execute()->fetchCol();
  $this->assertEqual(count($people), 5, 'Returned the correct number of rows.');

  // Create a subquery that only returns one value
  $subquery2 = db_select('test_task', 'tt');
  $subquery2->addField('tt', 'priority');
  $subquery2->condition('tt.task', 'eat'); // Gives 3

  // Create another query that tests against the value returned by the subquery
  $select2 = db_select('test_task', 'tt2');
  $select2->addField('tt2', 'task');
  $select2->condition('tt2.priority', $subquery2, '<');

  $tasks = $select2->execute()->fetchCol();
  $this->assertEqual(count($tasks), 4, 'Returned the correct number of rows.');
}