diff options
| -rw-r--r-- | includes/database/database.inc | 3 | ||||
| -rw-r--r-- | includes/database/select.inc | 50 | ||||
| -rw-r--r-- | modules/simpletest/tests/database_test.module | 1 | ||||
| -rw-r--r-- | modules/simpletest/tests/database_test.test | 66 |
4 files changed, 101 insertions, 19 deletions
diff --git a/includes/database/database.inc b/includes/database/database.inc index bd154e626..9dc3d78dd 100644 --- a/includes/database/database.inc +++ b/includes/database/database.inc @@ -1687,7 +1687,8 @@ function db_delete($table, array $options = array()) { * Returns a new SelectQuery object for the active database. * * @param $table - * The base table for this query. + * The base table for this query. May be a string or another SelectQuery + * object. If a query object is passed, it will be used as a subselect. * @param $alias * The alias for the base table of this query. * @param $options diff --git a/includes/database/select.inc b/includes/database/select.inc index 218650b16..b9aa2fcf0 100644 --- a/includes/database/select.inc +++ b/includes/database/select.inc @@ -33,13 +33,16 @@ class SelectQuery extends Query implements QueryConditionInterface, QueryAlterab * * array( * 'type' => $join_type (one of INNER, LEFT OUTER, RIGHT OUTER), - * 'table' => $name_of_table, + * 'table' => $table, * 'alias' => $alias_of_the_table, * 'condition' => $condition_clause_on_which_to_join, * 'arguments' => $array_of_arguments_for_placeholders_in_the condition. * 'all_fields' => TRUE to SELECT $alias.*, FALSE or NULL otherwise. * ) * + * If $table is a string, it is taken as the name of a table. If it is + * a SelectQuery object, it is taken as a subquery. + * * @var array */ protected $tables = array(); @@ -262,7 +265,8 @@ class SelectQuery extends Query implements QueryConditionInterface, QueryAlterab /** * Compiles and returns an associative array of the arguments for this prepared statement. * - * @return array + * @return + * An associative array of all placeholder arguments for this query. */ public function getArguments() { $this->where->compile($this->connection); @@ -272,6 +276,10 @@ class SelectQuery extends Query implements QueryConditionInterface, QueryAlterab if ($table['arguments']) { $args += $table['arguments']; } + // If this table is a subquery, grab its arguments recursively. + if ($table['table'] instanceof SelectQuery) { + $args += $table['table']->getArguments(); + } } foreach ($this->expressions as $expression) { if ($expression['arguments']) { @@ -285,19 +293,7 @@ class SelectQuery extends Query implements QueryConditionInterface, QueryAlterab public function execute() { drupal_alter('query', $this); - $this->where->compile($this->connection); - $this->having->compile($this->connection); - $args = $this->where->arguments() + $this->having->arguments(); - foreach ($this->tables as $table) { - if ($table['arguments']) { - $args += $table['arguments']; - } - } - foreach ($this->expressions as $expression) { - if ($expression['arguments']) { - $args += $expression['arguments']; - } - } + $args = $this->getArguments(); if (!empty($this->range)) { return $this->connection->queryRange((string)$this, $args, $this->range['start'], $this->range['length'], $this->queryOptions); @@ -548,8 +544,11 @@ class SelectQuery extends Query implements QueryConditionInterface, QueryAlterab * In some cases, that may include dipping into the Schema API to find the necessary * fields on which to join. * + * @param $type + * The type of join. Typically one one of INNER, LEFT OUTER, and RIGHT OUTER. * @param $table - * The table against which to join. + * The table against which to join. May be a string or another SelectQuery + * object. If a query object is passed, it will be used as a subselect. * @param $alias * The alias for the table. In most cases this should be the first letter * of the table, or the first letter of each "word" in the table. If omitted, @@ -568,7 +567,12 @@ class SelectQuery extends Query implements QueryConditionInterface, QueryAlterab public function addJoin($type, $table, $alias = NULL, $condition = NULL, $arguments = array()) { if (empty($alias)) { - $alias = $table; + if ($table instanceof SelectQuery) { + $alias = 'subquery'; + } + else { + $alias = $table; + } } $alias_candidate = $alias; @@ -699,9 +703,19 @@ class SelectQuery extends Query implements QueryConditionInterface, QueryAlterab if (isset($table['join type'])) { $query .= $table['join type'] . ' JOIN '; } + + // If the table is a subquery, compile it and integrate it into this query. + if ($table['table'] instanceof SelectQuery) { + $table_string = '(' . (string)$table['table'] .')'; + } + else { + $table_string = '{' . $this->connection->escapeTable($table['table']) . '}'; + } + // Don't use the AS keyword for table aliases, as some // databases don't support it (e.g., Oracle). - $query .= '{' . $this->connection->escapeTable($table['table']) . '} ' . $table['alias']; + $query .= $table_string . ' ' . $table['alias']; + if (!empty($table['condition'])) { $query .= ' ON ' . $table['condition']; } 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 { |