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 * $fields =& $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 public function rightJoin($table, $alias = NULL, $condition = NULL, $arguments = []);
353 * Join against another table in the database.
355 * This method does the "hard" work of queuing up a table to be joined against.
356 * In some cases, that may include dipping into the Schema API to find the necessary
357 * fields on which to join.
360 * The type of join. Typically one one of INNER, LEFT OUTER, and RIGHT OUTER.
362 * The table against which to join. May be a string or another SelectQuery
363 * object. If a query object is passed, it will be used as a subselect.
364 * Unless the table name starts with the database / schema name and a dot
365 * it will be prefixed.
367 * The alias for the table. In most cases this should be the first letter
368 * of the table, or the first letter of each "word" in the table. If omitted,
369 * one will be dynamically generated.
371 * The condition on which to join this table. If the join requires values,
372 * this clause should use a named placeholder and the value or values to
373 * insert should be passed in the 4th parameter. For the first table joined
374 * on a query, this value is ignored as the first table is taken as the base
375 * table. The token %alias can be used in this string to be replaced with
376 * the actual alias. This is useful when $alias is modified by the database
377 * system, for example, when joining the same table more than once.
379 * An array of arguments to replace into the $condition of this join.
381 * The unique alias that was assigned for this table.
383 public function addJoin($type, $table, $alias = NULL, $condition = NULL, $arguments = []);
386 * Orders the result set by a given field.
388 * If called multiple times, the query will order by each specified field in the
389 * order this method is called.
391 * If the query uses DISTINCT or GROUP BY conditions, fields or expressions
392 * that are used for the order must be selected to be compatible with some
393 * databases like PostgreSQL. The PostgreSQL driver can handle simple cases
394 * automatically but it is suggested to explicitly specify them. Additionally,
395 * when ordering on an alias, the alias must be added before orderBy() is
399 * The field on which to order. The field is escaped for security so only
400 * valid field and alias names are possible. To order by an expression, add
401 * the expression with addExpression() first and then use the alias to order
406 * $query->addExpression('SUBSTRING(thread, 1, (LENGTH(thread) - 1))', 'order_field');
407 * $query->orderBy('order_field', 'ASC');
410 * The direction to sort. Legal values are "ASC" and "DESC". Any other value
411 * will be converted to "ASC".
412 * @return \Drupal\Core\Database\Query\SelectInterface
415 public function orderBy($field, $direction = 'ASC');
418 * Orders the result set by a random value.
420 * This may be stacked with other orderBy() calls. If so, the query will order
421 * by each specified field, including this one, in the order called. Although
422 * this method may be called multiple times on the same query, doing so
423 * is not particularly useful.
425 * Note: The method used by most drivers may not scale to very large result
426 * sets. If you need to work with extremely large data sets, you may create
427 * your own database driver by subclassing off of an existing driver and
428 * implementing your own randomization mechanism. See
430 * http://jan.kneschke.de/projects/mysql/order-by-rand/
432 * for an example of such an alternate sorting mechanism.
434 * @return \Drupal\Core\Database\Query\SelectInterface
437 public function orderRandom();
440 * Restricts a query to a given range in the result set.
442 * If this method is called with no parameters, will remove any range
443 * directives that have been set.
446 * The first record from the result set to return. If NULL, removes any
447 * range directives that are set.
449 * The number of records to return from the result set.
450 * @return \Drupal\Core\Database\Query\SelectInterface
453 public function range($start = NULL, $length = NULL);
456 * Add another Select query to UNION to this one.
458 * Union queries consist of two or more queries whose
459 * results are effectively concatenated together. Queries
460 * will be UNIONed in the order they are specified, with
461 * this object's query coming first. Duplicate columns will
462 * be discarded. All forms of UNION are supported, using
463 * the second '$type' argument.
465 * Note: All queries UNIONed together must have the same
466 * field structure, in the same order. It is up to the
467 * caller to ensure that they match properly. If they do
468 * not, an SQL syntax error will result.
471 * The query to UNION to this query.
473 * The type of UNION to add to the query. Defaults to plain
475 * @return \Drupal\Core\Database\Query\SelectInterface
478 public function union(SelectInterface $query, $type = '');
481 * Groups the result set by the specified field.
484 * The field on which to group. This should be the field as aliased.
485 * @return \Drupal\Core\Database\Query\SelectInterface
488 public function groupBy($field);
491 * Get the equivalent COUNT query of this query as a new query object.
493 * @return \Drupal\Core\Database\Query\SelectInterface
494 * A new SelectQuery object with no fields or expressions besides COUNT(*).
496 public function countQuery();
499 * Indicates if preExecute() has already been called on that object.
502 * TRUE is this query has already been prepared, FALSE otherwise.
504 public function isPrepared();
507 * Generic preparation and validation for a SELECT query.
510 * TRUE if the validation was successful, FALSE if not.
512 public function preExecute(SelectInterface $query = NULL);
515 * Runs the query against the database.
517 * @return \Drupal\Core\Database\StatementInterface|null
518 * A prepared statement, or NULL if the query is not valid.
520 public function execute();
523 * Helper function to build most common HAVING conditional clauses.
525 * This method can take a variable number of parameters. If called with two
526 * parameters, they are taken as $field and $value with $operator having a value
527 * of IN if $value is an array and = otherwise.
530 * The name of the field to check. If you would like to add a more complex
531 * condition involving operators or functions, use having().
533 * The value to test the field against. In most cases, this is a scalar. For more
534 * complex options, it is an array. The meaning of each element in the array is
535 * dependent on the $operator.
537 * The comparison operator, such as =, <, or >=. It also accepts more complex
538 * options such as IN, LIKE, or BETWEEN. Defaults to IN if $value is an array
540 * @return \Drupal\Core\Database\Query\ConditionInterface
543 public function havingCondition($field, $value = NULL, $operator = NULL);
546 * Gets a list of all conditions in the HAVING clause.
548 * This method returns by reference. That allows alter hooks to access the
549 * data structure directly and manipulate it before it gets compiled.
552 * An array of conditions.
554 * @see \Drupal\Core\Database\Query\ConditionInterface::conditions()
556 public function &havingConditions();
559 * Gets a list of all values to insert into the HAVING clause.
562 * An associative array of placeholders and values.
564 public function havingArguments();
567 * Adds an arbitrary HAVING clause to the query.
570 * A portion of a HAVING clause as a prepared statement. It must use named
571 * placeholders, not ? placeholders.
573 * (optional) An associative array of arguments.
577 public function having($snippet, $args = []);
580 * Compiles the HAVING clause for later retrieval.
583 * The database connection for which to compile the clause.
585 public function havingCompile(Connection $connection);
588 * Sets a condition in the HAVING clause that the specified field be NULL.
591 * The name of the field to check.
595 public function havingIsNull($field);
598 * Sets a condition in the HAVING clause that the specified field be NOT NULL.
601 * The name of the field to check.
605 public function havingIsNotNull($field);
608 * Sets a HAVING condition that the specified subquery returns values.
610 * @param \Drupal\Core\Database\Query\SelectInterface $select
611 * The subquery that must contain results.
615 public function havingExists(SelectInterface $select);
618 * Sets a HAVING condition that the specified subquery returns no values.
620 * @param \Drupal\Core\Database\Query\SelectInterface $select
621 * The subquery that must contain results.
625 public function havingNotExists(SelectInterface $select);
628 * Clone magic method.
630 * Select queries have dependent objects that must be deep-cloned. The
631 * connection object itself, however, should not be cloned as that would
632 * duplicate the connection itself.
634 public function __clone();
637 * Add FOR UPDATE to the query.
639 * FOR UPDATE prevents the rows retrieved by the SELECT statement from being
640 * modified or deleted by other transactions until the current transaction
641 * ends. Other transactions that attempt UPDATE, DELETE, or SELECT FOR UPDATE
642 * of these rows will be blocked until the current transaction ends.
645 * IF TRUE, FOR UPDATE will be added to the query, if FALSE then it won't.
647 * @return \Drupal\Core\Database\Query\ConditionInterface
650 public function forUpdate($set = TRUE);
653 * Returns a string representation of how the query will be executed in SQL.
656 * The Select Query object expressed as a string.
658 public function __toString();