diff options
| -rw-r--r-- | includes/database/select.inc | 114 | ||||
| -rw-r--r-- | modules/simpletest/tests/database_test.test | 48 |
2 files changed, 161 insertions, 1 deletions
diff --git a/includes/database/select.inc b/includes/database/select.inc index e8d418ea7..a4c4ed3e4 100644 --- a/includes/database/select.inc +++ b/includes/database/select.inc @@ -112,6 +112,25 @@ interface SelectQueryInterface extends QueryConditionInterface, QueryAlterableIn public function &getTables(); /** + * Returns a reference to the union queries for this query. This include + * queries for UNION, UNION ALL, and UNION DISTINCT. + * + * Because this method returns by reference, alter hooks may edit the tables + * array directly to make their changes. If just adding union queries, + * however, the use of the union() method is preferred. + * + * Note that this method must be called by reference as well: + * + * @code + * $fields =& $query->getUnion(); + * @endcode + * + * @return + * A reference to the union query array structure. + */ + public function &getUnion(); + + /** * Compiles and returns an associative array of the arguments for this prepared statement. * * @param $queryPlaceholder @@ -345,6 +364,31 @@ interface SelectQueryInterface extends QueryConditionInterface, QueryAlterableIn public function range($start = NULL, $length = NULL); /** + * Add another Select query to UNION to this one. + * + * Union queries consist of two or more queries whose + * results are effectively concatenated together. Queries + * will be UNIONed in the order they are specified, with + * this object's query coming first. Duplicate columns will + * be discarded. All forms of UNION are supported, using + * the second '$type' argument. + * + * Note: All queries UNIONed together must have the same + * field structure, in the same order. It is up to the + * caller to ensure that they match properly. If they do + * not, an SQL syntax error will result. + * + * @param $query + * The query to UNION to this query. + * @param $type + * The type of UNION to add to the query. Defaults to plain + * UNION. + * @return + * The called object. + */ + public function union(SelectQueryInterface $query, $type = ''); + + /** * Groups the result set by the specified field. * * @param $field @@ -524,6 +568,10 @@ class SelectQueryExtender implements SelectQueryInterface { return $this->query->getTables(); } + public function &getUnion() { + return $this->query->getUnion(); + } + public function getArguments(QueryPlaceholderInterface $queryPlaceholder = NULL) { return $this->query->getArguments($queryPlaceholder); } @@ -600,6 +648,11 @@ class SelectQueryExtender implements SelectQueryInterface { return $this; } + public function union(SelectQueryInterface $query, $type = '') { + $this->query->union($query, $type); + return $this; + } + public function groupBy($field) { $this->query->groupBy($field); return $this; @@ -766,6 +819,19 @@ class SelectQuery extends Query implements SelectQueryInterface { protected $range; /** + * An array whose elements specify a query to UNION, and the UNION type. The + * 'type' key may be '', 'ALL', or 'DISTINCT' to represent a 'UNION', + * 'UNION ALL', or 'UNION DISTINCT' statement, respectively. + * + * All entries in this array will be applied from front to back, with the + * first query to union on the right of the original query, the second union + * to the right of the first, etc. + * + * @var array + */ + protected $union = array(); + + /** * Indicates if preExecute() has already been called. * @var boolean */ @@ -910,6 +976,10 @@ class SelectQuery extends Query implements SelectQueryInterface { return $this->tables; } + public function &getUnion() { + return $this->union; + } + public function getArguments(QueryPlaceholderInterface $queryPlaceholder = NULL) { if (!isset($queryPlaceholder)) { $queryPlaceholder = $this; @@ -917,6 +987,7 @@ class SelectQuery extends Query implements SelectQueryInterface { $this->where->compile($this->connection, $queryPlaceholder); $this->having->compile($this->connection, $queryPlaceholder); $args = $this->where->arguments() + $this->having->arguments(); + foreach ($this->tables as $table) { if ($table['arguments']) { $args += $table['arguments']; @@ -926,12 +997,19 @@ class SelectQuery extends Query implements SelectQueryInterface { $args += $table['table']->getArguments($queryPlaceholder); } } + foreach ($this->expressions as $expression) { if ($expression['arguments']) { $args += $expression['arguments']; } } + // If there are any dependent queries to UNION, + // incorporate their arguments recursively. + foreach ($this->union as $union) { + $args += $union['query']->getArguments($queryPlaceholder); + } + return $args; } @@ -1109,6 +1187,28 @@ class SelectQuery extends Query implements SelectQueryInterface { return $this; } + public function union(SelectQueryInterface $query, $type = '') { + // Handle UNION aliasing. + switch ($type) { + // Fold UNION DISTINCT to UNION for better cross database support. + case 'DISTINCT': + case '': + $type = 'UNION'; + break; + + case 'ALL': + $type = 'UNION ALL'; + default: + } + + $this->union[] = array( + 'type' => $type, + 'query' => $query, + ); + + return $this; + } + public function groupBy($field) { $this->group[] = $field; return $this; @@ -1223,16 +1323,28 @@ class SelectQuery extends Query implements SelectQueryInterface { } // RANGE is database specific, so we can't do it here. + + // UNION is a little odd, as the select queries to combine are passed into + // this query, but syntactically they all end up on the same level. + if ($this->union) { + foreach ($this->union as $union) { + $query .= ' ' . $union['type'] . ' ' . (string) $union['query']; + } + } + return $query; } public function __clone() { - // On cloning, also clone the conditional objects. However, we do not + // On cloning, also clone the dependent objects. However, we do not // want to clone the database connection object as that would duplicate the // connection itself. $this->where = clone($this->where); $this->having = clone($this->having); + foreach ($this->union as $key => $aggregate) { + $this->union[$key]['query'] = clone($aggregate['query']); + } } } diff --git a/modules/simpletest/tests/database_test.test b/modules/simpletest/tests/database_test.test index d3c972742..8c1bb1dc5 100644 --- a/modules/simpletest/tests/database_test.test +++ b/modules/simpletest/tests/database_test.test @@ -1352,6 +1352,54 @@ class DatabaseSelectTestCase extends DatabaseTestCase { $this->assertEqual($names[0], 'Gonzo', t('Correct record returned for NOT NULL age.')); $this->assertEqual($names[1], 'Kermit', t('Correct record returned for NOT NULL age.')); } + + /** + * Test that we can UNION multiple Select queries together. This is + * semantically equal to UNION DISTINCT, so we don't explicity test that. + */ + function testUnion() { + $query_1 = db_select('test', 't') + ->fields('t', array('name')) + ->condition('age', array(27, 28), 'IN'); + + $query_2 = db_select('test', 't') + ->fields('t', array('name')) + ->condition('age', 28); + + $query_1->union($query_2); + + $names = $query_1->execute()->fetchCol(); + + // Ensure we only get 2 records. + $this->assertEqual(count($names), 2, t('UNION correctly discarded duplicates.')); + + $this->assertEqual($names[0], 'George', t('First query returned correct name.')); + $this->assertEqual($names[1], 'Ringo', t('Second query returned correct name.')); + } + + /** + * Test that we can UNION ALL multiple Select queries together. + */ + function testUnionAll() { + $query_1 = db_select('test', 't') + ->fields('t', array('name')) + ->condition('age', array(27, 28), 'IN'); + + $query_2 = db_select('test', 't') + ->fields('t', array('name')) + ->condition('age', 28); + + $query_1->union($query_2, 'ALL'); + + $names = $query_1->execute()->fetchCol(); + + // Ensure we get all 3 records. + $this->assertEqual(count($names), 3, t('UNION ALL correctly preserved duplicates.')); + + $this->assertEqual($names[0], 'George', t('First query returned correct first name.')); + $this->assertEqual($names[1], 'Ringo', t('Second query returned correct second name.')); + $this->assertEqual($names[2], 'Ringo', t('Third query returned correct name.')); + } } /** |