3 namespace Drupal\Core\Database\Query;
5 use Drupal\Core\Database\Database;
6 use Drupal\Core\Database\Connection;
9 * Query builder for SELECT statements.
13 class Select extends Query implements SelectInterface {
15 use QueryConditionTrait;
18 * The fields to SELECT.
22 protected $fields = [];
25 * The expressions to SELECT as virtual fields.
29 protected $expressions = [];
32 * The tables against which to JOIN.
34 * This property is a nested array. Each entry is an array representing
35 * a single table against which to join. The structure of each entry is:
38 * 'type' => $join_type (one of INNER, LEFT OUTER, RIGHT OUTER),
40 * 'alias' => $alias_of_the_table,
41 * 'condition' => $join_condition (string or Condition object),
42 * 'arguments' => $array_of_arguments_for_placeholders_in_the condition.
43 * 'all_fields' => TRUE to SELECT $alias.*, FALSE or NULL otherwise.
46 * If $table is a string, it is taken as the name of a table. If it is
47 * a Select query object, it is taken as a subquery.
49 * If $join_condition is a Condition object, any arguments should be
50 * incorporated into the object; a separate array of arguments does not
51 * need to be provided.
55 protected $tables = [];
58 * The fields by which to order this query.
60 * This is an associative array. The keys are the fields to order, and the value
61 * is the direction to order, either ASC or DESC.
65 protected $order = [];
68 * The fields by which to group.
72 protected $group = [];
75 * The conditional object for the HAVING clause.
77 * @var \Drupal\Core\Database\Query\Condition
82 * Whether or not this query should be DISTINCT
86 protected $distinct = FALSE;
89 * The range limiters for this query.
96 * An array whose elements specify a query to UNION, and the UNION type. The
97 * 'type' key may be '', 'ALL', or 'DISTINCT' to represent a 'UNION',
98 * 'UNION ALL', or 'UNION DISTINCT' statement, respectively.
100 * All entries in this array will be applied from front to back, with the
101 * first query to union on the right of the original query, the second union
102 * to the right of the first, etc.
106 protected $union = [];
109 * Indicates if preExecute() has already been called.
112 protected $prepared = FALSE;
115 * The FOR UPDATE status
119 protected $forUpdate = FALSE;
122 * Constructs a Select object.
124 * @param string $table
125 * The name of the table that is being queried.
126 * @param string $alias
127 * The alias for the table.
128 * @param \Drupal\Core\Database\Connection $connection
129 * Database connection object.
130 * @param array $options
131 * Array of query options.
133 public function __construct($table, $alias, Connection $connection, $options = []) {
134 $options['return'] = Database::RETURN_STATEMENT;
135 parent::__construct($connection, $options);
136 $conjunction = isset($options['conjunction']) ? $options['conjunction'] : 'AND';
137 $this->condition = new Condition($conjunction);
138 $this->having = new Condition($conjunction);
139 $this->addJoin(NULL, $table, $alias);
145 public function addTag($tag) {
146 $this->alterTags[$tag] = 1;
153 public function hasTag($tag) {
154 return isset($this->alterTags[$tag]);
160 public function hasAllTags() {
161 return !(boolean) array_diff(func_get_args(), array_keys($this->alterTags));
167 public function hasAnyTag() {
168 return (boolean) array_intersect(func_get_args(), array_keys($this->alterTags));
174 public function addMetaData($key, $object) {
175 $this->alterMetaData[$key] = $object;
182 public function getMetaData($key) {
183 return isset($this->alterMetaData[$key]) ? $this->alterMetaData[$key] : NULL;
189 public function arguments() {
190 if (!$this->compiled()) {
194 $args = $this->condition->arguments() + $this->having->arguments();
196 foreach ($this->tables as $table) {
197 if ($table['arguments']) {
198 $args += $table['arguments'];
200 // If this table is a subquery, grab its arguments recursively.
201 if ($table['table'] instanceof SelectInterface) {
202 $args += $table['table']->arguments();
204 // If the join condition is an object, grab its arguments recursively.
205 if (!empty($table['condition']) && $table['condition'] instanceof ConditionInterface) {
206 $args += $table['condition']->arguments();
210 foreach ($this->expressions as $expression) {
211 if ($expression['arguments']) {
212 $args += $expression['arguments'];
216 // If there are any dependent queries to UNION,
217 // incorporate their arguments recursively.
218 foreach ($this->union as $union) {
219 $args += $union['query']->arguments();
228 public function compile(Connection $connection, PlaceholderInterface $queryPlaceholder) {
229 $this->condition->compile($connection, $queryPlaceholder);
230 $this->having->compile($connection, $queryPlaceholder);
232 foreach ($this->tables as $table) {
233 // If this table is a subquery, compile it recursively.
234 if ($table['table'] instanceof SelectInterface) {
235 $table['table']->compile($connection, $queryPlaceholder);
237 // Make sure join conditions are also compiled.
238 if (!empty($table['condition']) && $table['condition'] instanceof ConditionInterface) {
239 $table['condition']->compile($connection, $queryPlaceholder);
243 // If there are any dependent queries to UNION, compile it recursively.
244 foreach ($this->union as $union) {
245 $union['query']->compile($connection, $queryPlaceholder);
252 public function compiled() {
253 if (!$this->condition->compiled() || !$this->having->compiled()) {
257 foreach ($this->tables as $table) {
258 // If this table is a subquery, check its status recursively.
259 if ($table['table'] instanceof SelectInterface) {
260 if (!$table['table']->compiled()) {
264 if (!empty($table['condition']) && $table['condition'] instanceof ConditionInterface) {
265 if (!$table['condition']->compiled()) {
271 foreach ($this->union as $union) {
272 if (!$union['query']->compiled()) {
283 public function havingCondition($field, $value = NULL, $operator = NULL) {
284 $this->having->condition($field, $value, $operator);
291 public function &havingConditions() {
292 return $this->having->conditions();
298 public function havingArguments() {
299 return $this->having->arguments();
305 public function having($snippet, $args = []) {
306 $this->having->where($snippet, $args);
313 public function havingCompile(Connection $connection) {
314 $this->having->compile($connection, $this);
320 public function extend($extender_name) {
321 $override_class = $extender_name . '_' . $this->connection->driver();
322 if (class_exists($override_class)) {
323 $extender_name = $override_class;
325 return new $extender_name($this, $this->connection);
331 public function havingIsNull($field) {
332 $this->having->isNull($field);
339 public function havingIsNotNull($field) {
340 $this->having->isNotNull($field);
347 public function havingExists(SelectInterface $select) {
348 $this->having->exists($select);
355 public function havingNotExists(SelectInterface $select) {
356 $this->having->notExists($select);
363 public function forUpdate($set = TRUE) {
365 $this->forUpdate = $set;
373 public function &getFields() {
374 return $this->fields;
380 public function &getExpressions() {
381 return $this->expressions;
387 public function &getOrderBy() {
394 public function &getGroupBy() {
401 public function &getTables() {
402 return $this->tables;
408 public function &getUnion() {
415 public function escapeLike($string) {
416 return $this->connection->escapeLike($string);
422 public function escapeField($string) {
423 return $this->connection->escapeField($string);
429 public function getArguments(PlaceholderInterface $queryPlaceholder = NULL) {
430 if (!isset($queryPlaceholder)) {
431 $queryPlaceholder = $this;
433 $this->compile($this->connection, $queryPlaceholder);
434 return $this->arguments();
440 public function isPrepared() {
441 return $this->prepared;
447 public function preExecute(SelectInterface $query = NULL) {
448 // If no query object is passed in, use $this.
449 if (!isset($query)) {
453 // Only execute this once.
454 if ($query->isPrepared()) {
458 // Modules may alter all queries or only those having a particular tag.
459 if (isset($this->alterTags)) {
460 // Many contrib modules as well as Entity Reference in core assume that
461 // query tags used for access-checking purposes follow the pattern
462 // $entity_type . '_access'. But this is not the case for taxonomy terms,
463 // since the core Taxonomy module used to add term_access instead of
464 // taxonomy_term_access to its queries. Provide backwards compatibility
465 // by adding both tags here instead of attempting to fix all contrib
466 // modules in a coordinated effort.
468 // - Extract this mechanism into a hook as part of a public (non-security)
470 // - Emit E_USER_DEPRECATED if term_access is used.
471 // https://www.drupal.org/node/2575081
472 $term_access_tags = ['term_access' => 1, 'taxonomy_term_access' => 1];
473 if (array_intersect_key($this->alterTags, $term_access_tags)) {
474 $this->alterTags += $term_access_tags;
477 foreach ($this->alterTags as $tag => $value) {
478 $hooks[] = 'query_' . $tag;
480 \Drupal::moduleHandler()->alter($hooks, $query);
483 $this->prepared = TRUE;
485 // Now also prepare any sub-queries.
486 foreach ($this->tables as $table) {
487 if ($table['table'] instanceof SelectInterface) {
488 $table['table']->preExecute();
492 foreach ($this->union as $union) {
493 $union['query']->preExecute();
496 return $this->prepared;
502 public function execute() {
503 // If validation fails, simply return NULL.
504 // Note that validation routines in preExecute() may throw exceptions instead.
505 if (!$this->preExecute()) {
509 $args = $this->getArguments();
510 return $this->connection->query((string) $this, $args, $this->queryOptions);
516 public function distinct($distinct = TRUE) {
517 $this->distinct = $distinct;
524 public function addField($table_alias, $field, $alias = NULL) {
525 // If no alias is specified, first try the field name itself.
530 // If that's already in use, try the table name and field name.
531 if (!empty($this->fields[$alias])) {
532 $alias = $table_alias . '_' . $field;
535 // If that is already used, just add a counter until we find an unused alias.
536 $alias_candidate = $alias;
538 while (!empty($this->fields[$alias_candidate])) {
539 $alias_candidate = $alias . '_' . $count++;
541 $alias = $alias_candidate;
543 $this->fields[$alias] = [
545 'table' => $table_alias,
555 public function fields($table_alias, array $fields = []) {
557 foreach ($fields as $field) {
558 // We don't care what alias was assigned.
559 $this->addField($table_alias, $field);
563 // We want all fields from this table.
564 $this->tables[$table_alias]['all_fields'] = TRUE;
573 public function addExpression($expression, $alias = NULL, $arguments = []) {
575 $alias = 'expression';
578 $alias_candidate = $alias;
580 while (!empty($this->expressions[$alias_candidate])) {
581 $alias_candidate = $alias . '_' . $count++;
583 $alias = $alias_candidate;
585 $this->expressions[$alias] = [
586 'expression' => $expression,
588 'arguments' => $arguments,
597 public function join($table, $alias = NULL, $condition = NULL, $arguments = []) {
598 return $this->addJoin('INNER', $table, $alias, $condition, $arguments);
604 public function innerJoin($table, $alias = NULL, $condition = NULL, $arguments = []) {
605 return $this->addJoin('INNER', $table, $alias, $condition, $arguments);
611 public function leftJoin($table, $alias = NULL, $condition = NULL, $arguments = []) {
612 return $this->addJoin('LEFT OUTER', $table, $alias, $condition, $arguments);
618 public function rightJoin($table, $alias = NULL, $condition = NULL, $arguments = []) {
619 return $this->addJoin('RIGHT OUTER', $table, $alias, $condition, $arguments);
625 public function addJoin($type, $table, $alias = NULL, $condition = NULL, $arguments = []) {
627 if ($table instanceof SelectInterface) {
635 $alias_candidate = $alias;
637 while (!empty($this->tables[$alias_candidate])) {
638 $alias_candidate = $alias . '_' . $count++;
640 $alias = $alias_candidate;
642 if (is_string($condition)) {
643 $condition = str_replace('%alias', $alias, $condition);
646 $this->tables[$alias] = [
647 'join type' => $type,
650 'condition' => $condition,
651 'arguments' => $arguments,
660 public function orderBy($field, $direction = 'ASC') {
661 // Only allow ASC and DESC, default to ASC.
662 $direction = strtoupper($direction) == 'DESC' ? 'DESC' : 'ASC';
663 $this->order[$field] = $direction;
670 public function orderRandom() {
671 $alias = $this->addExpression('RAND()', 'random_field');
672 $this->orderBy($alias);
679 public function range($start = NULL, $length = NULL) {
680 $this->range = $start !== NULL ? ['start' => $start, 'length' => $length] : [];
687 public function union(SelectInterface $query, $type = '') {
688 // Handle UNION aliasing.
690 // Fold UNION DISTINCT to UNION for better cross database support.
712 public function groupBy($field) {
713 $this->group[$field] = $field;
720 public function countQuery() {
721 $count = $this->prepareCountQuery();
723 $query = $this->connection->select($count, NULL, $this->queryOptions);
724 $query->addExpression('COUNT(*)');
730 * Prepares a count query from the current query object.
732 * @return \Drupal\Core\Database\Query\Select
733 * A new query object ready to have COUNT(*) performed on it.
735 protected function prepareCountQuery() {
736 // Create our new query object that we will mutate into a count query.
737 $count = clone($this);
739 $group_by = $count->getGroupBy();
740 $having = $count->havingConditions();
742 if (!$count->distinct && !isset($having[0])) {
743 // When not executing a distinct query, we can zero-out existing fields
744 // and expressions that are not used by a GROUP BY or HAVING. Fields
745 // listed in a GROUP BY or HAVING clause need to be present in the
747 $fields =& $count->getFields();
748 foreach (array_keys($fields) as $field) {
749 if (empty($group_by[$field])) {
750 unset($fields[$field]);
754 $expressions =& $count->getExpressions();
755 foreach (array_keys($expressions) as $field) {
756 if (empty($group_by[$field])) {
757 unset($expressions[$field]);
761 // Also remove 'all_fields' statements, which are expanded into tablename.*
762 // when the query is executed.
763 foreach ($count->tables as &$table) {
764 unset($table['all_fields']);
768 // If we've just removed all fields from the query, make sure there is at
769 // least one so that the query still runs.
770 $count->addExpression('1');
772 // Ordering a count query is a waste of cycles, and breaks on some
774 $orders = &$count->getOrderBy();
777 if ($count->distinct && !empty($group_by)) {
778 // If the query is distinct and contains a GROUP BY, we need to remove the
779 // distinct because SQL99 does not support counting on distinct multiple fields.
780 $count->distinct = FALSE;
783 // If there are any dependent queries to UNION, prepare each of those for
784 // the count query also.
785 foreach ($count->union as &$union) {
786 $union['query'] = $union['query']->prepareCountQuery();
795 public function __toString() {
796 // For convenience, we compile the query ourselves if the caller forgot
797 // to do it. This allows constructs like "(string) $query" to work. When
798 // the query will be executed, it will be recompiled using the proper
799 // placeholder generator anyway.
800 if (!$this->compiled()) {
801 $this->compile($this->connection, $this);
804 // Create a sanitized comment string to prepend to the query.
805 $comments = $this->connection->makeComment($this->comments);
808 $query = $comments . 'SELECT ';
809 if ($this->distinct) {
810 $query .= 'DISTINCT ';
813 // FIELDS and EXPRESSIONS
815 foreach ($this->tables as $alias => $table) {
816 if (!empty($table['all_fields'])) {
817 $fields[] = $this->connection->escapeTable($alias) . '.*';
820 foreach ($this->fields as $field) {
821 // Always use the AS keyword for field aliases, as some
822 // databases require it (e.g., PostgreSQL).
823 $fields[] = (isset($field['table']) ? $this->connection->escapeTable($field['table']) . '.' : '') . $this->connection->escapeField($field['field']) . ' AS ' . $this->connection->escapeAlias($field['alias']);
825 foreach ($this->expressions as $expression) {
826 $fields[] = $expression['expression'] . ' AS ' . $this->connection->escapeAlias($expression['alias']);
828 $query .= implode(', ', $fields);
830 // FROM - We presume all queries have a FROM, as any query that doesn't won't need the query builder anyway.
832 foreach ($this->tables as $table) {
834 if (isset($table['join type'])) {
835 $query .= $table['join type'] . ' JOIN ';
838 // If the table is a subquery, compile it and integrate it into this query.
839 if ($table['table'] instanceof SelectInterface) {
840 // Run preparation steps on this sub-query before converting to string.
841 $subquery = $table['table'];
842 $subquery->preExecute();
843 $table_string = '(' . (string) $subquery . ')';
846 $table_string = $this->connection->escapeTable($table['table']);
847 // Do not attempt prefixing cross database / schema queries.
848 if (strpos($table_string, '.') === FALSE) {
849 $table_string = '{' . $table_string . '}';
853 // Don't use the AS keyword for table aliases, as some
854 // databases don't support it (e.g., Oracle).
855 $query .= $table_string . ' ' . $this->connection->escapeTable($table['alias']);
857 if (!empty($table['condition'])) {
858 $query .= ' ON ' . (string) $table['condition'];
863 if (count($this->condition)) {
864 // There is an implicit string cast on $this->condition.
865 $query .= "\nWHERE " . $this->condition;
870 $query .= "\nGROUP BY " . implode(', ', $this->group);
874 if (count($this->having)) {
875 // There is an implicit string cast on $this->having.
876 $query .= "\nHAVING " . $this->having;
879 // UNION is a little odd, as the select queries to combine are passed into
880 // this query, but syntactically they all end up on the same level.
882 foreach ($this->union as $union) {
883 $query .= ' ' . $union['type'] . ' ' . (string) $union['query'];
889 $query .= "\nORDER BY ";
891 foreach ($this->order as $field => $direction) {
892 $fields[] = $this->connection->escapeField($field) . ' ' . $direction;
894 $query .= implode(', ', $fields);
898 // There is no universal SQL standard for handling range or limit clauses.
899 // Fortunately, all core-supported databases use the same range syntax.
900 // Databases that need a different syntax can override this method and
901 // do whatever alternate logic they need to.
902 if (!empty($this->range)) {
903 $query .= "\nLIMIT " . (int) $this->range['length'] . " OFFSET " . (int) $this->range['start'];
906 if ($this->forUpdate) {
907 $query .= ' FOR UPDATE';
916 public function __clone() {
919 // On cloning, also clone the dependent objects. However, we do not
920 // want to clone the database connection object as that would duplicate the
921 // connection itself.
923 $this->condition = clone($this->condition);
924 $this->having = clone($this->having);
925 foreach ($this->union as $key => $aggregate) {
926 $this->union[$key]['query'] = clone($aggregate['query']);
928 foreach ($this->tables as $alias => $table) {
929 if ($table['table'] instanceof SelectInterface) {
930 $this->tables[$alias]['table'] = clone $table['table'];