- Patch #299178 by Crell et al: add support for subqueries in FROM and JOIN clauses in dynamic query. Cool feature/syntax.

2 files changed, 67 insertions, 0 deletions

diff --git a/modules/simpletest/tests/database_test.module b/modules/simpletest/tests/database_test.module
index 067e684ca..555f976b3 100644
--- a/modules/simpletest/tests/database_test.module
+++ b/modules/simpletest/tests/database_test.module
@@ -59,3 +59,4 @@ function database_test_db_query_temporary() {
   print db_query('SELECT COUNT(*) FROM temporary')->fetchField();
   exit;
 }
+
diff --git a/modules/simpletest/tests/database_test.test b/modules/simpletest/tests/database_test.test
index 82ce6512a..66087f49b 100644
--- a/modules/simpletest/tests/database_test.test
+++ b/modules/simpletest/tests/database_test.test
@@ -1100,6 +1100,72 @@ class DatabaseSelectTestCase extends DatabaseTestCase {
 }
 
 /**
+ * Test case for subselects in a dynamic SELECT query.
+ */
+class DatabaseSelectSubqueryTestCase extends DatabaseTestCase {
+
+  function getInfo() {
+    return array(
+      'name' => t('Select tests, subqueries'),
+      'description' => t('Test the Select query builder.'),
+      'group' => t('Database'),
+    );
+  }
+
+  /**
+   * Test that we can use a subquery in a FROM clause.
+   */
+  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.'));
+  }
+
+  /**
+   * Test that we can use a subquery in a JOIN clause.
+   */
+  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, t('Returned the correct number of rows.'));
+  }
+}
+
+/**
  * Test select with order by clauses.
  */
 class DatabaseSelectOrderedTestCase extends DatabaseTestCase {