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