1: <?php
2: /**
3: * CakePHP(tm) : Rapid Development Framework (https://cakephp.org)
4: * Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
5: *
6: * Licensed under The MIT License
7: * For full copyright and license information, please see the LICENSE.txt
8: * Redistributions of files must retain the above copyright notice.
9: *
10: * @copyright Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
11: * @link https://cakephp.org CakePHP(tm) Project
12: * @since 3.0.0
13: * @license https://opensource.org/licenses/mit-license.php MIT License
14: */
15: namespace Cake\Database;
16:
17: use Cake\Database\Expression\IdentifierExpression;
18: use Cake\Database\Expression\OrderByExpression;
19: use Cake\Database\Expression\OrderClauseExpression;
20: use Cake\Database\Expression\QueryExpression;
21: use Cake\Database\Expression\ValuesExpression;
22: use Cake\Database\Statement\CallbackStatement;
23: use Cake\Datasource\QueryInterface;
24: use InvalidArgumentException;
25: use IteratorAggregate;
26: use RuntimeException;
27:
28: /**
29: * This class represents a Relational database SQL Query. A query can be of
30: * different types like select, update, insert and delete. Exposes the methods
31: * for dynamically constructing each query part, execute it and transform it
32: * to a specific SQL dialect.
33: */
34: class Query implements ExpressionInterface, IteratorAggregate
35: {
36: use TypeMapTrait;
37:
38: /**
39: * Connection instance to be used to execute this query.
40: *
41: * @var \Cake\Database\Connection
42: */
43: protected $_connection;
44:
45: /**
46: * Type of this query (select, insert, update, delete).
47: *
48: * @var string
49: */
50: protected $_type = 'select';
51:
52: /**
53: * List of SQL parts that will be used to build this query.
54: *
55: * @var array
56: */
57: protected $_parts = [
58: 'delete' => true,
59: 'update' => [],
60: 'set' => [],
61: 'insert' => [],
62: 'values' => [],
63: 'select' => [],
64: 'distinct' => false,
65: 'modifier' => [],
66: 'from' => [],
67: 'join' => [],
68: 'where' => null,
69: 'group' => [],
70: 'having' => null,
71: 'order' => null,
72: 'limit' => null,
73: 'offset' => null,
74: 'union' => [],
75: 'epilog' => null
76: ];
77:
78: /**
79: * Indicates whether internal state of this query was changed, this is used to
80: * discard internal cached objects such as the transformed query or the reference
81: * to the executed statement.
82: *
83: * @var bool
84: */
85: protected $_dirty = false;
86:
87: /**
88: * A list of callback functions to be called to alter each row from resulting
89: * statement upon retrieval. Each one of the callback function will receive
90: * the row array as first argument.
91: *
92: * @var array
93: */
94: protected $_resultDecorators = [];
95:
96: /**
97: * Statement object resulting from executing this query.
98: *
99: * @var \Cake\Database\StatementInterface|null
100: */
101: protected $_iterator;
102:
103: /**
104: * The object responsible for generating query placeholders and temporarily store values
105: * associated to each of those.
106: *
107: * @var \Cake\Database\ValueBinder|null
108: */
109: protected $_valueBinder;
110:
111: /**
112: * Instance of functions builder object used for generating arbitrary SQL functions.
113: *
114: * @var \Cake\Database\FunctionsBuilder|null
115: */
116: protected $_functionsBuilder;
117:
118: /**
119: * Boolean for tracking whether or not buffered results
120: * are enabled.
121: *
122: * @var bool
123: */
124: protected $_useBufferedResults = true;
125:
126: /**
127: * The Type map for fields in the select clause
128: *
129: * @var \Cake\Database\TypeMap
130: */
131: protected $_selectTypeMap;
132:
133: /**
134: * Tracking flag to disable casting
135: *
136: * @var bool
137: */
138: protected $typeCastEnabled = true;
139:
140: /**
141: * Constructor.
142: *
143: * @param \Cake\Database\Connection $connection The connection
144: * object to be used for transforming and executing this query
145: */
146: public function __construct($connection)
147: {
148: $this->setConnection($connection);
149: }
150:
151: /**
152: * Sets the connection instance to be used for executing and transforming this query.
153: *
154: * @param \Cake\Database\Connection $connection Connection instance
155: * @return $this
156: */
157: public function setConnection($connection)
158: {
159: $this->_dirty();
160: $this->_connection = $connection;
161:
162: return $this;
163: }
164:
165: /**
166: * Gets the connection instance to be used for executing and transforming this query.
167: *
168: * @return \Cake\Database\Connection
169: */
170: public function getConnection()
171: {
172: return $this->_connection;
173: }
174:
175: /**
176: * Sets the connection instance to be used for executing and transforming this query
177: * When called with a null argument, it will return the current connection instance.
178: *
179: * @deprecated 3.4.0 Use setConnection()/getConnection() instead.
180: * @param \Cake\Database\Connection|null $connection Connection instance
181: * @return $this|\Cake\Database\Connection
182: */
183: public function connection($connection = null)
184: {
185: deprecationWarning(
186: 'Query::connection() is deprecated. ' .
187: 'Use Query::setConnection()/getConnection() instead.'
188: );
189: if ($connection !== null) {
190: return $this->setConnection($connection);
191: }
192:
193: return $this->getConnection();
194: }
195:
196: /**
197: * Compiles the SQL representation of this query and executes it using the
198: * configured connection object. Returns the resulting statement object.
199: *
200: * Executing a query internally executes several steps, the first one is
201: * letting the connection transform this object to fit its particular dialect,
202: * this might result in generating a different Query object that will be the one
203: * to actually be executed. Immediately after, literal values are passed to the
204: * connection so they are bound to the query in a safe way. Finally, the resulting
205: * statement is decorated with custom objects to execute callbacks for each row
206: * retrieved if necessary.
207: *
208: * Resulting statement is traversable, so it can be used in any loop as you would
209: * with an array.
210: *
211: * This method can be overridden in query subclasses to decorate behavior
212: * around query execution.
213: *
214: * @return \Cake\Database\StatementInterface
215: */
216: public function execute()
217: {
218: $statement = $this->_connection->run($this);
219: $this->_iterator = $this->_decorateStatement($statement);
220: $this->_dirty = false;
221:
222: return $this->_iterator;
223: }
224:
225: /**
226: * Executes the SQL of this query and immediately closes the statement before returning the row count of records
227: * changed.
228: *
229: * This method can be used with UPDATE and DELETE queries, but is not recommended for SELECT queries and is not
230: * used to count records.
231: *
232: * ## Example
233: *
234: * ```
235: * $rowCount = $query->update('articles')
236: * ->set(['published'=>true])
237: * ->where(['published'=>false])
238: * ->rowCountAndClose();
239: * ```
240: *
241: * The above example will change the published column to true for all false records, and return the number of
242: * records that were updated.
243: *
244: * @return int
245: */
246: public function rowCountAndClose()
247: {
248: $statement = $this->execute();
249: try {
250: return $statement->rowCount();
251: } finally {
252: $statement->closeCursor();
253: }
254: }
255:
256: /**
257: * Returns the SQL representation of this object.
258: *
259: * This function will compile this query to make it compatible
260: * with the SQL dialect that is used by the connection, This process might
261: * add, remove or alter any query part or internal expression to make it
262: * executable in the target platform.
263: *
264: * The resulting query may have placeholders that will be replaced with the actual
265: * values when the query is executed, hence it is most suitable to use with
266: * prepared statements.
267: *
268: * @param \Cake\Database\ValueBinder|null $generator A placeholder object that will hold
269: * associated values for expressions
270: * @return string
271: */
272: public function sql(ValueBinder $generator = null)
273: {
274: if (!$generator) {
275: $generator = $this->getValueBinder();
276: $generator->resetCount();
277: }
278:
279: return $this->getConnection()->compileQuery($this, $generator);
280: }
281:
282: /**
283: * Will iterate over every specified part. Traversing functions can aggregate
284: * results using variables in the closure or instance variables. This function
285: * is commonly used as a way for traversing all query parts that
286: * are going to be used for constructing a query.
287: *
288: * The callback will receive 2 parameters, the first one is the value of the query
289: * part that is being iterated and the second the name of such part.
290: *
291: * ### Example:
292: * ```
293: * $query->select(['title'])->from('articles')->traverse(function ($value, $clause) {
294: * if ($clause === 'select') {
295: * var_dump($value);
296: * }
297: * }, ['select', 'from']);
298: * ```
299: *
300: * @param callable $visitor A function or callable to be executed for each part
301: * @param string[] $parts The query clauses to traverse
302: * @return $this
303: */
304: public function traverse(callable $visitor, array $parts = [])
305: {
306: $parts = $parts ?: array_keys($this->_parts);
307: foreach ($parts as $name) {
308: $visitor($this->_parts[$name], $name);
309: }
310:
311: return $this;
312: }
313:
314: /**
315: * Adds new fields to be returned by a `SELECT` statement when this query is
316: * executed. Fields can be passed as an array of strings, array of expression
317: * objects, a single expression or a single string.
318: *
319: * If an array is passed, keys will be used to alias fields using the value as the
320: * real field to be aliased. It is possible to alias strings, Expression objects or
321: * even other Query objects.
322: *
323: * If a callable function is passed, the returning array of the function will
324: * be used as the list of fields.
325: *
326: * By default this function will append any passed argument to the list of fields
327: * to be selected, unless the second argument is set to true.
328: *
329: * ### Examples:
330: *
331: * ```
332: * $query->select(['id', 'title']); // Produces SELECT id, title
333: * $query->select(['author' => 'author_id']); // Appends author: SELECT id, title, author_id as author
334: * $query->select('id', true); // Resets the list: SELECT id
335: * $query->select(['total' => $countQuery]); // SELECT id, (SELECT ...) AS total
336: * $query->select(function ($query) {
337: * return ['article_id', 'total' => $query->count('*')];
338: * })
339: * ```
340: *
341: * By default no fields are selected, if you have an instance of `Cake\ORM\Query` and try to append
342: * fields you should also call `Cake\ORM\Query::enableAutoFields()` to select the default fields
343: * from the table.
344: *
345: * @param array|\Cake\Database\ExpressionInterface|string|callable $fields fields to be added to the list.
346: * @param bool $overwrite whether to reset fields with passed list or not
347: * @return $this
348: */
349: public function select($fields = [], $overwrite = false)
350: {
351: if (!is_string($fields) && is_callable($fields)) {
352: $fields = $fields($this);
353: }
354:
355: if (!is_array($fields)) {
356: $fields = [$fields];
357: }
358:
359: if ($overwrite) {
360: $this->_parts['select'] = $fields;
361: } else {
362: $this->_parts['select'] = array_merge($this->_parts['select'], $fields);
363: }
364:
365: $this->_dirty();
366: $this->_type = 'select';
367:
368: return $this;
369: }
370:
371: /**
372: * Adds a `DISTINCT` clause to the query to remove duplicates from the result set.
373: * This clause can only be used for select statements.
374: *
375: * If you wish to filter duplicates based of those rows sharing a particular field
376: * or set of fields, you may pass an array of fields to filter on. Beware that
377: * this option might not be fully supported in all database systems.
378: *
379: * ### Examples:
380: *
381: * ```
382: * // Filters products with the same name and city
383: * $query->select(['name', 'city'])->from('products')->distinct();
384: *
385: * // Filters products in the same city
386: * $query->distinct(['city']);
387: * $query->distinct('city');
388: *
389: * // Filter products with the same name
390: * $query->distinct(['name'], true);
391: * $query->distinct('name', true);
392: * ```
393: *
394: * @param array|\Cake\Database\ExpressionInterface|string|bool $on Enable/disable distinct class
395: * or list of fields to be filtered on
396: * @param bool $overwrite whether to reset fields with passed list or not
397: * @return $this
398: */
399: public function distinct($on = [], $overwrite = false)
400: {
401: if ($on === []) {
402: $on = true;
403: } elseif (is_string($on)) {
404: $on = [$on];
405: }
406:
407: if (is_array($on)) {
408: $merge = [];
409: if (is_array($this->_parts['distinct'])) {
410: $merge = $this->_parts['distinct'];
411: }
412: $on = $overwrite ? array_values($on) : array_merge($merge, array_values($on));
413: }
414:
415: $this->_parts['distinct'] = $on;
416: $this->_dirty();
417:
418: return $this;
419: }
420:
421: /**
422: * Adds a single or multiple `SELECT` modifiers to be used in the `SELECT`.
423: *
424: * By default this function will append any passed argument to the list of modifiers
425: * to be applied, unless the second argument is set to true.
426: *
427: * ### Example:
428: *
429: * ```
430: * // Ignore cache query in MySQL
431: * $query->select(['name', 'city'])->from('products')->modifier('SQL_NO_CACHE');
432: * // It will produce the SQL: SELECT SQL_NO_CACHE name, city FROM products
433: *
434: * // Or with multiple modifiers
435: * $query->select(['name', 'city'])->from('products')->modifier(['HIGH_PRIORITY', 'SQL_NO_CACHE']);
436: * // It will produce the SQL: SELECT HIGH_PRIORITY SQL_NO_CACHE name, city FROM products
437: * ```
438: *
439: * @param array|\Cake\Database\ExpressionInterface|string $modifiers modifiers to be applied to the query
440: * @param bool $overwrite whether to reset order with field list or not
441: * @return $this
442: */
443: public function modifier($modifiers, $overwrite = false)
444: {
445: $this->_dirty();
446: if ($overwrite) {
447: $this->_parts['modifier'] = [];
448: }
449: $this->_parts['modifier'] = array_merge($this->_parts['modifier'], (array)$modifiers);
450:
451: return $this;
452: }
453:
454: /**
455: * Adds a single or multiple tables to be used in the FROM clause for this query.
456: * Tables can be passed as an array of strings, array of expression
457: * objects, a single expression or a single string.
458: *
459: * If an array is passed, keys will be used to alias tables using the value as the
460: * real field to be aliased. It is possible to alias strings, ExpressionInterface objects or
461: * even other Query objects.
462: *
463: * By default this function will append any passed argument to the list of tables
464: * to be selected from, unless the second argument is set to true.
465: *
466: * This method can be used for select, update and delete statements.
467: *
468: * ### Examples:
469: *
470: * ```
471: * $query->from(['p' => 'posts']); // Produces FROM posts p
472: * $query->from('authors'); // Appends authors: FROM posts p, authors
473: * $query->from(['products'], true); // Resets the list: FROM products
474: * $query->from(['sub' => $countQuery]); // FROM (SELECT ...) sub
475: * ```
476: *
477: * @param array|string $tables tables to be added to the list. This argument, can be
478: * passed as an array of strings, array of expression objects, or a single string. See
479: * the examples above for the valid call types.
480: * @param bool $overwrite whether to reset tables with passed list or not
481: * @return $this|array
482: */
483: public function from($tables = [], $overwrite = false)
484: {
485: if (empty($tables)) {
486: deprecationWarning('Using Query::from() to read state is deprecated. Use clause("from") instead.');
487:
488: return $this->_parts['from'];
489: }
490:
491: $tables = (array)$tables;
492:
493: if ($overwrite) {
494: $this->_parts['from'] = $tables;
495: } else {
496: $this->_parts['from'] = array_merge($this->_parts['from'], $tables);
497: }
498:
499: $this->_dirty();
500:
501: return $this;
502: }
503:
504: /**
505: * Adds a single or multiple tables to be used as JOIN clauses to this query.
506: * Tables can be passed as an array of strings, an array describing the
507: * join parts, an array with multiple join descriptions, or a single string.
508: *
509: * By default this function will append any passed argument to the list of tables
510: * to be joined, unless the third argument is set to true.
511: *
512: * When no join type is specified an `INNER JOIN` is used by default:
513: * `$query->join(['authors'])` will produce `INNER JOIN authors ON 1 = 1`
514: *
515: * It is also possible to alias joins using the array key:
516: * `$query->join(['a' => 'authors'])` will produce `INNER JOIN authors a ON 1 = 1`
517: *
518: * A join can be fully described and aliased using the array notation:
519: *
520: * ```
521: * $query->join([
522: * 'a' => [
523: * 'table' => 'authors',
524: * 'type' => 'LEFT',
525: * 'conditions' => 'a.id = b.author_id'
526: * ]
527: * ]);
528: * // Produces LEFT JOIN authors a ON a.id = b.author_id
529: * ```
530: *
531: * You can even specify multiple joins in an array, including the full description:
532: *
533: * ```
534: * $query->join([
535: * 'a' => [
536: * 'table' => 'authors',
537: * 'type' => 'LEFT',
538: * 'conditions' => 'a.id = b.author_id'
539: * ],
540: * 'p' => [
541: * 'table' => 'publishers',
542: * 'type' => 'INNER',
543: * 'conditions' => 'p.id = b.publisher_id AND p.name = "Cake Software Foundation"'
544: * ]
545: * ]);
546: * // LEFT JOIN authors a ON a.id = b.author_id
547: * // INNER JOIN publishers p ON p.id = b.publisher_id AND p.name = "Cake Software Foundation"
548: * ```
549: *
550: * ### Using conditions and types
551: *
552: * Conditions can be expressed, as in the examples above, using a string for comparing
553: * columns, or string with already quoted literal values. Additionally it is
554: * possible to use conditions expressed in arrays or expression objects.
555: *
556: * When using arrays for expressing conditions, it is often desirable to convert
557: * the literal values to the correct database representation. This is achieved
558: * using the second parameter of this function.
559: *
560: * ```
561: * $query->join(['a' => [
562: * 'table' => 'articles',
563: * 'conditions' => [
564: * 'a.posted >=' => new DateTime('-3 days'),
565: * 'a.published' => true,
566: * 'a.author_id = authors.id'
567: * ]
568: * ]], ['a.posted' => 'datetime', 'a.published' => 'boolean'])
569: * ```
570: *
571: * ### Overwriting joins
572: *
573: * When creating aliased joins using the array notation, you can override
574: * previous join definitions by using the same alias in consequent
575: * calls to this function or you can replace all previously defined joins
576: * with another list if the third parameter for this function is set to true.
577: *
578: * ```
579: * $query->join(['alias' => 'table']); // joins table with as alias
580: * $query->join(['alias' => 'another_table']); // joins another_table with as alias
581: * $query->join(['something' => 'different_table'], [], true); // resets joins list
582: * ```
583: *
584: * @param array|string|null $tables list of tables to be joined in the query
585: * @param array $types associative array of type names used to bind values to query
586: * @param bool $overwrite whether to reset joins with passed list or not
587: * @see \Cake\Database\Type
588: * @return $this|array
589: */
590: public function join($tables = null, $types = [], $overwrite = false)
591: {
592: if ($tables === null) {
593: deprecationWarning('Using Query::join() to read state is deprecated. Use Query::clause("join") instead.');
594:
595: return $this->_parts['join'];
596: }
597:
598: if (is_string($tables) || isset($tables['table'])) {
599: $tables = [$tables];
600: }
601:
602: $joins = [];
603: $i = count($this->_parts['join']);
604: foreach ($tables as $alias => $t) {
605: if (!is_array($t)) {
606: $t = ['table' => $t, 'conditions' => $this->newExpr()];
607: }
608:
609: if (!is_string($t['conditions']) && is_callable($t['conditions'])) {
610: $t['conditions'] = $t['conditions']($this->newExpr(), $this);
611: }
612:
613: if (!($t['conditions'] instanceof ExpressionInterface)) {
614: $t['conditions'] = $this->newExpr()->add($t['conditions'], $types);
615: }
616: $alias = is_string($alias) ? $alias : null;
617: $joins[$alias ?: $i++] = $t + ['type' => QueryInterface::JOIN_TYPE_INNER, 'alias' => $alias];
618: }
619:
620: if ($overwrite) {
621: $this->_parts['join'] = $joins;
622: } else {
623: $this->_parts['join'] = array_merge($this->_parts['join'], $joins);
624: }
625:
626: $this->_dirty();
627:
628: return $this;
629: }
630:
631: /**
632: * Remove a join if it has been defined.
633: *
634: * Useful when you are redefining joins or want to re-order
635: * the join clauses.
636: *
637: * @param string $name The alias/name of the join to remove.
638: * @return $this
639: */
640: public function removeJoin($name)
641: {
642: unset($this->_parts['join'][$name]);
643: $this->_dirty();
644:
645: return $this;
646: }
647:
648: /**
649: * Adds a single `LEFT JOIN` clause to the query.
650: *
651: * This is a shorthand method for building joins via `join()`.
652: *
653: * The table name can be passed as a string, or as an array in case it needs to
654: * be aliased:
655: *
656: * ```
657: * // LEFT JOIN authors ON authors.id = posts.author_id
658: * $query->leftJoin('authors', 'authors.id = posts.author_id');
659: *
660: * // LEFT JOIN authors a ON a.id = posts.author_id
661: * $query->leftJoin(['a' => 'authors'], 'a.id = posts.author_id');
662: * ```
663: *
664: * Conditions can be passed as strings, arrays, or expression objects. When
665: * using arrays it is possible to combine them with the `$types` parameter
666: * in order to define how to convert the values:
667: *
668: * ```
669: * $query->leftJoin(['a' => 'articles'], [
670: * 'a.posted >=' => new DateTime('-3 days'),
671: * 'a.published' => true,
672: * 'a.author_id = authors.id'
673: * ], ['a.posted' => 'datetime', 'a.published' => 'boolean']);
674: * ```
675: *
676: * See `join()` for further details on conditions and types.
677: *
678: * @param string|string[] $table The table to join with
679: * @param string|array|\Cake\Database\ExpressionInterface $conditions The conditions
680: * to use for joining.
681: * @param array $types a list of types associated to the conditions used for converting
682: * values to the corresponding database representation.
683: * @return $this
684: */
685: public function leftJoin($table, $conditions = [], $types = [])
686: {
687: return $this->join($this->_makeJoin($table, $conditions, QueryInterface::JOIN_TYPE_LEFT), $types);
688: }
689:
690: /**
691: * Adds a single `RIGHT JOIN` clause to the query.
692: *
693: * This is a shorthand method for building joins via `join()`.
694: *
695: * The arguments of this method are identical to the `leftJoin()` shorthand, please refer
696: * to that methods description for further details.
697: *
698: * @param string|array $table The table to join with
699: * @param string|array|\Cake\Database\ExpressionInterface $conditions The conditions
700: * to use for joining.
701: * @param array $types a list of types associated to the conditions used for converting
702: * values to the corresponding database representation.
703: * @return $this
704: */
705: public function rightJoin($table, $conditions = [], $types = [])
706: {
707: return $this->join($this->_makeJoin($table, $conditions, QueryInterface::JOIN_TYPE_RIGHT), $types);
708: }
709:
710: /**
711: * Adds a single `INNER JOIN` clause to the query.
712: *
713: * This is a shorthand method for building joins via `join()`.
714: *
715: * The arguments of this method are identical to the `leftJoin()` shorthand, please refer
716: * to that methods description for further details.
717: *
718: * @param string|string[] $table The table to join with
719: * @param string|array|\Cake\Database\ExpressionInterface $conditions The conditions
720: * to use for joining.
721: * @param array $types a list of types associated to the conditions used for converting
722: * values to the corresponding database representation.
723: * @return $this
724: */
725: public function innerJoin($table, $conditions = [], $types = [])
726: {
727: return $this->join($this->_makeJoin($table, $conditions, QueryInterface::JOIN_TYPE_INNER), $types);
728: }
729:
730: /**
731: * Returns an array that can be passed to the join method describing a single join clause
732: *
733: * @param string|string[] $table The table to join with
734: * @param string|array|\Cake\Database\ExpressionInterface $conditions The conditions
735: * to use for joining.
736: * @param string $type the join type to use
737: * @return array
738: */
739: protected function _makeJoin($table, $conditions, $type)
740: {
741: $alias = $table;
742:
743: if (is_array($table)) {
744: $alias = key($table);
745: $table = current($table);
746: }
747:
748: return [
749: $alias => [
750: 'table' => $table,
751: 'conditions' => $conditions,
752: 'type' => $type
753: ]
754: ];
755: }
756:
757: /**
758: * Adds a condition or set of conditions to be used in the WHERE clause for this
759: * query. Conditions can be expressed as an array of fields as keys with
760: * comparison operators in it, the values for the array will be used for comparing
761: * the field to such literal. Finally, conditions can be expressed as a single
762: * string or an array of strings.
763: *
764: * When using arrays, each entry will be joined to the rest of the conditions using
765: * an `AND` operator. Consecutive calls to this function will also join the new
766: * conditions specified using the AND operator. Additionally, values can be
767: * expressed using expression objects which can include other query objects.
768: *
769: * Any conditions created with this methods can be used with any `SELECT`, `UPDATE`
770: * and `DELETE` type of queries.
771: *
772: * ### Conditions using operators:
773: *
774: * ```
775: * $query->where([
776: * 'posted >=' => new DateTime('3 days ago'),
777: * 'title LIKE' => 'Hello W%',
778: * 'author_id' => 1,
779: * ], ['posted' => 'datetime']);
780: * ```
781: *
782: * The previous example produces:
783: *
784: * `WHERE posted >= 2012-01-27 AND title LIKE 'Hello W%' AND author_id = 1`
785: *
786: * Second parameter is used to specify what type is expected for each passed
787: * key. Valid types can be used from the mapped with Database\Type class.
788: *
789: * ### Nesting conditions with conjunctions:
790: *
791: * ```
792: * $query->where([
793: * 'author_id !=' => 1,
794: * 'OR' => ['published' => true, 'posted <' => new DateTime('now')],
795: * 'NOT' => ['title' => 'Hello']
796: * ], ['published' => boolean, 'posted' => 'datetime']
797: * ```
798: *
799: * The previous example produces:
800: *
801: * `WHERE author_id = 1 AND (published = 1 OR posted < '2012-02-01') AND NOT (title = 'Hello')`
802: *
803: * You can nest conditions using conjunctions as much as you like. Sometimes, you
804: * may want to define 2 different options for the same key, in that case, you can
805: * wrap each condition inside a new array:
806: *
807: * `$query->where(['OR' => [['published' => false], ['published' => true]])`
808: *
809: * Would result in:
810: *
811: * `WHERE (published = false) OR (published = true)`
812: *
813: * Keep in mind that every time you call where() with the third param set to false
814: * (default), it will join the passed conditions to the previous stored list using
815: * the `AND` operator. Also, using the same array key twice in consecutive calls to
816: * this method will not override the previous value.
817: *
818: * ### Using expressions objects:
819: *
820: * ```
821: * $exp = $query->newExpr()->add(['id !=' => 100, 'author_id' != 1])->tieWith('OR');
822: * $query->where(['published' => true], ['published' => 'boolean'])->where($exp);
823: * ```
824: *
825: * The previous example produces:
826: *
827: * `WHERE (id != 100 OR author_id != 1) AND published = 1`
828: *
829: * Other Query objects that be used as conditions for any field.
830: *
831: * ### Adding conditions in multiple steps:
832: *
833: * You can use callable functions to construct complex expressions, functions
834: * receive as first argument a new QueryExpression object and this query instance
835: * as second argument. Functions must return an expression object, that will be
836: * added the list of conditions for the query using the `AND` operator.
837: *
838: * ```
839: * $query
840: * ->where(['title !=' => 'Hello World'])
841: * ->where(function ($exp, $query) {
842: * $or = $exp->or_(['id' => 1]);
843: * $and = $exp->and_(['id >' => 2, 'id <' => 10]);
844: * return $or->add($and);
845: * });
846: * ```
847: *
848: * * The previous example produces:
849: *
850: * `WHERE title != 'Hello World' AND (id = 1 OR (id > 2 AND id < 10))`
851: *
852: * ### Conditions as strings:
853: *
854: * ```
855: * $query->where(['articles.author_id = authors.id', 'modified IS NULL']);
856: * ```
857: *
858: * The previous example produces:
859: *
860: * `WHERE articles.author_id = authors.id AND modified IS NULL`
861: *
862: * Please note that when using the array notation or the expression objects, all
863: * *values* will be correctly quoted and transformed to the correspondent database
864: * data type automatically for you, thus securing your application from SQL injections.
865: * The keys however, are not treated as unsafe input, and should be sanitized/whitelisted.
866: *
867: * If you use string conditions make sure that your values are correctly quoted.
868: * The safest thing you can do is to never use string conditions.
869: *
870: * @param string|array|\Cake\Database\ExpressionInterface|callable|null $conditions The conditions to filter on.
871: * @param array $types associative array of type names used to bind values to query
872: * @param bool $overwrite whether to reset conditions with passed list or not
873: * @see \Cake\Database\Type
874: * @see \Cake\Database\Expression\QueryExpression
875: * @return $this
876: */
877: public function where($conditions = null, $types = [], $overwrite = false)
878: {
879: if ($overwrite) {
880: $this->_parts['where'] = $this->newExpr();
881: }
882: $this->_conjugate('where', $conditions, 'AND', $types);
883:
884: return $this;
885: }
886:
887: /**
888: * Convenience method that adds a NOT NULL condition to the query
889: *
890: * @param array|string|\Cake\Database\ExpressionInterface $fields A single field or expressions or a list of them that should be not null
891: * @return $this
892: */
893: public function whereNotNull($fields)
894: {
895: if (!is_array($fields)) {
896: $fields = [$fields];
897: }
898:
899: $exp = $this->newExpr();
900:
901: foreach ($fields as $field) {
902: $exp->isNotNull($field);
903: }
904:
905: return $this->where($exp);
906: }
907:
908: /**
909: * Convenience method that adds a IS NULL condition to the query
910: *
911: * @param array|string|\Cake\Database\ExpressionInterface $fields A single field or expressions or a list of them that should be null
912: * @return $this
913: */
914: public function whereNull($fields)
915: {
916: if (!is_array($fields)) {
917: $fields = [$fields];
918: }
919:
920: $exp = $this->newExpr();
921:
922: foreach ($fields as $field) {
923: $exp->isNull($field);
924: }
925:
926: return $this->where($exp);
927: }
928:
929: /**
930: * Adds an IN condition or set of conditions to be used in the WHERE clause for this
931: * query.
932: *
933: * This method does allow empty inputs in contrast to where() if you set
934: * 'allowEmpty' to true.
935: * Be careful about using it without proper sanity checks.
936: *
937: * Options:
938: * - `types` - Associative array of type names used to bind values to query
939: * - `allowEmpty` - Allow empty array.
940: *
941: * @param string $field Field
942: * @param array $values Array of values
943: * @param array $options Options
944: * @return $this
945: */
946: public function whereInList($field, array $values, array $options = [])
947: {
948: $options += [
949: 'types' => [],
950: 'allowEmpty' => false,
951: ];
952:
953: if ($options['allowEmpty'] && !$values) {
954: return $this->where('1=0');
955: }
956:
957: return $this->where([$field . ' IN' => $values], $options['types']);
958: }
959:
960: /**
961: * Adds a NOT IN condition or set of conditions to be used in the WHERE clause for this
962: * query.
963: *
964: * This method does allow empty inputs in contrast to where() if you set
965: * 'allowEmpty' to true.
966: * Be careful about using it without proper sanity checks.
967: *
968: * @param string $field Field
969: * @param array $values Array of values
970: * @param array $options Options
971: * @return $this
972: */
973: public function whereNotInList($field, array $values, array $options = [])
974: {
975: $options += [
976: 'types' => [],
977: 'allowEmpty' => false,
978: ];
979:
980: if ($options['allowEmpty'] && !$values) {
981: return $this->where([$field . ' IS NOT' => null]);
982: }
983:
984: return $this->where([$field . ' NOT IN' => $values], $options['types']);
985: }
986:
987: /**
988: * Connects any previously defined set of conditions to the provided list
989: * using the AND operator. This function accepts the conditions list in the same
990: * format as the method `where` does, hence you can use arrays, expression objects
991: * callback functions or strings.
992: *
993: * It is important to notice that when calling this function, any previous set
994: * of conditions defined for this query will be treated as a single argument for
995: * the AND operator. This function will not only operate the most recently defined
996: * condition, but all the conditions as a whole.
997: *
998: * When using an array for defining conditions, creating constraints form each
999: * array entry will use the same logic as with the `where()` function. This means
1000: * that each array entry will be joined to the other using the AND operator, unless
1001: * you nest the conditions in the array using other operator.
1002: *
1003: * ### Examples:
1004: *
1005: * ```
1006: * $query->where(['title' => 'Hello World')->andWhere(['author_id' => 1]);
1007: * ```
1008: *
1009: * Will produce:
1010: *
1011: * `WHERE title = 'Hello World' AND author_id = 1`
1012: *
1013: * ```
1014: * $query
1015: * ->where(['OR' => ['published' => false, 'published is NULL']])
1016: * ->andWhere(['author_id' => 1, 'comments_count >' => 10])
1017: * ```
1018: *
1019: * Produces:
1020: *
1021: * `WHERE (published = 0 OR published IS NULL) AND author_id = 1 AND comments_count > 10`
1022: *
1023: * ```
1024: * $query
1025: * ->where(['title' => 'Foo'])
1026: * ->andWhere(function ($exp, $query) {
1027: * return $exp
1028: * ->or_(['author_id' => 1])
1029: * ->add(['author_id' => 2]);
1030: * });
1031: * ```
1032: *
1033: * Generates the following conditions:
1034: *
1035: * `WHERE (title = 'Foo') AND (author_id = 1 OR author_id = 2)`
1036: *
1037: * @param string|array|\Cake\Database\ExpressionInterface|callable $conditions The conditions to add with AND.
1038: * @param array $types associative array of type names used to bind values to query
1039: * @see \Cake\Database\Query::where()
1040: * @see \Cake\Database\Type
1041: * @return $this
1042: */
1043: public function andWhere($conditions, $types = [])
1044: {
1045: $this->_conjugate('where', $conditions, 'AND', $types);
1046:
1047: return $this;
1048: }
1049:
1050: /**
1051: * Connects any previously defined set of conditions to the provided list
1052: * using the OR operator. This function accepts the conditions list in the same
1053: * format as the method `where` does, hence you can use arrays, expression objects
1054: * callback functions or strings.
1055: *
1056: * It is important to notice that when calling this function, any previous set
1057: * of conditions defined for this query will be treated as a single argument for
1058: * the OR operator. This function will not only operate the most recently defined
1059: * condition, but all the conditions as a whole.
1060: *
1061: * When using an array for defining conditions, creating constraints form each
1062: * array entry will use the same logic as with the `where()` function. This means
1063: * that each array entry will be joined to the other using the OR operator, unless
1064: * you nest the conditions in the array using other operator.
1065: *
1066: * ### Examples:
1067: *
1068: * ```
1069: * $query->where(['title' => 'Hello World')->orWhere(['title' => 'Foo']);
1070: * ```
1071: *
1072: * Will produce:
1073: *
1074: * `WHERE title = 'Hello World' OR title = 'Foo'`
1075: *
1076: * ```
1077: * $query
1078: * ->where(['OR' => ['published' => false, 'published is NULL']])
1079: * ->orWhere(['author_id' => 1, 'comments_count >' => 10])
1080: * ```
1081: *
1082: * Produces:
1083: *
1084: * `WHERE (published = 0 OR published IS NULL) OR (author_id = 1 AND comments_count > 10)`
1085: *
1086: * ```
1087: * $query
1088: * ->where(['title' => 'Foo'])
1089: * ->orWhere(function ($exp, $query) {
1090: * return $exp
1091: * ->or_(['author_id' => 1])
1092: * ->add(['author_id' => 2]);
1093: * });
1094: * ```
1095: *
1096: * Generates the following conditions:
1097: *
1098: * `WHERE (title = 'Foo') OR (author_id = 1 OR author_id = 2)`
1099: *
1100: * @param string|array|\Cake\Database\ExpressionInterface|callable $conditions The conditions to add with OR.
1101: * @param array $types associative array of type names used to bind values to query
1102: * @see \Cake\Database\Query::where()
1103: * @see \Cake\Database\Type
1104: * @return $this
1105: * @deprecated 3.5.0 This method creates hard to predict SQL based on the current query state.
1106: * Use `Query::where()` instead as it has more predicatable and easier to understand behavior.
1107: */
1108: public function orWhere($conditions, $types = [])
1109: {
1110: deprecationWarning(
1111: 'Query::orWhere() is deprecated as it creates hard to predict SQL based on the ' .
1112: 'current query state. Use `Query::where()` instead.'
1113: );
1114: $this->_conjugate('where', $conditions, 'OR', $types);
1115:
1116: return $this;
1117: }
1118:
1119: /**
1120: * Adds a single or multiple fields to be used in the ORDER clause for this query.
1121: * Fields can be passed as an array of strings, array of expression
1122: * objects, a single expression or a single string.
1123: *
1124: * If an array is passed, keys will be used as the field itself and the value will
1125: * represent the order in which such field should be ordered. When called multiple
1126: * times with the same fields as key, the last order definition will prevail over
1127: * the others.
1128: *
1129: * By default this function will append any passed argument to the list of fields
1130: * to be selected, unless the second argument is set to true.
1131: *
1132: * ### Examples:
1133: *
1134: * ```
1135: * $query->order(['title' => 'DESC', 'author_id' => 'ASC']);
1136: * ```
1137: *
1138: * Produces:
1139: *
1140: * `ORDER BY title DESC, author_id ASC`
1141: *
1142: * ```
1143: * $query->order(['title' => 'DESC NULLS FIRST'])->order('author_id');
1144: * ```
1145: *
1146: * Will generate:
1147: *
1148: * `ORDER BY title DESC NULLS FIRST, author_id`
1149: *
1150: * ```
1151: * $expression = $query->newExpr()->add(['id % 2 = 0']);
1152: * $query->order($expression)->order(['title' => 'ASC']);
1153: * ```
1154: *
1155: * and
1156: *
1157: * ```
1158: * $query->order(function ($exp, $query) {
1159: * return [$exp->add(['id % 2 = 0']), 'title' => 'ASC'];
1160: * });
1161: * ```
1162: *
1163: * Will both become:
1164: *
1165: * `ORDER BY (id %2 = 0), title ASC`
1166: *
1167: * Order fields/directions are not sanitized by the query builder.
1168: * You should use a whitelist of fields/directions when passing
1169: * in user-supplied data to `order()`.
1170: *
1171: * If you need to set complex expressions as order conditions, you
1172: * should use `orderAsc()` or `orderDesc()`.
1173: *
1174: * @param array|\Cake\Database\ExpressionInterface|callable|string $fields fields to be added to the list
1175: * @param bool $overwrite whether to reset order with field list or not
1176: * @return $this
1177: */
1178: public function order($fields, $overwrite = false)
1179: {
1180: if ($overwrite) {
1181: $this->_parts['order'] = null;
1182: }
1183:
1184: if (!$fields) {
1185: return $this;
1186: }
1187:
1188: if (!$this->_parts['order']) {
1189: $this->_parts['order'] = new OrderByExpression();
1190: }
1191: $this->_conjugate('order', $fields, '', []);
1192:
1193: return $this;
1194: }
1195:
1196: /**
1197: * Add an ORDER BY clause with an ASC direction.
1198: *
1199: * This method allows you to set complex expressions
1200: * as order conditions unlike order()
1201: *
1202: * Order fields are not suitable for use with user supplied data as they are
1203: * not sanitized by the query builder.
1204: *
1205: * @param string|\Cake\Database\Expression\QueryExpression $field The field to order on.
1206: * @param bool $overwrite Whether or not to reset the order clauses.
1207: * @return $this
1208: */
1209: public function orderAsc($field, $overwrite = false)
1210: {
1211: if ($overwrite) {
1212: $this->_parts['order'] = null;
1213: }
1214: if (!$field) {
1215: return $this;
1216: }
1217:
1218: if (!$this->_parts['order']) {
1219: $this->_parts['order'] = new OrderByExpression();
1220: }
1221: $this->_parts['order']->add(new OrderClauseExpression($field, 'ASC'));
1222:
1223: return $this;
1224: }
1225:
1226: /**
1227: * Add an ORDER BY clause with a DESC direction.
1228: *
1229: * This method allows you to set complex expressions
1230: * as order conditions unlike order()
1231: *
1232: * Order fields are not suitable for use with user supplied data as they are
1233: * not sanitized by the query builder.
1234: *
1235: * @param string|\Cake\Database\Expression\QueryExpression $field The field to order on.
1236: * @param bool $overwrite Whether or not to reset the order clauses.
1237: * @return $this
1238: */
1239: public function orderDesc($field, $overwrite = false)
1240: {
1241: if ($overwrite) {
1242: $this->_parts['order'] = null;
1243: }
1244: if (!$field) {
1245: return $this;
1246: }
1247:
1248: if (!$this->_parts['order']) {
1249: $this->_parts['order'] = new OrderByExpression();
1250: }
1251: $this->_parts['order']->add(new OrderClauseExpression($field, 'DESC'));
1252:
1253: return $this;
1254: }
1255:
1256: /**
1257: * Adds a single or multiple fields to be used in the GROUP BY clause for this query.
1258: * Fields can be passed as an array of strings, array of expression
1259: * objects, a single expression or a single string.
1260: *
1261: * By default this function will append any passed argument to the list of fields
1262: * to be grouped, unless the second argument is set to true.
1263: *
1264: * ### Examples:
1265: *
1266: * ```
1267: * // Produces GROUP BY id, title
1268: * $query->group(['id', 'title']);
1269: *
1270: * // Produces GROUP BY title
1271: * $query->group('title');
1272: * ```
1273: *
1274: * Group fields are not suitable for use with user supplied data as they are
1275: * not sanitized by the query builder.
1276: *
1277: * @param array|\Cake\Database\ExpressionInterface|string $fields fields to be added to the list
1278: * @param bool $overwrite whether to reset fields with passed list or not
1279: * @return $this
1280: */
1281: public function group($fields, $overwrite = false)
1282: {
1283: if ($overwrite) {
1284: $this->_parts['group'] = [];
1285: }
1286:
1287: if (!is_array($fields)) {
1288: $fields = [$fields];
1289: }
1290:
1291: $this->_parts['group'] = array_merge($this->_parts['group'], array_values($fields));
1292: $this->_dirty();
1293:
1294: return $this;
1295: }
1296:
1297: /**
1298: * Adds a condition or set of conditions to be used in the `HAVING` clause for this
1299: * query. This method operates in exactly the same way as the method `where()`
1300: * does. Please refer to its documentation for an insight on how to using each
1301: * parameter.
1302: *
1303: * Having fields are not suitable for use with user supplied data as they are
1304: * not sanitized by the query builder.
1305: *
1306: * @param string|array|\Cake\Database\ExpressionInterface|callable|null $conditions The having conditions.
1307: * @param array $types associative array of type names used to bind values to query
1308: * @param bool $overwrite whether to reset conditions with passed list or not
1309: * @see \Cake\Database\Query::where()
1310: * @return $this
1311: */
1312: public function having($conditions = null, $types = [], $overwrite = false)
1313: {
1314: if ($overwrite) {
1315: $this->_parts['having'] = $this->newExpr();
1316: }
1317: $this->_conjugate('having', $conditions, 'AND', $types);
1318:
1319: return $this;
1320: }
1321:
1322: /**
1323: * Connects any previously defined set of conditions to the provided list
1324: * using the AND operator in the HAVING clause. This method operates in exactly
1325: * the same way as the method `andWhere()` does. Please refer to its
1326: * documentation for an insight on how to using each parameter.
1327: *
1328: * Having fields are not suitable for use with user supplied data as they are
1329: * not sanitized by the query builder.
1330: *
1331: * @param string|array|\Cake\Database\ExpressionInterface|callable $conditions The AND conditions for HAVING.
1332: * @param array $types associative array of type names used to bind values to query
1333: * @see \Cake\Database\Query::andWhere()
1334: * @return $this
1335: */
1336: public function andHaving($conditions, $types = [])
1337: {
1338: $this->_conjugate('having', $conditions, 'AND', $types);
1339:
1340: return $this;
1341: }
1342:
1343: /**
1344: * Connects any previously defined set of conditions to the provided list
1345: * using the OR operator in the HAVING clause. This method operates in exactly
1346: * the same way as the method `orWhere()` does. Please refer to its
1347: * documentation for an insight on how to using each parameter.
1348: *
1349: * Having fields are not suitable for use with user supplied data as they are
1350: * not sanitized by the query builder.
1351: *
1352: * @param string|array|\Cake\Database\ExpressionInterface|callable $conditions The OR conditions for HAVING.
1353: * @param array $types associative array of type names used to bind values to query.
1354: * @see \Cake\Database\Query::orWhere()
1355: * @return $this
1356: * @deprecated 3.5.0 This method creates hard to predict SQL based on the current query state.
1357: * Use `Query::having()` instead as it has more predicatable and easier to understand behavior.
1358: */
1359: public function orHaving($conditions, $types = [])
1360: {
1361: deprecationWarning('Query::orHaving() is deprecated. Use Query::having() instead.');
1362: $this->_conjugate('having', $conditions, 'OR', $types);
1363:
1364: return $this;
1365: }
1366:
1367: /**
1368: * Set the page of results you want.
1369: *
1370: * This method provides an easier to use interface to set the limit + offset
1371: * in the record set you want as results. If empty the limit will default to
1372: * the existing limit clause, and if that too is empty, then `25` will be used.
1373: *
1374: * Pages must start at 1.
1375: *
1376: * @param int $num The page number you want.
1377: * @param int|null $limit The number of rows you want in the page. If null
1378: * the current limit clause will be used.
1379: * @return $this
1380: * @throws \InvalidArgumentException If page number < 1.
1381: */
1382: public function page($num, $limit = null)
1383: {
1384: if ($num < 1) {
1385: throw new InvalidArgumentException('Pages must start at 1.');
1386: }
1387: if ($limit !== null) {
1388: $this->limit($limit);
1389: }
1390: $limit = $this->clause('limit');
1391: if ($limit === null) {
1392: $limit = 25;
1393: $this->limit($limit);
1394: }
1395: $offset = ($num - 1) * $limit;
1396: if (PHP_INT_MAX <= $offset) {
1397: $offset = PHP_INT_MAX;
1398: }
1399: $this->offset((int)$offset);
1400:
1401: return $this;
1402: }
1403:
1404: /**
1405: * Sets the number of records that should be retrieved from database,
1406: * accepts an integer or an expression object that evaluates to an integer.
1407: * In some databases, this operation might not be supported or will require
1408: * the query to be transformed in order to limit the result set size.
1409: *
1410: * ### Examples
1411: *
1412: * ```
1413: * $query->limit(10) // generates LIMIT 10
1414: * $query->limit($query->newExpr()->add(['1 + 1'])); // LIMIT (1 + 1)
1415: * ```
1416: *
1417: * @param int|\Cake\Database\ExpressionInterface $num number of records to be returned
1418: * @return $this
1419: */
1420: public function limit($num)
1421: {
1422: $this->_dirty();
1423: if ($num !== null && !is_object($num)) {
1424: $num = (int)$num;
1425: }
1426: $this->_parts['limit'] = $num;
1427:
1428: return $this;
1429: }
1430:
1431: /**
1432: * Sets the number of records that should be skipped from the original result set
1433: * This is commonly used for paginating large results. Accepts an integer or an
1434: * expression object that evaluates to an integer.
1435: *
1436: * In some databases, this operation might not be supported or will require
1437: * the query to be transformed in order to limit the result set size.
1438: *
1439: * ### Examples
1440: *
1441: * ```
1442: * $query->offset(10) // generates OFFSET 10
1443: * $query->offset($query->newExpr()->add(['1 + 1'])); // OFFSET (1 + 1)
1444: * ```
1445: *
1446: * @param int|\Cake\Database\ExpressionInterface $num number of records to be skipped
1447: * @return $this
1448: */
1449: public function offset($num)
1450: {
1451: $this->_dirty();
1452: if ($num !== null && !is_object($num)) {
1453: $num = (int)$num;
1454: }
1455: $this->_parts['offset'] = $num;
1456:
1457: return $this;
1458: }
1459:
1460: /**
1461: * Adds a complete query to be used in conjunction with an UNION operator with
1462: * this query. This is used to combine the result set of this query with the one
1463: * that will be returned by the passed query. You can add as many queries as you
1464: * required by calling multiple times this method with different queries.
1465: *
1466: * By default, the UNION operator will remove duplicate rows, if you wish to include
1467: * every row for all queries, use unionAll().
1468: *
1469: * ### Examples
1470: *
1471: * ```
1472: * $union = (new Query($conn))->select(['id', 'title'])->from(['a' => 'articles']);
1473: * $query->select(['id', 'name'])->from(['d' => 'things'])->union($union);
1474: * ```
1475: *
1476: * Will produce:
1477: *
1478: * `SELECT id, name FROM things d UNION SELECT id, title FROM articles a`
1479: *
1480: * @param string|\Cake\Database\Query $query full SQL query to be used in UNION operator
1481: * @param bool $overwrite whether to reset the list of queries to be operated or not
1482: * @return $this
1483: */
1484: public function union($query, $overwrite = false)
1485: {
1486: if ($overwrite) {
1487: $this->_parts['union'] = [];
1488: }
1489: $this->_parts['union'][] = [
1490: 'all' => false,
1491: 'query' => $query
1492: ];
1493: $this->_dirty();
1494:
1495: return $this;
1496: }
1497:
1498: /**
1499: * Adds a complete query to be used in conjunction with the UNION ALL operator with
1500: * this query. This is used to combine the result set of this query with the one
1501: * that will be returned by the passed query. You can add as many queries as you
1502: * required by calling multiple times this method with different queries.
1503: *
1504: * Unlike UNION, UNION ALL will not remove duplicate rows.
1505: *
1506: * ```
1507: * $union = (new Query($conn))->select(['id', 'title'])->from(['a' => 'articles']);
1508: * $query->select(['id', 'name'])->from(['d' => 'things'])->unionAll($union);
1509: * ```
1510: *
1511: * Will produce:
1512: *
1513: * `SELECT id, name FROM things d UNION ALL SELECT id, title FROM articles a`
1514: *
1515: * @param string|\Cake\Database\Query $query full SQL query to be used in UNION operator
1516: * @param bool $overwrite whether to reset the list of queries to be operated or not
1517: * @return $this
1518: */
1519: public function unionAll($query, $overwrite = false)
1520: {
1521: if ($overwrite) {
1522: $this->_parts['union'] = [];
1523: }
1524: $this->_parts['union'][] = [
1525: 'all' => true,
1526: 'query' => $query
1527: ];
1528: $this->_dirty();
1529:
1530: return $this;
1531: }
1532:
1533: /**
1534: * Create an insert query.
1535: *
1536: * Note calling this method will reset any data previously set
1537: * with Query::values().
1538: *
1539: * @param array $columns The columns to insert into.
1540: * @param string[] $types A map between columns & their datatypes.
1541: * @return $this
1542: * @throws \RuntimeException When there are 0 columns.
1543: */
1544: public function insert(array $columns, array $types = [])
1545: {
1546: if (empty($columns)) {
1547: throw new RuntimeException('At least 1 column is required to perform an insert.');
1548: }
1549: $this->_dirty();
1550: $this->_type = 'insert';
1551: $this->_parts['insert'][1] = $columns;
1552: if (!$this->_parts['values']) {
1553: $this->_parts['values'] = new ValuesExpression($columns, $this->getTypeMap()->setTypes($types));
1554: } else {
1555: $this->_parts['values']->setColumns($columns);
1556: }
1557:
1558: return $this;
1559: }
1560:
1561: /**
1562: * Set the table name for insert queries.
1563: *
1564: * @param string $table The table name to insert into.
1565: * @return $this
1566: */
1567: public function into($table)
1568: {
1569: $this->_dirty();
1570: $this->_type = 'insert';
1571: $this->_parts['insert'][0] = $table;
1572:
1573: return $this;
1574: }
1575:
1576: /**
1577: * Creates an expression that refers to an identifier. Identifiers are used to refer to field names and allow
1578: * the SQL compiler to apply quotes or escape the identifier.
1579: *
1580: * The value is used as is, and you might be required to use aliases or include the table reference in
1581: * the identifier. Do not use this method to inject SQL methods or logical statements.
1582: *
1583: * ### Example
1584: *
1585: * ```
1586: * $query->newExpr()->lte('count', $query->identifier('total'));
1587: * ```
1588: *
1589: * @param string $identifier The identifier for an expression
1590: * @return \Cake\Database\ExpressionInterface
1591: */
1592: public function identifier($identifier)
1593: {
1594: return new IdentifierExpression($identifier);
1595: }
1596:
1597: /**
1598: * Set the values for an insert query.
1599: *
1600: * Multi inserts can be performed by calling values() more than one time,
1601: * or by providing an array of value sets. Additionally $data can be a Query
1602: * instance to insert data from another SELECT statement.
1603: *
1604: * @param array|\Cake\Database\Query $data The data to insert.
1605: * @return $this
1606: * @throws \Cake\Database\Exception if you try to set values before declaring columns.
1607: * Or if you try to set values on non-insert queries.
1608: */
1609: public function values($data)
1610: {
1611: if ($this->_type !== 'insert') {
1612: throw new Exception(
1613: 'You cannot add values before defining columns to use.'
1614: );
1615: }
1616: if (empty($this->_parts['insert'])) {
1617: throw new Exception(
1618: 'You cannot add values before defining columns to use.'
1619: );
1620: }
1621:
1622: $this->_dirty();
1623: if ($data instanceof ValuesExpression) {
1624: $this->_parts['values'] = $data;
1625:
1626: return $this;
1627: }
1628:
1629: $this->_parts['values']->add($data);
1630:
1631: return $this;
1632: }
1633:
1634: /**
1635: * Create an update query.
1636: *
1637: * Can be combined with set() and where() methods to create update queries.
1638: *
1639: * @param string|\Cake\Database\ExpressionInterface $table The table you want to update.
1640: * @return $this
1641: */
1642: public function update($table)
1643: {
1644: if (!is_string($table) && !($table instanceof ExpressionInterface)) {
1645: $text = 'Table must be of type string or "%s", got "%s"';
1646: $message = sprintf($text, ExpressionInterface::class, gettype($table));
1647: throw new InvalidArgumentException($message);
1648: }
1649:
1650: $this->_dirty();
1651: $this->_type = 'update';
1652: $this->_parts['update'][0] = $table;
1653:
1654: return $this;
1655: }
1656:
1657: /**
1658: * Set one or many fields to update.
1659: *
1660: * ### Examples
1661: *
1662: * Passing a string:
1663: *
1664: * ```
1665: * $query->update('articles')->set('title', 'The Title');
1666: * ```
1667: *
1668: * Passing an array:
1669: *
1670: * ```
1671: * $query->update('articles')->set(['title' => 'The Title'], ['title' => 'string']);
1672: * ```
1673: *
1674: * Passing a callable:
1675: *
1676: * ```
1677: * $query->update('articles')->set(function ($exp) {
1678: * return $exp->eq('title', 'The title', 'string');
1679: * });
1680: * ```
1681: *
1682: * @param string|array|callable|\Cake\Database\Expression\QueryExpression $key The column name or array of keys
1683: * + values to set. This can also be a QueryExpression containing a SQL fragment.
1684: * It can also be a callable, that is required to return an expression object.
1685: * @param mixed $value The value to update $key to. Can be null if $key is an
1686: * array or QueryExpression. When $key is an array, this parameter will be
1687: * used as $types instead.
1688: * @param array $types The column types to treat data as.
1689: * @return $this
1690: */
1691: public function set($key, $value = null, $types = [])
1692: {
1693: if (empty($this->_parts['set'])) {
1694: $this->_parts['set'] = $this->newExpr()->setConjunction(',');
1695: }
1696:
1697: if ($this->_parts['set']->isCallable($key)) {
1698: $exp = $this->newExpr()->setConjunction(',');
1699: $this->_parts['set']->add($key($exp));
1700:
1701: return $this;
1702: }
1703:
1704: if (is_array($key) || $key instanceof ExpressionInterface) {
1705: $types = (array)$value;
1706: $this->_parts['set']->add($key, $types);
1707:
1708: return $this;
1709: }
1710:
1711: if (is_string($types) && is_string($key)) {
1712: $types = [$key => $types];
1713: }
1714: $this->_parts['set']->eq($key, $value, $types);
1715:
1716: return $this;
1717: }
1718:
1719: /**
1720: * Create a delete query.
1721: *
1722: * Can be combined with from(), where() and other methods to
1723: * create delete queries with specific conditions.
1724: *
1725: * @param string|null $table The table to use when deleting.
1726: * @return $this
1727: */
1728: public function delete($table = null)
1729: {
1730: $this->_dirty();
1731: $this->_type = 'delete';
1732: if ($table !== null) {
1733: $this->from($table);
1734: }
1735:
1736: return $this;
1737: }
1738:
1739: /**
1740: * A string or expression that will be appended to the generated query
1741: *
1742: * ### Examples:
1743: * ```
1744: * $query->select('id')->where(['author_id' => 1])->epilog('FOR UPDATE');
1745: * $query
1746: * ->insert('articles', ['title'])
1747: * ->values(['author_id' => 1])
1748: * ->epilog('RETURNING id');
1749: * ```
1750: *
1751: * Epliog content is raw SQL and not suitable for use with user supplied data.
1752: *
1753: * @param string|\Cake\Database\Expression\QueryExpression|null $expression The expression to be appended
1754: * @return $this
1755: */
1756: public function epilog($expression = null)
1757: {
1758: $this->_dirty();
1759: $this->_parts['epilog'] = $expression;
1760:
1761: return $this;
1762: }
1763:
1764: /**
1765: * Returns the type of this query (select, insert, update, delete)
1766: *
1767: * @return string
1768: */
1769: public function type()
1770: {
1771: return $this->_type;
1772: }
1773:
1774: /**
1775: * Returns a new QueryExpression object. This is a handy function when
1776: * building complex queries using a fluent interface. You can also override
1777: * this function in subclasses to use a more specialized QueryExpression class
1778: * if required.
1779: *
1780: * You can optionally pass a single raw SQL string or an array or expressions in
1781: * any format accepted by \Cake\Database\Expression\QueryExpression:
1782: *
1783: * ```
1784: * $expression = $query->newExpr(); // Returns an empty expression object
1785: * $expression = $query->newExpr('Table.column = Table2.column'); // Return a raw SQL expression
1786: * ```
1787: *
1788: * @param mixed $rawExpression A string, array or anything you want wrapped in an expression object
1789: * @return \Cake\Database\Expression\QueryExpression
1790: */
1791: public function newExpr($rawExpression = null)
1792: {
1793: $expression = new QueryExpression([], $this->getTypeMap());
1794:
1795: if ($rawExpression !== null) {
1796: $expression->add($rawExpression);
1797: }
1798:
1799: return $expression;
1800: }
1801:
1802: /**
1803: * Returns an instance of a functions builder object that can be used for
1804: * generating arbitrary SQL functions.
1805: *
1806: * ### Example:
1807: *
1808: * ```
1809: * $query->func()->count('*');
1810: * $query->func()->dateDiff(['2012-01-05', '2012-01-02'])
1811: * ```
1812: *
1813: * @return \Cake\Database\FunctionsBuilder
1814: */
1815: public function func()
1816: {
1817: if ($this->_functionsBuilder === null) {
1818: $this->_functionsBuilder = new FunctionsBuilder();
1819: }
1820:
1821: return $this->_functionsBuilder;
1822: }
1823:
1824: /**
1825: * Executes this query and returns a results iterator. This function is required
1826: * for implementing the IteratorAggregate interface and allows the query to be
1827: * iterated without having to call execute() manually, thus making it look like
1828: * a result set instead of the query itself.
1829: *
1830: * @return \Cake\Database\StatementInterface|null
1831: */
1832: public function getIterator()
1833: {
1834: if ($this->_iterator === null || $this->_dirty) {
1835: $this->_iterator = $this->execute();
1836: }
1837:
1838: return $this->_iterator;
1839: }
1840:
1841: /**
1842: * Returns any data that was stored in the specified clause. This is useful for
1843: * modifying any internal part of the query and it is used by the SQL dialects
1844: * to transform the query accordingly before it is executed. The valid clauses that
1845: * can be retrieved are: delete, update, set, insert, values, select, distinct,
1846: * from, join, set, where, group, having, order, limit, offset and union.
1847: *
1848: * The return value for each of those parts may vary. Some clauses use QueryExpression
1849: * to internally store their state, some use arrays and others may use booleans or
1850: * integers. This is summary of the return types for each clause.
1851: *
1852: * - update: string The name of the table to update
1853: * - set: QueryExpression
1854: * - insert: array, will return an array containing the table + columns.
1855: * - values: ValuesExpression
1856: * - select: array, will return empty array when no fields are set
1857: * - distinct: boolean
1858: * - from: array of tables
1859: * - join: array
1860: * - set: array
1861: * - where: QueryExpression, returns null when not set
1862: * - group: array
1863: * - having: QueryExpression, returns null when not set
1864: * - order: OrderByExpression, returns null when not set
1865: * - limit: integer or QueryExpression, null when not set
1866: * - offset: integer or QueryExpression, null when not set
1867: * - union: array
1868: *
1869: * @param string $name name of the clause to be returned
1870: * @return mixed
1871: * @throws \InvalidArgumentException When the named clause does not exist.
1872: */
1873: public function clause($name)
1874: {
1875: if (!array_key_exists($name, $this->_parts)) {
1876: $clauses = implode(', ', array_keys($this->_parts));
1877: throw new InvalidArgumentException("The '$name' clause is not defined. Valid clauses are: $clauses");
1878: }
1879:
1880: return $this->_parts[$name];
1881: }
1882:
1883: /**
1884: * Registers a callback to be executed for each result that is fetched from the
1885: * result set, the callback function will receive as first parameter an array with
1886: * the raw data from the database for every row that is fetched and must return the
1887: * row with any possible modifications.
1888: *
1889: * Callbacks will be executed lazily, if only 3 rows are fetched for database it will
1890: * called 3 times, event though there might be more rows to be fetched in the cursor.
1891: *
1892: * Callbacks are stacked in the order they are registered, if you wish to reset the stack
1893: * the call this function with the second parameter set to true.
1894: *
1895: * If you wish to remove all decorators from the stack, set the first parameter
1896: * to null and the second to true.
1897: *
1898: * ### Example
1899: *
1900: * ```
1901: * $query->decorateResults(function ($row) {
1902: * $row['order_total'] = $row['subtotal'] + ($row['subtotal'] * $row['tax']);
1903: * return $row;
1904: * });
1905: * ```
1906: *
1907: * @param callable|null $callback The callback to invoke when results are fetched.
1908: * @param bool $overwrite Whether or not this should append or replace all existing decorators.
1909: * @return $this
1910: */
1911: public function decorateResults($callback, $overwrite = false)
1912: {
1913: if ($overwrite) {
1914: $this->_resultDecorators = [];
1915: }
1916:
1917: if ($callback !== null) {
1918: $this->_resultDecorators[] = $callback;
1919: }
1920:
1921: return $this;
1922: }
1923:
1924: /**
1925: * This function works similar to the traverse() function, with the difference
1926: * that it does a full depth traversal of the entire expression tree. This will execute
1927: * the provided callback function for each ExpressionInterface object that is
1928: * stored inside this query at any nesting depth in any part of the query.
1929: *
1930: * Callback will receive as first parameter the currently visited expression.
1931: *
1932: * @param callable $callback the function to be executed for each ExpressionInterface
1933: * found inside this query.
1934: * @return $this|null
1935: */
1936: public function traverseExpressions(callable $callback)
1937: {
1938: $visitor = function ($expression) use (&$visitor, $callback) {
1939: if (is_array($expression)) {
1940: foreach ($expression as $e) {
1941: $visitor($e);
1942: }
1943:
1944: return null;
1945: }
1946:
1947: if ($expression instanceof ExpressionInterface) {
1948: $expression->traverse($visitor);
1949:
1950: if (!($expression instanceof self)) {
1951: $callback($expression);
1952: }
1953: }
1954: };
1955:
1956: return $this->traverse($visitor);
1957: }
1958:
1959: /**
1960: * Associates a query placeholder to a value and a type.
1961: *
1962: * ```
1963: * $query->bind(':id', 1, 'integer');
1964: * ```
1965: *
1966: * @param string|int $param placeholder to be replaced with quoted version
1967: * of $value
1968: * @param mixed $value The value to be bound
1969: * @param string|int $type the mapped type name, used for casting when sending
1970: * to database
1971: * @return $this
1972: */
1973: public function bind($param, $value, $type = 'string')
1974: {
1975: $this->getValueBinder()->bind($param, $value, $type);
1976:
1977: return $this;
1978: }
1979:
1980: /**
1981: * Returns the currently used ValueBinder instance.
1982: *
1983: * A ValueBinder is responsible for generating query placeholders and temporarily
1984: * associate values to those placeholders so that they can be passed correctly
1985: * to the statement object.
1986: *
1987: * @return \Cake\Database\ValueBinder
1988: */
1989: public function getValueBinder()
1990: {
1991: if ($this->_valueBinder === null) {
1992: $this->_valueBinder = new ValueBinder();
1993: }
1994:
1995: return $this->_valueBinder;
1996: }
1997:
1998: /**
1999: * Overwrite the current value binder
2000: *
2001: * A ValueBinder is responsible for generating query placeholders and temporarily
2002: * associate values to those placeholders so that they can be passed correctly
2003: * to the statement object.
2004: *
2005: * @param \Cake\Database\ValueBinder|bool $binder The binder or false to disable binding.
2006: * @return $this
2007: */
2008: public function setValueBinder($binder)
2009: {
2010: $this->_valueBinder = $binder;
2011:
2012: return $this;
2013: }
2014:
2015: /**
2016: * Returns the currently used ValueBinder instance. If a value is passed,
2017: * it will be set as the new instance to be used.
2018: *
2019: * A ValueBinder is responsible for generating query placeholders and temporarily
2020: * associate values to those placeholders so that they can be passed correctly
2021: * to the statement object.
2022: *
2023: * @deprecated 3.5.0 Use setValueBinder()/getValueBinder() instead.
2024: * @param \Cake\Database\ValueBinder|false|null $binder new instance to be set. If no value is passed the
2025: * default one will be returned
2026: * @return $this|\Cake\Database\ValueBinder
2027: */
2028: public function valueBinder($binder = null)
2029: {
2030: deprecationWarning('Query::valueBinder() is deprecated. Use Query::getValueBinder()/setValueBinder() instead.');
2031: if ($binder === null) {
2032: if ($this->_valueBinder === null) {
2033: $this->_valueBinder = new ValueBinder();
2034: }
2035:
2036: return $this->_valueBinder;
2037: }
2038: $this->_valueBinder = $binder;
2039:
2040: return $this;
2041: }
2042:
2043: /**
2044: * Enables/Disables buffered results.
2045: *
2046: * When enabled the results returned by this Query will be
2047: * buffered. This enables you to iterate a result set multiple times, or
2048: * both cache and iterate it.
2049: *
2050: * When disabled it will consume less memory as fetched results are not
2051: * remembered for future iterations.
2052: *
2053: * @param bool $enable Whether or not to enable buffering
2054: * @return $this
2055: */
2056: public function enableBufferedResults($enable = true)
2057: {
2058: $this->_dirty();
2059: $this->_useBufferedResults = (bool)$enable;
2060:
2061: return $this;
2062: }
2063:
2064: /**
2065: * Disables buffered results.
2066: *
2067: * Disabling buffering will consume less memory as fetched results are not
2068: * remembered for future iterations.
2069: *
2070: * @return $this
2071: */
2072: public function disableBufferedResults()
2073: {
2074: $this->_dirty();
2075: $this->_useBufferedResults = false;
2076:
2077: return $this;
2078: }
2079:
2080: /**
2081: * Returns whether buffered results are enabled/disabled.
2082: *
2083: * When enabled the results returned by this Query will be
2084: * buffered. This enables you to iterate a result set multiple times, or
2085: * both cache and iterate it.
2086: *
2087: * When disabled it will consume less memory as fetched results are not
2088: * remembered for future iterations.
2089: *
2090: * @return bool
2091: */
2092: public function isBufferedResultsEnabled()
2093: {
2094: return $this->_useBufferedResults;
2095: }
2096:
2097: /**
2098: * Enable/Disable buffered results.
2099: *
2100: * When enabled the results returned by this Query will be
2101: * buffered. This enables you to iterate a result set multiple times, or
2102: * both cache and iterate it.
2103: *
2104: * When disabled it will consume less memory as fetched results are not
2105: * remembered for future iterations.
2106: *
2107: * If called with no arguments, it will return whether or not buffering is
2108: * enabled.
2109: *
2110: * @deprecated 3.4.0 Use enableBufferedResults()/isBufferedResultsEnabled() instead.
2111: * @param bool|null $enable Whether or not to enable buffering
2112: * @return bool|$this
2113: */
2114: public function bufferResults($enable = null)
2115: {
2116: deprecationWarning(
2117: 'Query::bufferResults() is deprecated. ' .
2118: 'Use Query::enableBufferedResults()/isBufferedResultsEnabled() instead.'
2119: );
2120: if ($enable !== null) {
2121: return $this->enableBufferedResults($enable);
2122: }
2123:
2124: return $this->isBufferedResultsEnabled();
2125: }
2126:
2127: /**
2128: * Sets the TypeMap class where the types for each of the fields in the
2129: * select clause are stored.
2130: *
2131: * @param \Cake\Database\TypeMap $typeMap The map object to use
2132: * @return $this
2133: */
2134: public function setSelectTypeMap(TypeMap $typeMap)
2135: {
2136: $this->_selectTypeMap = $typeMap;
2137: $this->_dirty();
2138:
2139: return $this;
2140: }
2141:
2142: /**
2143: * Gets the TypeMap class where the types for each of the fields in the
2144: * select clause are stored.
2145: *
2146: * @return \Cake\Database\TypeMap
2147: */
2148: public function getSelectTypeMap()
2149: {
2150: if ($this->_selectTypeMap === null) {
2151: $this->_selectTypeMap = new TypeMap();
2152: }
2153:
2154: return $this->_selectTypeMap;
2155: }
2156:
2157: /**
2158: * Disables the automatic casting of fields to their corresponding PHP data type
2159: *
2160: * @return $this
2161: */
2162: public function disableResultsCasting()
2163: {
2164: $this->typeCastEnabled = false;
2165:
2166: return $this;
2167: }
2168:
2169: /**
2170: * Enables the automatic casting of fields to their corresponding type
2171: *
2172: * @return $this
2173: */
2174: public function enableResultsCasting()
2175: {
2176: $this->typeCastEnabled = true;
2177:
2178: return $this;
2179: }
2180:
2181: /**
2182: * Sets the TypeMap class where the types for each of the fields in the
2183: * select clause are stored.
2184: *
2185: * When called with no arguments, the current TypeMap object is returned.
2186: *
2187: * @deprecated 3.4.0 Use setSelectTypeMap()/getSelectTypeMap() instead.
2188: * @param \Cake\Database\TypeMap|null $typeMap The map object to use
2189: * @return $this|\Cake\Database\TypeMap
2190: */
2191: public function selectTypeMap(TypeMap $typeMap = null)
2192: {
2193: deprecationWarning(
2194: 'Query::selectTypeMap() is deprecated. ' .
2195: 'Use Query::setSelectTypeMap()/getSelectTypeMap() instead.'
2196: );
2197: if ($typeMap !== null) {
2198: return $this->setSelectTypeMap($typeMap);
2199: }
2200:
2201: return $this->getSelectTypeMap();
2202: }
2203:
2204: /**
2205: * Auxiliary function used to wrap the original statement from the driver with
2206: * any registered callbacks.
2207: *
2208: * @param \Cake\Database\StatementInterface $statement to be decorated
2209: * @return \Cake\Database\Statement\CallbackStatement
2210: */
2211: protected function _decorateStatement($statement)
2212: {
2213: $typeMap = $this->getSelectTypeMap();
2214: $driver = $this->getConnection()->getDriver();
2215:
2216: if ($this->typeCastEnabled && $typeMap->toArray()) {
2217: $statement = new CallbackStatement($statement, $driver, new FieldTypeConverter($typeMap, $driver));
2218: }
2219:
2220: foreach ($this->_resultDecorators as $f) {
2221: $statement = new CallbackStatement($statement, $driver, $f);
2222: }
2223:
2224: return $statement;
2225: }
2226:
2227: /**
2228: * Helper function used to build conditions by composing QueryExpression objects.
2229: *
2230: * @param string $part Name of the query part to append the new part to
2231: * @param string|array|\Cake\Database\ExpressionInterface|callable|null $append Expression or builder function to append.
2232: * @param string $conjunction type of conjunction to be used to operate part
2233: * @param array $types associative array of type names used to bind values to query
2234: * @return void
2235: */
2236: protected function _conjugate($part, $append, $conjunction, $types)
2237: {
2238: $expression = $this->_parts[$part] ?: $this->newExpr();
2239: if (empty($append)) {
2240: $this->_parts[$part] = $expression;
2241:
2242: return;
2243: }
2244:
2245: if ($expression->isCallable($append)) {
2246: $append = $append($this->newExpr(), $this);
2247: }
2248:
2249: if ($expression->getConjunction() === $conjunction) {
2250: $expression->add($append, $types);
2251: } else {
2252: $expression = $this->newExpr()
2253: ->setConjunction($conjunction)
2254: ->add([$expression, $append], $types);
2255: }
2256:
2257: $this->_parts[$part] = $expression;
2258: $this->_dirty();
2259: }
2260:
2261: /**
2262: * Marks a query as dirty, removing any preprocessed information
2263: * from in memory caching.
2264: *
2265: * @return void
2266: */
2267: protected function _dirty()
2268: {
2269: $this->_dirty = true;
2270:
2271: if ($this->_iterator && $this->_valueBinder) {
2272: $this->getValueBinder()->reset();
2273: }
2274: }
2275:
2276: /**
2277: * Do a deep clone on this object.
2278: *
2279: * Will clone all of the expression objects used in
2280: * each of the clauses, as well as the valueBinder.
2281: *
2282: * @return void
2283: */
2284: public function __clone()
2285: {
2286: $this->_iterator = null;
2287: if ($this->_valueBinder !== null) {
2288: $this->_valueBinder = clone $this->_valueBinder;
2289: }
2290: if ($this->_selectTypeMap !== null) {
2291: $this->_selectTypeMap = clone $this->_selectTypeMap;
2292: }
2293: foreach ($this->_parts as $name => $part) {
2294: if (empty($part)) {
2295: continue;
2296: }
2297: if (is_array($part)) {
2298: foreach ($part as $i => $piece) {
2299: if ($piece instanceof ExpressionInterface) {
2300: $this->_parts[$name][$i] = clone $piece;
2301: }
2302: }
2303: }
2304: if ($part instanceof ExpressionInterface) {
2305: $this->_parts[$name] = clone $part;
2306: }
2307: }
2308: }
2309:
2310: /**
2311: * Returns string representation of this query (complete SQL statement).
2312: *
2313: * @return string
2314: */
2315: public function __toString()
2316: {
2317: return $this->sql();
2318: }
2319:
2320: /**
2321: * Returns an array that can be used to describe the internal state of this
2322: * object.
2323: *
2324: * @return array
2325: */
2326: public function __debugInfo()
2327: {
2328: try {
2329: set_error_handler(function ($errno, $errstr) {
2330: throw new RuntimeException($errstr, $errno);
2331: }, E_ALL);
2332: $sql = $this->sql();
2333: $params = $this->getValueBinder()->bindings();
2334: } catch (RuntimeException $e) {
2335: $sql = 'SQL could not be generated for this query as it is incomplete.';
2336: $params = [];
2337: } finally {
2338: restore_error_handler();
2339: }
2340:
2341: return [
2342: '(help)' => 'This is a Query object, to get the results execute or iterate it.',
2343: 'sql' => $sql,
2344: 'params' => $params,
2345: 'defaultTypes' => $this->getDefaultTypes(),
2346: 'decorators' => count($this->_resultDecorators),
2347: 'executed' => $this->_iterator ? true : false
2348: ];
2349: }
2350: }
2351: