3 namespace Drupal\Core\Database\Query;
5 use Drupal\Core\Database\Connection;
8 * Interface definition for a Select Query object.
12 interface SelectInterface extends ConditionInterface, AlterableInterface, ExtendableInterface, PlaceholderInterface {
14 /* Alter accessors to expose the query data to alter hooks. */
17 * Returns a reference to the fields array for this query.
19 * Because this method returns by reference, alter hooks may edit the fields
20 * array directly to make their changes. If just adding fields, however, the
21 * use of addField() is preferred.
23 * Note that this method must be called by reference as well:
26 * $fields =& $query->getFields();
30 * A reference to the fields array structure.
32 public function &getFields();
35 * Returns a reference to the expressions array for this query.
37 * Because this method returns by reference, alter hooks may edit the expressions
38 * array directly to make their changes. If just adding expressions, however, the
39 * use of addExpression() is preferred.
41 * Note that this method must be called by reference as well:
44 * $fields =& $query->getExpressions();
48 * A reference to the expression array structure.
50 public function &getExpressions();
53 * Returns a reference to the order by array for this query.
55 * Because this method returns by reference, alter hooks may edit the order-by
56 * array directly to make their changes. If just adding additional ordering
57 * fields, however, the use of orderBy() is preferred.
59 * Note that this method must be called by reference as well:
62 * $fields =& $query->getOrderBy();
66 * A reference to the expression array structure.
68 public function &getOrderBy();
71 * Returns a reference to the group-by array for this query.
73 * Because this method returns by reference, alter hooks may edit the group-by
74 * array directly to make their changes. If just adding additional grouping
75 * fields, however, the use of groupBy() is preferred.
77 * Note that this method must be called by reference as well:
80 * $fields =& $query->getGroupBy();
84 * A reference to the group-by array structure.
86 public function &getGroupBy();
89 * Returns a reference to the tables array for this query.
91 * Because this method returns by reference, alter hooks may edit the tables
92 * array directly to make their changes. If just adding tables, however, the
93 * use of the join() methods is preferred.
95 * Note that this method must be called by reference as well:
98 * $tables =& $query->getTables();
102 * A reference to the tables array structure.
104 public function &getTables();
107 * Returns a reference to the union queries for this query. This include
108 * queries for UNION, UNION ALL, and UNION DISTINCT.
110 * Because this method returns by reference, alter hooks may edit the tables
111 * array directly to make their changes. If just adding union queries,
112 * however, the use of the union() method is preferred.
114 * Note that this method must be called by reference as well:
117 * $fields =& $query->getUnion();
121 * A reference to the union query array structure.
123 public function &getUnion();
126 * Escapes characters that work as wildcard characters in a LIKE pattern.
129 * The string to escape.
132 * The escaped string.
134 * @see \Drupal\Core\Database\Connection::escapeLike()
136 public function escapeLike($string);
139 * Escapes a field name string.
141 * Force all field names to be strictly alphanumeric-plus-underscore.
142 * For some database drivers, it may also wrap the field name in
143 * database-specific escape characters.
145 * @param string $string
146 * An unsanitized field name.
149 * The sanitized field name string.
151 public function escapeField($string);
154 * Compiles and returns an associative array of the arguments for this prepared statement.
156 * @param $queryPlaceholder
157 * When collecting the arguments of a subquery, the main placeholder
158 * object should be passed as this parameter.
161 * An associative array of all placeholder arguments for this query.
163 public function getArguments(PlaceholderInterface $queryPlaceholder = NULL);
165 /* Query building operations */
168 * Sets this query to be DISTINCT.
171 * TRUE to flag this query DISTINCT, FALSE to disable it.
172 * @return \Drupal\Core\Database\Query\SelectInterface
175 public function distinct($distinct = TRUE);
178 * Adds a field to the list to be SELECTed.
180 * @param $table_alias
181 * The name of the table from which the field comes, as an alias. Generally
182 * you will want to use the return value of join() here to ensure that it is
185 * The name of the field.
187 * The alias for this field. If not specified, one will be generated
188 * automatically based on the $table_alias and $field. The alias will be
189 * checked for uniqueness, so the requested alias may not be the alias
190 * that is assigned in all cases.
192 * The unique alias that was assigned for this field.
194 public function addField($table_alias, $field, $alias = NULL);
197 * Add multiple fields from the same table to be SELECTed.
199 * This method does not return the aliases set for the passed fields. In the
200 * majority of cases that is not a problem, as the alias will be the field
201 * name. However, if you do need to know the alias you can call getFields()
202 * and examine the result to determine what alias was created. Alternatively,
203 * simply use addField() for the few fields you care about and this method for
206 * @param $table_alias
207 * The name of the table from which the field comes, as an alias. Generally
208 * you will want to use the return value of join() here to ensure that it is
211 * An indexed array of fields present in the specified table that should be
212 * included in this query. If not specified, $table_alias.* will be generated
213 * without any aliases.
214 * @return \Drupal\Core\Database\Query\SelectInterface
217 public function fields($table_alias, array $fields = []);
220 * Adds an expression to the list of "fields" to be SELECTed.
222 * An expression can be any arbitrary string that is valid SQL. That includes
223 * various functions, which may in some cases be database-dependent. This
224 * method makes no effort to correct for database-specific functions.
227 * The expression string. May contain placeholders.
229 * The alias for this expression. If not specified, one will be generated
230 * automatically in the form "expression_#". The alias will be checked for
231 * uniqueness, so the requested alias may not be the alias that is assigned
234 * Any placeholder arguments needed for this expression.
236 * The unique alias that was assigned for this expression.
238 public function addExpression($expression, $alias = NULL, $arguments = []);
241 * Default Join against another table in the database.
243 * This method is a convenience method for innerJoin().
246 * The table against which to join. May be a string or another SelectQuery
247 * object. If a query object is passed, it will be used as a subselect.
248 * Unless the table name starts with the database / schema name and a dot
249 * it will be prefixed.
251 * The alias for the table. In most cases this should be the first letter
252 * of the table, or the first letter of each "word" in the table.
254 * The condition on which to join this table. If the join requires values,
255 * this clause should use a named placeholder and the value or values to
256 * insert should be passed in the 4th parameter. For the first table joined
257 * on a query, this value is ignored as the first table is taken as the base
258 * table. The token %alias can be used in this string to be replaced with
259 * the actual alias. This is useful when $alias is modified by the database
260 * system, for example, when joining the same table more than once.
262 * An array of arguments to replace into the $condition of this join.
264 * The unique alias that was assigned for this table.
266 public function join($table, $alias = NULL, $condition = NULL, $arguments = []);
269 * Inner Join against another table in the database.
272 * The table against which to join. May be a string or another SelectQuery
273 * object. If a query object is passed, it will be used as a subselect.
274 * Unless the table name starts with the database / schema name and a dot
275 * it will be prefixed.
277 * The alias for the table. In most cases this should be the first letter
278 * of the table, or the first letter of each "word" in the table.
280 * The condition on which to join this table. If the join requires values,
281 * this clause should use a named placeholder and the value or values to
282 * insert should be passed in the 4th parameter. For the first table joined
283 * on a query, this value is ignored as the first table is taken as the base
284 * table. The token %alias can be used in this string to be replaced with
285 * the actual alias. This is useful when $alias is modified by the database
286 * system, for example, when joining the same table more than once.
288 * An array of arguments to replace into the $condition of this join.
290 * The unique alias that was assigned for this table.
292 public function innerJoin($table, $alias = NULL, $condition = NULL, $arguments = []);
295 * Left Outer Join against another table in the database.
298 * The table against which to join. May be a string or another SelectQuery
299 * object. If a query object is passed, it will be used as a subselect.
300 * Unless the table name starts with the database / schema name and a dot
301 * it will be prefixed.
303 * The alias for the table. In most cases this should be the first letter
304 * of the table, or the first letter of each "word" in the table.
306 * The condition on which to join this table. If the join requires values,
307 * this clause should use a named placeholder and the value or values to
308 * insert should be passed in the 4th parameter. For the first table joined
309 * on a query, this value is ignored as the first table is taken as the base
310 * table. The token %alias can be used in this string to be replaced with
311 * the actual alias. This is useful when $alias is modified by the database
312 * system, for example, when joining the same table more than once.
314 * An array of arguments to replace into the $condition of this join.
316 * The unique alias that was assigned for this table.
318 public function leftJoin($table, $alias = NULL, $condition = NULL, $arguments = []);
321 * Right Outer Join against another table in the database.
324 * The table against which to join. May be a string or another SelectQuery
325 * object. If a query object is passed, it will be used as a subselect.
326 * Unless the table name starts with the database / schema name and a dot
327 * it will be prefixed.
329 * The alias for the table. In most cases this should be the first letter
330 * of the table, or the first letter of each "word" in the table.
332 * The condition on which to join this table. If the join requires values,
333 * this clause should use a named placeholder and the value or values to
334 * insert should be passed in the 4th parameter. For the first table joined
335 * on a query, this value is ignored as the first table is taken as the base
336 * table. The token %alias can be used in this string to be replaced with
337 * the actual alias. This is useful when $alias is modified by the database
338 * system, for example, when joining the same table more than once.
340 * An array of arguments to replace into the $condition of this join.
342 * The unique alias that was assigned for this table.
344 * @deprecated as of Drupal 8.1.x, will be removed in Drupal 9.0.0. Instead,
345 * change the query to use leftJoin(). For instance:
346 * db_query('A')->rightJoin('B') is identical to
347 * db_query('B')->leftJoin('A'). This functionality has been deprecated
348 * because SQLite does not support it.
350 * @see https://www.drupal.org/node/2765249
352 public function rightJoin($table, $alias = NULL, $condition = NULL, $arguments = []);
355 * Join against another table in the database.
357 * This method does the "hard" work of queuing up a table to be joined against.
358 * In some cases, that may include dipping into the Schema API to find the necessary
359 * fields on which to join.
362 * The type of join. Typically one one of INNER, LEFT OUTER, and RIGHT OUTER.
364 * The table against which to join. May be a string or another SelectQuery
365 * object. If a query object is passed, it will be used as a subselect.
366 * Unless the table name starts with the database / schema name and a dot
367 * it will be prefixed.
369 * The alias for the table. In most cases this should be the first letter
370 * of the table, or the first letter of each "word" in the table. If omitted,
371 * one will be dynamically generated.
373 * The condition on which to join this table. If the join requires values,
374 * this clause should use a named placeholder and the value or values to
375 * insert should be passed in the 4th parameter. For the first table joined
376 * on a query, this value is ignored as the first table is taken as the base
377 * table. The token %alias can be used in this string to be replaced with
378 * the actual alias. This is useful when $alias is modified by the database
379 * system, for example, when joining the same table more than once.
381 * An array of arguments to replace into the $condition of this join.
383 * The unique alias that was assigned for this table.
385 public function addJoin($type, $table, $alias = NULL, $condition = NULL, $arguments = []);
388 * Orders the result set by a given field.
390 * If called multiple times, the query will order by each specified field in the
391 * order this method is called.
393 * If the query uses DISTINCT or GROUP BY conditions, fields or expressions
394 * that are used for the order must be selected to be compatible with some
395 * databases like PostgreSQL. The PostgreSQL driver can handle simple cases
396 * automatically but it is suggested to explicitly specify them. Additionally,
397 * when ordering on an alias, the alias must be added before orderBy() is
401 * The field on which to order. The field is escaped for security so only
402 * valid field and alias names are possible. To order by an expression, add
403 * the expression with addExpression() first and then use the alias to order
408 * $query->addExpression('SUBSTRING(thread, 1, (LENGTH(thread) - 1))', 'order_field');
409 * $query->orderBy('order_field', 'ASC');
412 * The direction to sort. Legal values are "ASC" and "DESC". Any other value
413 * will be converted to "ASC".
414 * @return \Drupal\Core\Database\Query\SelectInterface
417 public function orderBy($field, $direction = 'ASC');
420 * Orders the result set by a random value.
422 * This may be stacked with other orderBy() calls. If so, the query will order
423 * by each specified field, including this one, in the order called. Although
424 * this method may be called multiple times on the same query, doing so
425 * is not particularly useful.
427 * Note: The method used by most drivers may not scale to very large result
428 * sets. If you need to work with extremely large data sets, you may create
429 * your own database driver by subclassing off of an existing driver and
430 * implementing your own randomization mechanism. See
432 * http://jan.kneschke.de/projects/mysql/order-by-rand/
434 * for an example of such an alternate sorting mechanism.
436 * @return \Drupal\Core\Database\Query\SelectInterface
439 public function orderRandom();
442 * Restricts a query to a given range in the result set.
444 * If this method is called with no parameters, will remove any range
445 * directives that have been set.
448 * The first record from the result set to return. If NULL, removes any
449 * range directives that are set.
451 * The number of records to return from the result set.
452 * @return \Drupal\Core\Database\Query\SelectInterface
455 public function range($start = NULL, $length = NULL);
458 * Add another Select query to UNION to this one.
460 * Union queries consist of two or more queries whose
461 * results are effectively concatenated together. Queries
462 * will be UNIONed in the order they are specified, with
463 * this object's query coming first. Duplicate columns will
464 * be discarded. All forms of UNION are supported, using
465 * the second '$type' argument.
467 * Note: All queries UNIONed together must have the same
468 * field structure, in the same order. It is up to the
469 * caller to ensure that they match properly. If they do
470 * not, an SQL syntax error will result.
473 * The query to UNION to this query.
475 * The type of UNION to add to the query. Defaults to plain
477 * @return \Drupal\Core\Database\Query\SelectInterface
480 public function union(SelectInterface $query, $type = '');
483 * Groups the result set by the specified field.
486 * The field on which to group. This should be the field as aliased.
487 * @return \Drupal\Core\Database\Query\SelectInterface
490 public function groupBy($field);
493 * Get the equivalent COUNT query of this query as a new query object.
495 * @return \Drupal\Core\Database\Query\SelectInterface
496 * A new SelectQuery object with no fields or expressions besides COUNT(*).
498 public function countQuery();
501 * Indicates if preExecute() has already been called on that object.
504 * TRUE is this query has already been prepared, FALSE otherwise.
506 public function isPrepared();
509 * Generic preparation and validation for a SELECT query.
512 * TRUE if the validation was successful, FALSE if not.
514 public function preExecute(SelectInterface $query = NULL);
517 * Runs the query against the database.
519 * @return \Drupal\Core\Database\StatementInterface|null
520 * A prepared statement, or NULL if the query is not valid.
522 public function execute();
525 * Helper function to build most common HAVING conditional clauses.
527 * This method can take a variable number of parameters. If called with two
528 * parameters, they are taken as $field and $value with $operator having a value
529 * of IN if $value is an array and = otherwise.
532 * The name of the field to check. If you would like to add a more complex
533 * condition involving operators or functions, use having().
535 * The value to test the field against. In most cases, this is a scalar. For more
536 * complex options, it is an array. The meaning of each element in the array is
537 * dependent on the $operator.
539 * The comparison operator, such as =, <, or >=. It also accepts more complex
540 * options such as IN, LIKE, or BETWEEN. Defaults to IN if $value is an array
542 * @return \Drupal\Core\Database\Query\ConditionInterface
545 public function havingCondition($field, $value = NULL, $operator = NULL);
548 * Gets a list of all conditions in the HAVING clause.
550 * This method returns by reference. That allows alter hooks to access the
551 * data structure directly and manipulate it before it gets compiled.
554 * An array of conditions.
556 * @see \Drupal\Core\Database\Query\ConditionInterface::conditions()
558 public function &havingConditions();
561 * Gets a list of all values to insert into the HAVING clause.
564 * An associative array of placeholders and values.
566 public function havingArguments();
569 * Adds an arbitrary HAVING clause to the query.
572 * A portion of a HAVING clause as a prepared statement. It must use named
573 * placeholders, not ? placeholders.
575 * (optional) An associative array of arguments.
579 public function having($snippet, $args = []);
582 * Compiles the HAVING clause for later retrieval.
585 * The database connection for which to compile the clause.
587 public function havingCompile(Connection $connection);
590 * Sets a condition in the HAVING clause that the specified field be NULL.
593 * The name of the field to check.
597 public function havingIsNull($field);
600 * Sets a condition in the HAVING clause that the specified field be NOT NULL.
603 * The name of the field to check.
607 public function havingIsNotNull($field);
610 * Sets a HAVING condition that the specified subquery returns values.
612 * @param \Drupal\Core\Database\Query\SelectInterface $select
613 * The subquery that must contain results.
617 public function havingExists(SelectInterface $select);
620 * Sets a HAVING condition that the specified subquery returns no values.
622 * @param \Drupal\Core\Database\Query\SelectInterface $select
623 * The subquery that must contain results.
627 public function havingNotExists(SelectInterface $select);
630 * Clone magic method.
632 * Select queries have dependent objects that must be deep-cloned. The
633 * connection object itself, however, should not be cloned as that would
634 * duplicate the connection itself.
636 public function __clone();
639 * Add FOR UPDATE to the query.
641 * FOR UPDATE prevents the rows retrieved by the SELECT statement from being
642 * modified or deleted by other transactions until the current transaction
643 * ends. Other transactions that attempt UPDATE, DELETE, or SELECT FOR UPDATE
644 * of these rows will be blocked until the current transaction ends.
647 * IF TRUE, FOR UPDATE will be added to the query, if FALSE then it won't.
649 * @return \Drupal\Core\Database\Query\ConditionInterface
652 public function forUpdate($set = TRUE);
655 * Returns a string representation of how the query will be executed in SQL.
658 * The Select Query object expressed as a string.
660 public function __toString();