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\ORM;
16:
17: use ArrayObject;
18: use Cake\Database\ExpressionInterface;
19: use Cake\Database\Query as DatabaseQuery;
20: use Cake\Database\TypedResultInterface;
21: use Cake\Database\TypeMap;
22: use Cake\Database\ValueBinder;
23: use Cake\Datasource\QueryInterface;
24: use Cake\Datasource\QueryTrait;
25: use JsonSerializable;
26: use RuntimeException;
27:
28: /**
29: * Extends the base Query class to provide new methods related to association
30: * loading, automatic fields selection, automatic type casting and to wrap results
31: * into a specific iterator that will be responsible for hydrating results if
32: * required.
33: *
34: * @see \Cake\Collection\CollectionInterface For a full description of the collection methods supported by this class
35: * @method \Cake\Collection\CollectionInterface each(callable $c) Passes each of the query results to the callable
36: * @method \Cake\Collection\CollectionInterface sortBy($callback, $dir = SORT_DESC, $type = \SORT_NUMERIC) Sorts the query with the callback
37: * @method \Cake\Collection\CollectionInterface filter(callable $c = null) Keeps the results using passing the callable test
38: * @method \Cake\Collection\CollectionInterface reject(callable $c) Removes the results passing the callable test
39: * @method bool every(callable $c) Returns true if all the results pass the callable test
40: * @method bool some(callable $c) Returns true if at least one of the results pass the callable test
41: * @method \Cake\Collection\CollectionInterface map(callable $c) Modifies each of the results using the callable
42: * @method mixed reduce(callable $c, $zero = null) Folds all the results into a single value using the callable.
43: * @method \Cake\Collection\CollectionInterface extract($field) Extracts a single column from each row
44: * @method mixed max($field, $type = SORT_NUMERIC) Returns the maximum value for a single column in all the results.
45: * @method mixed min($field, $type = SORT_NUMERIC) Returns the minimum value for a single column in all the results.
46: * @method \Cake\Collection\CollectionInterface groupBy(string|callable $field) In-memory group all results by the value of a column.
47: * @method \Cake\Collection\CollectionInterface indexBy(string|callable $field) Returns the results indexed by the value of a column.
48: * @method \Cake\Collection\CollectionInterface countBy(string|callable $field) Returns the number of unique values for a column
49: * @method float sumOf(string|callable $field) Returns the sum of all values for a single column
50: * @method \Cake\Collection\CollectionInterface shuffle() In-memory randomize the order the results are returned
51: * @method \Cake\Collection\CollectionInterface sample($size = 10) In-memory shuffle the results and return a subset of them.
52: * @method \Cake\Collection\CollectionInterface take($size = 1, $from = 0) In-memory limit and offset for the query results.
53: * @method \Cake\Collection\CollectionInterface skip(int $howMany) Skips some rows from the start of the query result.
54: * @method mixed last() Return the last row of the query result
55: * @method \Cake\Collection\CollectionInterface append(array|\Traversable $items) Appends more rows to the result of the query.
56: * @method \Cake\Collection\CollectionInterface combine($k, $v, $g = null) Returns the values of the column $v index by column $k,
57: * and grouped by $g.
58: * @method \Cake\Collection\CollectionInterface nest($k, $p, $n = 'children') Creates a tree structure by nesting the values of column $p into that
59: * with the same value for $k using $n as the nesting key.
60: * @method array toArray() Returns a key-value array with the results of this query.
61: * @method array toList() Returns a numerically indexed array with the results of this query.
62: * @method \Cake\Collection\CollectionInterface stopWhen(callable $c) Returns each row until the callable returns true.
63: * @method \Cake\Collection\CollectionInterface zip(array|\Traversable $c) Returns the first result of both the query and $c in an array,
64: * then the second results and so on.
65: * @method \Cake\Collection\CollectionInterface zipWith($collections, callable $callable) Returns each of the results out of calling $c
66: * with the first rows of the query and each of the items, then the second rows and so on.
67: * @method \Cake\Collection\CollectionInterface chunk($size) Groups the results in arrays of $size rows each.
68: * @method bool isEmpty() Returns true if this query found no results.
69: */
70: class Query extends DatabaseQuery implements JsonSerializable, QueryInterface
71: {
72: use QueryTrait {
73: cache as private _cache;
74: all as private _all;
75: _decorateResults as private _applyDecorators;
76: __call as private _call;
77: }
78:
79: /**
80: * Indicates that the operation should append to the list
81: *
82: * @var int
83: */
84: const APPEND = 0;
85:
86: /**
87: * Indicates that the operation should prepend to the list
88: *
89: * @var int
90: */
91: const PREPEND = 1;
92:
93: /**
94: * Indicates that the operation should overwrite the list
95: *
96: * @var bool
97: */
98: const OVERWRITE = true;
99:
100: /**
101: * Whether the user select any fields before being executed, this is used
102: * to determined if any fields should be automatically be selected.
103: *
104: * @var bool|null
105: */
106: protected $_hasFields;
107:
108: /**
109: * Tracks whether or not the original query should include
110: * fields from the top level table.
111: *
112: * @var bool|null
113: */
114: protected $_autoFields;
115:
116: /**
117: * Whether to hydrate results into entity objects
118: *
119: * @var bool
120: */
121: protected $_hydrate = true;
122:
123: /**
124: * A callable function that can be used to calculate the total amount of
125: * records this query will match when not using `limit`
126: *
127: * @var callable|null
128: */
129: protected $_counter;
130:
131: /**
132: * Instance of a class responsible for storing association containments and
133: * for eager loading them when this query is executed
134: *
135: * @var \Cake\ORM\EagerLoader|null
136: */
137: protected $_eagerLoader;
138:
139: /**
140: * True if the beforeFind event has already been triggered for this query
141: *
142: * @var bool
143: */
144: protected $_beforeFindFired = false;
145:
146: /**
147: * The COUNT(*) for the query.
148: *
149: * When set, count query execution will be bypassed.
150: *
151: * @var int|null
152: */
153: protected $_resultsCount;
154:
155: /**
156: * Constructor
157: *
158: * @param \Cake\Database\Connection $connection The connection object
159: * @param \Cake\ORM\Table $table The table this query is starting on
160: */
161: public function __construct($connection, $table)
162: {
163: parent::__construct($connection);
164: $this->repository($table);
165:
166: if ($this->_repository) {
167: $this->addDefaultTypes($this->_repository);
168: }
169: }
170:
171: /**
172: * Adds new fields to be returned by a `SELECT` statement when this query is
173: * executed. Fields can be passed as an array of strings, array of expression
174: * objects, a single expression or a single string.
175: *
176: * If an array is passed, keys will be used to alias fields using the value as the
177: * real field to be aliased. It is possible to alias strings, Expression objects or
178: * even other Query objects.
179: *
180: * If a callable function is passed, the returning array of the function will
181: * be used as the list of fields.
182: *
183: * By default this function will append any passed argument to the list of fields
184: * to be selected, unless the second argument is set to true.
185: *
186: * ### Examples:
187: *
188: * ```
189: * $query->select(['id', 'title']); // Produces SELECT id, title
190: * $query->select(['author' => 'author_id']); // Appends author: SELECT id, title, author_id as author
191: * $query->select('id', true); // Resets the list: SELECT id
192: * $query->select(['total' => $countQuery]); // SELECT id, (SELECT ...) AS total
193: * $query->select(function ($query) {
194: * return ['article_id', 'total' => $query->count('*')];
195: * })
196: * ```
197: *
198: * By default no fields are selected, if you have an instance of `Cake\ORM\Query` and try to append
199: * fields you should also call `Cake\ORM\Query::enableAutoFields()` to select the default fields
200: * from the table.
201: *
202: * If you pass an instance of a `Cake\ORM\Table` or `Cake\ORM\Association` class,
203: * all the fields in the schema of the table or the association will be added to
204: * the select clause.
205: *
206: * @param array|\Cake\Database\ExpressionInterface|callable|string|\Cake\ORM\Table|\Cake\ORM\Association $fields fields
207: * to be added to the list.
208: * @param bool $overwrite whether to reset fields with passed list or not
209: * @return $this
210: */
211: public function select($fields = [], $overwrite = false)
212: {
213: if ($fields instanceof Association) {
214: $fields = $fields->getTarget();
215: }
216:
217: if ($fields instanceof Table) {
218: $fields = $this->aliasFields($fields->getSchema()->columns(), $fields->getAlias());
219: }
220:
221: return parent::select($fields, $overwrite);
222: }
223:
224: /**
225: * All the fields associated with the passed table except the excluded
226: * fields will be added to the select clause of the query. Passed excluded fields should not be aliased.
227: * After the first call to this method, a second call cannot be used to remove fields that have already
228: * been added to the query by the first. If you need to change the list after the first call,
229: * pass overwrite boolean true which will reset the select clause removing all previous additions.
230: *
231: *
232: *
233: * @param \Cake\ORM\Table|\Cake\ORM\Association $table The table to use to get an array of columns
234: * @param string[] $excludedFields The un-aliased column names you do not want selected from $table
235: * @param bool $overwrite Whether to reset/remove previous selected fields
236: * @return Query
237: * @throws \InvalidArgumentException If Association|Table is not passed in first argument
238: */
239: public function selectAllExcept($table, array $excludedFields, $overwrite = false)
240: {
241: if ($table instanceof Association) {
242: $table = $table->getTarget();
243: }
244:
245: if (!($table instanceof Table)) {
246: throw new \InvalidArgumentException('You must provide either an Association or a Table object');
247: }
248:
249: $fields = array_diff($table->getSchema()->columns(), $excludedFields);
250: $aliasedFields = $this->aliasFields($fields);
251:
252: return $this->select($aliasedFields, $overwrite);
253: }
254:
255: /**
256: * Hints this object to associate the correct types when casting conditions
257: * for the database. This is done by extracting the field types from the schema
258: * associated to the passed table object. This prevents the user from repeating
259: * themselves when specifying conditions.
260: *
261: * This method returns the same query object for chaining.
262: *
263: * @param \Cake\ORM\Table $table The table to pull types from
264: * @return $this
265: */
266: public function addDefaultTypes(Table $table)
267: {
268: $alias = $table->getAlias();
269: $map = $table->getSchema()->typeMap();
270: $fields = [];
271: foreach ($map as $f => $type) {
272: $fields[$f] = $fields[$alias . '.' . $f] = $fields[$alias . '__' . $f] = $type;
273: }
274: $this->getTypeMap()->addDefaults($fields);
275:
276: return $this;
277: }
278:
279: /**
280: * Sets the instance of the eager loader class to use for loading associations
281: * and storing containments.
282: *
283: * @param \Cake\ORM\EagerLoader $instance The eager loader to use.
284: * @return $this
285: */
286: public function setEagerLoader(EagerLoader $instance)
287: {
288: $this->_eagerLoader = $instance;
289:
290: return $this;
291: }
292:
293: /**
294: * Returns the currently configured instance.
295: *
296: * @return \Cake\ORM\EagerLoader
297: */
298: public function getEagerLoader()
299: {
300: if ($this->_eagerLoader === null) {
301: $this->_eagerLoader = new EagerLoader();
302: }
303:
304: return $this->_eagerLoader;
305: }
306:
307: /**
308: * Sets the instance of the eager loader class to use for loading associations
309: * and storing containments. If called with no arguments, it will return the
310: * currently configured instance.
311: *
312: * @deprecated 3.4.0 Use setEagerLoader()/getEagerLoader() instead.
313: * @param \Cake\ORM\EagerLoader|null $instance The eager loader to use. Pass null
314: * to get the current eagerloader.
315: * @return \Cake\ORM\EagerLoader|$this
316: */
317: public function eagerLoader(EagerLoader $instance = null)
318: {
319: deprecationWarning(
320: 'Query::eagerLoader() is deprecated. ' .
321: 'Use setEagerLoader()/getEagerLoader() instead.'
322: );
323: if ($instance !== null) {
324: return $this->setEagerLoader($instance);
325: }
326:
327: return $this->getEagerLoader();
328: }
329:
330: /**
331: * Sets the list of associations that should be eagerly loaded along with this
332: * query. The list of associated tables passed must have been previously set as
333: * associations using the Table API.
334: *
335: * ### Example:
336: *
337: * ```
338: * // Bring articles' author information
339: * $query->contain('Author');
340: *
341: * // Also bring the category and tags associated to each article
342: * $query->contain(['Category', 'Tag']);
343: * ```
344: *
345: * Associations can be arbitrarily nested using dot notation or nested arrays,
346: * this allows this object to calculate joins or any additional queries that
347: * must be executed to bring the required associated data.
348: *
349: * ### Example:
350: *
351: * ```
352: * // Eager load the product info, and for each product load other 2 associations
353: * $query->contain(['Product' => ['Manufacturer', 'Distributor']);
354: *
355: * // Which is equivalent to calling
356: * $query->contain(['Products.Manufactures', 'Products.Distributors']);
357: *
358: * // For an author query, load his region, state and country
359: * $query->contain('Regions.States.Countries');
360: * ```
361: *
362: * It is possible to control the conditions and fields selected for each of the
363: * contained associations:
364: *
365: * ### Example:
366: *
367: * ```
368: * $query->contain(['Tags' => function ($q) {
369: * return $q->where(['Tags.is_popular' => true]);
370: * }]);
371: *
372: * $query->contain(['Products.Manufactures' => function ($q) {
373: * return $q->select(['name'])->where(['Manufactures.active' => true]);
374: * }]);
375: * ```
376: *
377: * Each association might define special options when eager loaded, the allowed
378: * options that can be set per association are:
379: *
380: * - `foreignKey`: Used to set a different field to match both tables, if set to false
381: * no join conditions will be generated automatically. `false` can only be used on
382: * joinable associations and cannot be used with hasMany or belongsToMany associations.
383: * - `fields`: An array with the fields that should be fetched from the association.
384: * - `finder`: The finder to use when loading associated records. Either the name of the
385: * finder as a string, or an array to define options to pass to the finder.
386: * - `queryBuilder`: Equivalent to passing a callable instead of an options array.
387: *
388: * ### Example:
389: *
390: * ```
391: * // Set options for the hasMany articles that will be eagerly loaded for an author
392: * $query->contain([
393: * 'Articles' => [
394: * 'fields' => ['title', 'author_id']
395: * ]
396: * ]);
397: * ```
398: *
399: * Finders can be configured to use options.
400: *
401: * ```
402: * // Retrieve translations for the articles, but only those for the `en` and `es` locales
403: * $query->contain([
404: * 'Articles' => [
405: * 'finder' => [
406: * 'translations' => [
407: * 'locales' => ['en', 'es']
408: * ]
409: * ]
410: * ]
411: * ]);
412: * ```
413: *
414: * When containing associations, it is important to include foreign key columns.
415: * Failing to do so will trigger exceptions.
416: *
417: * ```
418: * // Use a query builder to add conditions to the containment
419: * $query->contain('Authors', function ($q) {
420: * return $q->where(...); // add conditions
421: * });
422: * // Use special join conditions for multiple containments in the same method call
423: * $query->contain([
424: * 'Authors' => [
425: * 'foreignKey' => false,
426: * 'queryBuilder' => function ($q) {
427: * return $q->where(...); // Add full filtering conditions
428: * }
429: * ],
430: * 'Tags' => function ($q) {
431: * return $q->where(...); // add conditions
432: * }
433: * ]);
434: * ```
435: *
436: * If called with no arguments, this function will return an array with
437: * with the list of previously configured associations to be contained in the
438: * result. This getter part is deprecated as of 3.6.0. Use getContain() instead.
439: *
440: * If called with an empty first argument and `$override` is set to true, the
441: * previous list will be emptied.
442: *
443: * @param array|string|null $associations List of table aliases to be queried.
444: * @param callable|bool $override The query builder for the association, or
445: * if associations is an array, a bool on whether to override previous list
446: * with the one passed
447: * defaults to merging previous list with the new one.
448: * @return array|$this
449: */
450: public function contain($associations = null, $override = false)
451: {
452: $loader = $this->getEagerLoader();
453: if ($override === true) {
454: $this->clearContain();
455: }
456:
457: if ($associations === null) {
458: deprecationWarning(
459: 'Using Query::contain() as getter is deprecated. ' .
460: 'Use getContain() instead.'
461: );
462:
463: return $loader->getContain();
464: }
465:
466: $queryBuilder = null;
467: if (is_callable($override)) {
468: $queryBuilder = $override;
469: }
470:
471: if ($associations) {
472: $loader->contain($associations, $queryBuilder);
473: }
474: $this->_addAssociationsToTypeMap(
475: $this->getRepository(),
476: $this->getTypeMap(),
477: $loader->getContain()
478: );
479:
480: return $this;
481: }
482:
483: /**
484: * @return array
485: */
486: public function getContain()
487: {
488: return $this->getEagerLoader()->getContain();
489: }
490:
491: /**
492: * Clears the contained associations from the current query.
493: *
494: * @return $this
495: */
496: public function clearContain()
497: {
498: $this->getEagerLoader()->clearContain();
499: $this->_dirty();
500:
501: return $this;
502: }
503:
504: /**
505: * Used to recursively add contained association column types to
506: * the query.
507: *
508: * @param \Cake\ORM\Table $table The table instance to pluck associations from.
509: * @param \Cake\Database\TypeMap $typeMap The typemap to check for columns in.
510: * This typemap is indirectly mutated via Cake\ORM\Query::addDefaultTypes()
511: * @param array $associations The nested tree of associations to walk.
512: * @return void
513: */
514: protected function _addAssociationsToTypeMap($table, $typeMap, $associations)
515: {
516: foreach ($associations as $name => $nested) {
517: if (!$table->hasAssociation($name)) {
518: continue;
519: }
520: $association = $table->getAssociation($name);
521: $target = $association->getTarget();
522: $primary = (array)$target->getPrimaryKey();
523: if (empty($primary) || $typeMap->type($target->aliasField($primary[0])) === null) {
524: $this->addDefaultTypes($target);
525: }
526: if (!empty($nested)) {
527: $this->_addAssociationsToTypeMap($target, $typeMap, $nested);
528: }
529: }
530: }
531:
532: /**
533: * Adds filtering conditions to this query to only bring rows that have a relation
534: * to another from an associated table, based on conditions in the associated table.
535: *
536: * This function will add entries in the `contain` graph.
537: *
538: * ### Example:
539: *
540: * ```
541: * // Bring only articles that were tagged with 'cake'
542: * $query->matching('Tags', function ($q) {
543: * return $q->where(['name' => 'cake']);
544: * );
545: * ```
546: *
547: * It is possible to filter by deep associations by using dot notation:
548: *
549: * ### Example:
550: *
551: * ```
552: * // Bring only articles that were commented by 'markstory'
553: * $query->matching('Comments.Users', function ($q) {
554: * return $q->where(['username' => 'markstory']);
555: * );
556: * ```
557: *
558: * As this function will create `INNER JOIN`, you might want to consider
559: * calling `distinct` on this query as you might get duplicate rows if
560: * your conditions don't filter them already. This might be the case, for example,
561: * of the same user commenting more than once in the same article.
562: *
563: * ### Example:
564: *
565: * ```
566: * // Bring unique articles that were commented by 'markstory'
567: * $query->distinct(['Articles.id'])
568: * ->matching('Comments.Users', function ($q) {
569: * return $q->where(['username' => 'markstory']);
570: * );
571: * ```
572: *
573: * Please note that the query passed to the closure will only accept calling
574: * `select`, `where`, `andWhere` and `orWhere` on it. If you wish to
575: * add more complex clauses you can do it directly in the main query.
576: *
577: * @param string $assoc The association to filter by
578: * @param callable|null $builder a function that will receive a pre-made query object
579: * that can be used to add custom conditions or selecting some fields
580: * @return $this
581: */
582: public function matching($assoc, callable $builder = null)
583: {
584: $result = $this->getEagerLoader()->setMatching($assoc, $builder)->getMatching();
585: $this->_addAssociationsToTypeMap($this->getRepository(), $this->getTypeMap(), $result);
586: $this->_dirty();
587:
588: return $this;
589: }
590:
591: /**
592: * Creates a LEFT JOIN with the passed association table while preserving
593: * the foreign key matching and the custom conditions that were originally set
594: * for it.
595: *
596: * This function will add entries in the `contain` graph.
597: *
598: * ### Example:
599: *
600: * ```
601: * // Get the count of articles per user
602: * $usersQuery
603: * ->select(['total_articles' => $query->func()->count('Articles.id')])
604: * ->leftJoinWith('Articles')
605: * ->group(['Users.id'])
606: * ->enableAutoFields(true);
607: * ```
608: *
609: * You can also customize the conditions passed to the LEFT JOIN:
610: *
611: * ```
612: * // Get the count of articles per user with at least 5 votes
613: * $usersQuery
614: * ->select(['total_articles' => $query->func()->count('Articles.id')])
615: * ->leftJoinWith('Articles', function ($q) {
616: * return $q->where(['Articles.votes >=' => 5]);
617: * })
618: * ->group(['Users.id'])
619: * ->enableAutoFields(true);
620: * ```
621: *
622: * This will create the following SQL:
623: *
624: * ```
625: * SELECT COUNT(Articles.id) AS total_articles, Users.*
626: * FROM users Users
627: * LEFT JOIN articles Articles ON Articles.user_id = Users.id AND Articles.votes >= 5
628: * GROUP BY USers.id
629: * ```
630: *
631: * It is possible to left join deep associations by using dot notation
632: *
633: * ### Example:
634: *
635: * ```
636: * // Total comments in articles by 'markstory'
637: * $query
638: * ->select(['total_comments' => $query->func()->count('Comments.id')])
639: * ->leftJoinWith('Comments.Users', function ($q) {
640: * return $q->where(['username' => 'markstory']);
641: * )
642: * ->group(['Users.id']);
643: * ```
644: *
645: * Please note that the query passed to the closure will only accept calling
646: * `select`, `where`, `andWhere` and `orWhere` on it. If you wish to
647: * add more complex clauses you can do it directly in the main query.
648: *
649: * @param string $assoc The association to join with
650: * @param callable|null $builder a function that will receive a pre-made query object
651: * that can be used to add custom conditions or selecting some fields
652: * @return $this
653: */
654: public function leftJoinWith($assoc, callable $builder = null)
655: {
656: $result = $this->getEagerLoader()
657: ->setMatching($assoc, $builder, [
658: 'joinType' => QueryInterface::JOIN_TYPE_LEFT,
659: 'fields' => false
660: ])
661: ->getMatching();
662: $this->_addAssociationsToTypeMap($this->getRepository(), $this->getTypeMap(), $result);
663: $this->_dirty();
664:
665: return $this;
666: }
667:
668: /**
669: * Creates an INNER JOIN with the passed association table while preserving
670: * the foreign key matching and the custom conditions that were originally set
671: * for it.
672: *
673: * This function will add entries in the `contain` graph.
674: *
675: * ### Example:
676: *
677: * ```
678: * // Bring only articles that were tagged with 'cake'
679: * $query->innerJoinWith('Tags', function ($q) {
680: * return $q->where(['name' => 'cake']);
681: * );
682: * ```
683: *
684: * This will create the following SQL:
685: *
686: * ```
687: * SELECT Articles.*
688: * FROM articles Articles
689: * INNER JOIN tags Tags ON Tags.name = 'cake'
690: * INNER JOIN articles_tags ArticlesTags ON ArticlesTags.tag_id = Tags.id
691: * AND ArticlesTags.articles_id = Articles.id
692: * ```
693: *
694: * This function works the same as `matching()` with the difference that it
695: * will select no fields from the association.
696: *
697: * @param string $assoc The association to join with
698: * @param callable|null $builder a function that will receive a pre-made query object
699: * that can be used to add custom conditions or selecting some fields
700: * @return $this
701: * @see \Cake\ORM\Query::matching()
702: */
703: public function innerJoinWith($assoc, callable $builder = null)
704: {
705: $result = $this->getEagerLoader()
706: ->setMatching($assoc, $builder, [
707: 'joinType' => QueryInterface::JOIN_TYPE_INNER,
708: 'fields' => false
709: ])
710: ->getMatching();
711: $this->_addAssociationsToTypeMap($this->getRepository(), $this->getTypeMap(), $result);
712: $this->_dirty();
713:
714: return $this;
715: }
716:
717: /**
718: * Adds filtering conditions to this query to only bring rows that have no match
719: * to another from an associated table, based on conditions in the associated table.
720: *
721: * This function will add entries in the `contain` graph.
722: *
723: * ### Example:
724: *
725: * ```
726: * // Bring only articles that were not tagged with 'cake'
727: * $query->notMatching('Tags', function ($q) {
728: * return $q->where(['name' => 'cake']);
729: * );
730: * ```
731: *
732: * It is possible to filter by deep associations by using dot notation:
733: *
734: * ### Example:
735: *
736: * ```
737: * // Bring only articles that weren't commented by 'markstory'
738: * $query->notMatching('Comments.Users', function ($q) {
739: * return $q->where(['username' => 'markstory']);
740: * );
741: * ```
742: *
743: * As this function will create a `LEFT JOIN`, you might want to consider
744: * calling `distinct` on this query as you might get duplicate rows if
745: * your conditions don't filter them already. This might be the case, for example,
746: * of the same article having multiple comments.
747: *
748: * ### Example:
749: *
750: * ```
751: * // Bring unique articles that were commented by 'markstory'
752: * $query->distinct(['Articles.id'])
753: * ->notMatching('Comments.Users', function ($q) {
754: * return $q->where(['username' => 'markstory']);
755: * );
756: * ```
757: *
758: * Please note that the query passed to the closure will only accept calling
759: * `select`, `where`, `andWhere` and `orWhere` on it. If you wish to
760: * add more complex clauses you can do it directly in the main query.
761: *
762: * @param string $assoc The association to filter by
763: * @param callable|null $builder a function that will receive a pre-made query object
764: * that can be used to add custom conditions or selecting some fields
765: * @return $this
766: */
767: public function notMatching($assoc, callable $builder = null)
768: {
769: $result = $this->getEagerLoader()
770: ->setMatching($assoc, $builder, [
771: 'joinType' => QueryInterface::JOIN_TYPE_LEFT,
772: 'fields' => false,
773: 'negateMatch' => true
774: ])
775: ->getMatching();
776: $this->_addAssociationsToTypeMap($this->getRepository(), $this->getTypeMap(), $result);
777: $this->_dirty();
778:
779: return $this;
780: }
781:
782: /**
783: * {@inheritDoc}
784: *
785: * Populates or adds parts to current query clauses using an array.
786: * This is handy for passing all query clauses at once. The option array accepts:
787: *
788: * - fields: Maps to the select method
789: * - conditions: Maps to the where method
790: * - limit: Maps to the limit method
791: * - order: Maps to the order method
792: * - offset: Maps to the offset method
793: * - group: Maps to the group method
794: * - having: Maps to the having method
795: * - contain: Maps to the contain options for eager loading
796: * - join: Maps to the join method
797: * - page: Maps to the page method
798: *
799: * ### Example:
800: *
801: * ```
802: * $query->applyOptions([
803: * 'fields' => ['id', 'name'],
804: * 'conditions' => [
805: * 'created >=' => '2013-01-01'
806: * ],
807: * 'limit' => 10
808: * ]);
809: * ```
810: *
811: * Is equivalent to:
812: *
813: * ```
814: * $query
815: * ->select(['id', 'name'])
816: * ->where(['created >=' => '2013-01-01'])
817: * ->limit(10)
818: * ```
819: */
820: public function applyOptions(array $options)
821: {
822: $valid = [
823: 'fields' => 'select',
824: 'conditions' => 'where',
825: 'join' => 'join',
826: 'order' => 'order',
827: 'limit' => 'limit',
828: 'offset' => 'offset',
829: 'group' => 'group',
830: 'having' => 'having',
831: 'contain' => 'contain',
832: 'page' => 'page',
833: ];
834:
835: ksort($options);
836: foreach ($options as $option => $values) {
837: if (isset($valid[$option], $values)) {
838: $this->{$valid[$option]}($values);
839: } else {
840: $this->_options[$option] = $values;
841: }
842: }
843:
844: return $this;
845: }
846:
847: /**
848: * Creates a copy of this current query, triggers beforeFind and resets some state.
849: *
850: * The following state will be cleared:
851: *
852: * - autoFields
853: * - limit
854: * - offset
855: * - map/reduce functions
856: * - result formatters
857: * - order
858: * - containments
859: *
860: * This method creates query clones that are useful when working with subqueries.
861: *
862: * @return \Cake\ORM\Query
863: */
864: public function cleanCopy()
865: {
866: $clone = clone $this;
867: $clone->setEagerLoader(clone $this->getEagerLoader());
868: $clone->triggerBeforeFind();
869: $clone->enableAutoFields(false);
870: $clone->limit(null);
871: $clone->order([], true);
872: $clone->offset(null);
873: $clone->mapReduce(null, null, true);
874: $clone->formatResults(null, true);
875: $clone->setSelectTypeMap(new TypeMap());
876: $clone->decorateResults(null, true);
877:
878: return $clone;
879: }
880:
881: /**
882: * Object clone hook.
883: *
884: * Destroys the clones inner iterator and clones the value binder, and eagerloader instances.
885: *
886: * @return void
887: */
888: public function __clone()
889: {
890: parent::__clone();
891: if ($this->_eagerLoader) {
892: $this->_eagerLoader = clone $this->_eagerLoader;
893: }
894: }
895:
896: /**
897: * {@inheritDoc}
898: *
899: * Returns the COUNT(*) for the query. If the query has not been
900: * modified, and the count has already been performed the cached
901: * value is returned
902: */
903: public function count()
904: {
905: if ($this->_resultsCount === null) {
906: $this->_resultsCount = $this->_performCount();
907: }
908:
909: return $this->_resultsCount;
910: }
911:
912: /**
913: * Performs and returns the COUNT(*) for the query.
914: *
915: * @return int
916: */
917: protected function _performCount()
918: {
919: $query = $this->cleanCopy();
920: $counter = $this->_counter;
921: if ($counter) {
922: $query->counter(null);
923:
924: return (int)$counter($query);
925: }
926:
927: $complex = (
928: $query->clause('distinct') ||
929: count($query->clause('group')) ||
930: count($query->clause('union')) ||
931: $query->clause('having')
932: );
933:
934: if (!$complex) {
935: // Expression fields could have bound parameters.
936: foreach ($query->clause('select') as $field) {
937: if ($field instanceof ExpressionInterface) {
938: $complex = true;
939: break;
940: }
941: }
942: }
943:
944: if (!$complex && $this->_valueBinder !== null) {
945: $order = $this->clause('order');
946: $complex = $order === null ? false : $order->hasNestedExpression();
947: }
948:
949: $count = ['count' => $query->func()->count('*')];
950:
951: if (!$complex) {
952: $query->getEagerLoader()->enableAutoFields(false);
953: $statement = $query
954: ->select($count, true)
955: ->enableAutoFields(false)
956: ->execute();
957: } else {
958: $statement = $this->getConnection()->newQuery()
959: ->select($count)
960: ->from(['count_source' => $query])
961: ->execute();
962: }
963:
964: $result = $statement->fetch('assoc')['count'];
965: $statement->closeCursor();
966:
967: return (int)$result;
968: }
969:
970: /**
971: * Registers a callable function that will be executed when the `count` method in
972: * this query is called. The return value for the function will be set as the
973: * return value of the `count` method.
974: *
975: * This is particularly useful when you need to optimize a query for returning the
976: * count, for example removing unnecessary joins, removing group by or just return
977: * an estimated number of rows.
978: *
979: * The callback will receive as first argument a clone of this query and not this
980: * query itself.
981: *
982: * If the first param is a null value, the built-in counter function will be called
983: * instead
984: *
985: * @param callable|null $counter The counter value
986: * @return $this
987: */
988: public function counter($counter)
989: {
990: $this->_counter = $counter;
991:
992: return $this;
993: }
994:
995: /**
996: * Toggle hydrating entities.
997: *
998: * If set to false array results will be returned for the query.
999: *
1000: * @param bool $enable Use a boolean to set the hydration mode.
1001: * @return $this
1002: */
1003: public function enableHydration($enable = true)
1004: {
1005: $this->_dirty();
1006: $this->_hydrate = (bool)$enable;
1007:
1008: return $this;
1009: }
1010:
1011: /**
1012: * Disable hydrating entities.
1013: *
1014: * Disabling hydration will cause array results to be returned for the query
1015: * instead of entities.
1016: *
1017: * @return $this
1018: */
1019: public function disableHydration()
1020: {
1021: $this->_dirty();
1022: $this->_hydrate = false;
1023:
1024: return $this;
1025: }
1026:
1027: /**
1028: * Returns the current hydration mode.
1029: *
1030: * @return bool
1031: */
1032: public function isHydrationEnabled()
1033: {
1034: return $this->_hydrate;
1035: }
1036:
1037: /**
1038: * Toggle hydrating entities.
1039: *
1040: * If set to false array results will be returned.
1041: *
1042: * @deprecated 3.4.0 Use enableHydration()/isHydrationEnabled() instead.
1043: * @param bool|null $enable Use a boolean to set the hydration mode.
1044: * Null will fetch the current hydration mode.
1045: * @return bool|$this A boolean when reading, and $this when setting the mode.
1046: */
1047: public function hydrate($enable = null)
1048: {
1049: deprecationWarning(
1050: 'Query::hydrate() is deprecated. ' .
1051: 'Use enableHydration()/isHydrationEnabled() instead.'
1052: );
1053: if ($enable === null) {
1054: return $this->isHydrationEnabled();
1055: }
1056:
1057: return $this->enableHydration($enable);
1058: }
1059:
1060: /**
1061: * {@inheritDoc}
1062: *
1063: * @return $this
1064: * @throws \RuntimeException When you attempt to cache a non-select query.
1065: */
1066: public function cache($key, $config = 'default')
1067: {
1068: if ($this->_type !== 'select' && $this->_type !== null) {
1069: throw new RuntimeException('You cannot cache the results of non-select queries.');
1070: }
1071:
1072: return $this->_cache($key, $config);
1073: }
1074:
1075: /**
1076: * {@inheritDoc}
1077: *
1078: * @throws \RuntimeException if this method is called on a non-select Query.
1079: */
1080: public function all()
1081: {
1082: if ($this->_type !== 'select' && $this->_type !== null) {
1083: throw new RuntimeException(
1084: 'You cannot call all() on a non-select query. Use execute() instead.'
1085: );
1086: }
1087:
1088: return $this->_all();
1089: }
1090:
1091: /**
1092: * Trigger the beforeFind event on the query's repository object.
1093: *
1094: * Will not trigger more than once, and only for select queries.
1095: *
1096: * @return void
1097: */
1098: public function triggerBeforeFind()
1099: {
1100: if (!$this->_beforeFindFired && $this->_type === 'select') {
1101: $this->_beforeFindFired = true;
1102:
1103: /** @var \Cake\Event\EventDispatcherInterface $repository */
1104: $repository = $this->getRepository();
1105: $repository->dispatchEvent('Model.beforeFind', [
1106: $this,
1107: new ArrayObject($this->_options),
1108: !$this->isEagerLoaded()
1109: ]);
1110: }
1111: }
1112:
1113: /**
1114: * {@inheritDoc}
1115: */
1116: public function sql(ValueBinder $binder = null)
1117: {
1118: $this->triggerBeforeFind();
1119:
1120: $this->_transformQuery();
1121:
1122: return parent::sql($binder);
1123: }
1124:
1125: /**
1126: * Executes this query and returns a ResultSet object containing the results.
1127: * This will also setup the correct statement class in order to eager load deep
1128: * associations.
1129: *
1130: * @return \Cake\ORM\ResultSet
1131: */
1132: protected function _execute()
1133: {
1134: $this->triggerBeforeFind();
1135: if ($this->_results) {
1136: $decorator = $this->_decoratorClass();
1137:
1138: return new $decorator($this->_results);
1139: }
1140:
1141: $statement = $this->getEagerLoader()->loadExternal($this, $this->execute());
1142:
1143: return new ResultSet($this, $statement);
1144: }
1145:
1146: /**
1147: * Applies some defaults to the query object before it is executed.
1148: *
1149: * Specifically add the FROM clause, adds default table fields if none are
1150: * specified and applies the joins required to eager load associations defined
1151: * using `contain`
1152: *
1153: * It also sets the default types for the columns in the select clause
1154: *
1155: * @see \Cake\Database\Query::execute()
1156: * @return void
1157: */
1158: protected function _transformQuery()
1159: {
1160: if (!$this->_dirty || $this->_type !== 'select') {
1161: return;
1162: }
1163:
1164: /** @var \Cake\ORM\Table $repository */
1165: $repository = $this->getRepository();
1166:
1167: if (empty($this->_parts['from'])) {
1168: $this->from([$repository->getAlias() => $repository->getTable()]);
1169: }
1170: $this->_addDefaultFields();
1171: $this->getEagerLoader()->attachAssociations($this, $repository, !$this->_hasFields);
1172: $this->_addDefaultSelectTypes();
1173: }
1174:
1175: /**
1176: * Inspects if there are any set fields for selecting, otherwise adds all
1177: * the fields for the default table.
1178: *
1179: * @return void
1180: */
1181: protected function _addDefaultFields()
1182: {
1183: $select = $this->clause('select');
1184: $this->_hasFields = true;
1185:
1186: /** @var \Cake\ORM\Table $repository */
1187: $repository = $this->getRepository();
1188:
1189: if (!count($select) || $this->_autoFields === true) {
1190: $this->_hasFields = false;
1191: $this->select($repository->getSchema()->columns());
1192: $select = $this->clause('select');
1193: }
1194:
1195: $aliased = $this->aliasFields($select, $repository->getAlias());
1196: $this->select($aliased, true);
1197: }
1198:
1199: /**
1200: * Sets the default types for converting the fields in the select clause
1201: *
1202: * @return void
1203: */
1204: protected function _addDefaultSelectTypes()
1205: {
1206: $typeMap = $this->getTypeMap()->getDefaults();
1207: $select = $this->clause('select');
1208: $types = [];
1209:
1210: foreach ($select as $alias => $value) {
1211: if (isset($typeMap[$alias])) {
1212: $types[$alias] = $typeMap[$alias];
1213: continue;
1214: }
1215: if (is_string($value) && isset($typeMap[$value])) {
1216: $types[$alias] = $typeMap[$value];
1217: }
1218: if ($value instanceof TypedResultInterface) {
1219: $types[$alias] = $value->getReturnType();
1220: }
1221: }
1222: $this->getSelectTypeMap()->addDefaults($types);
1223: }
1224:
1225: /**
1226: * {@inheritDoc}
1227: *
1228: * @see \Cake\ORM\Table::find()
1229: */
1230: public function find($finder, array $options = [])
1231: {
1232: /** @var \Cake\ORM\Table $table */
1233: $table = $this->getRepository();
1234:
1235: return $table->callFinder($finder, $this, $options);
1236: }
1237:
1238: /**
1239: * Marks a query as dirty, removing any preprocessed information
1240: * from in memory caching such as previous results
1241: *
1242: * @return void
1243: */
1244: protected function _dirty()
1245: {
1246: $this->_results = null;
1247: $this->_resultsCount = null;
1248: parent::_dirty();
1249: }
1250:
1251: /**
1252: * Create an update query.
1253: *
1254: * This changes the query type to be 'update'.
1255: * Can be combined with set() and where() methods to create update queries.
1256: *
1257: * @param string|null $table Unused parameter.
1258: * @return $this
1259: */
1260: public function update($table = null)
1261: {
1262: if (!$table) {
1263: /** @var \Cake\ORM\Table $repository */
1264: $repository = $this->getRepository();
1265: $table = $repository->getTable();
1266: }
1267:
1268: return parent::update($table);
1269: }
1270:
1271: /**
1272: * Create a delete query.
1273: *
1274: * This changes the query type to be 'delete'.
1275: * Can be combined with the where() method to create delete queries.
1276: *
1277: * @param string|null $table Unused parameter.
1278: * @return $this
1279: */
1280: public function delete($table = null)
1281: {
1282: /** @var \Cake\ORM\Table $repository */
1283: $repository = $this->getRepository();
1284: $this->from([$repository->getAlias() => $repository->getTable()]);
1285:
1286: // We do not pass $table to parent class here
1287: return parent::delete();
1288: }
1289:
1290: /**
1291: * Create an insert query.
1292: *
1293: * This changes the query type to be 'insert'.
1294: * Note calling this method will reset any data previously set
1295: * with Query::values()
1296: *
1297: * Can be combined with the where() method to create delete queries.
1298: *
1299: * @param array $columns The columns to insert into.
1300: * @param array $types A map between columns & their datatypes.
1301: * @return $this
1302: */
1303: public function insert(array $columns, array $types = [])
1304: {
1305: /** @var \Cake\ORM\Table $repository */
1306: $repository = $this->getRepository();
1307: $table = $repository->getTable();
1308: $this->into($table);
1309:
1310: return parent::insert($columns, $types);
1311: }
1312:
1313: /**
1314: * {@inheritDoc}
1315: *
1316: * @throws \BadMethodCallException if the method is called for a non-select query
1317: */
1318: public function __call($method, $arguments)
1319: {
1320: if ($this->type() === 'select') {
1321: return $this->_call($method, $arguments);
1322: }
1323:
1324: throw new \BadMethodCallException(
1325: sprintf('Cannot call method "%s" on a "%s" query', $method, $this->type())
1326: );
1327: }
1328:
1329: /**
1330: * {@inheritDoc}
1331: */
1332: public function __debugInfo()
1333: {
1334: $eagerLoader = $this->getEagerLoader();
1335:
1336: return parent::__debugInfo() + [
1337: 'hydrate' => $this->_hydrate,
1338: 'buffered' => $this->_useBufferedResults,
1339: 'formatters' => count($this->_formatters),
1340: 'mapReducers' => count($this->_mapReduce),
1341: 'contain' => $eagerLoader ? $eagerLoader->getContain() : [],
1342: 'matching' => $eagerLoader ? $eagerLoader->getMatching() : [],
1343: 'extraOptions' => $this->_options,
1344: 'repository' => $this->_repository
1345: ];
1346: }
1347:
1348: /**
1349: * Executes the query and converts the result set into JSON.
1350: *
1351: * Part of JsonSerializable interface.
1352: *
1353: * @return \Cake\Datasource\ResultSetInterface The data to convert to JSON.
1354: */
1355: public function jsonSerialize()
1356: {
1357: return $this->all();
1358: }
1359:
1360: /**
1361: * Sets whether or not the ORM should automatically append fields.
1362: *
1363: * By default calling select() will disable auto-fields. You can re-enable
1364: * auto-fields with this method.
1365: *
1366: * @param bool $value Set true to enable, false to disable.
1367: * @return $this
1368: */
1369: public function enableAutoFields($value = true)
1370: {
1371: $this->_autoFields = (bool)$value;
1372:
1373: return $this;
1374: }
1375:
1376: /**
1377: * Disables automatically appending fields.
1378: *
1379: * @return $this
1380: */
1381: public function disableAutoFields()
1382: {
1383: $this->_autoFields = false;
1384:
1385: return $this;
1386: }
1387:
1388: /**
1389: * Gets whether or not the ORM should automatically append fields.
1390: *
1391: * By default calling select() will disable auto-fields. You can re-enable
1392: * auto-fields with enableAutoFields().
1393: *
1394: * @return bool|null The current value. Returns null if neither enabled or disabled yet.
1395: */
1396: public function isAutoFieldsEnabled()
1397: {
1398: return $this->_autoFields;
1399: }
1400:
1401: /**
1402: * Get/Set whether or not the ORM should automatically append fields.
1403: *
1404: * By default calling select() will disable auto-fields. You can re-enable
1405: * auto-fields with this method.
1406: *
1407: * @deprecated 3.4.0 Use enableAutoFields()/isAutoFieldsEnabled() instead.
1408: * @param bool|null $value The value to set or null to read the current value.
1409: * @return bool|null|$this Either the current value or the query object.
1410: */
1411: public function autoFields($value = null)
1412: {
1413: deprecationWarning(
1414: 'Query::autoFields() is deprecated. ' .
1415: 'Use enableAutoFields()/isAutoFieldsEnabled() instead.'
1416: );
1417: if ($value === null) {
1418: return $this->isAutoFieldsEnabled();
1419: }
1420:
1421: return $this->enableAutoFields($value);
1422: }
1423:
1424: /**
1425: * Decorates the results iterator with MapReduce routines and formatters
1426: *
1427: * @param \Traversable $result Original results
1428: * @return \Cake\Datasource\ResultSetInterface
1429: */
1430: protected function _decorateResults($result)
1431: {
1432: $result = $this->_applyDecorators($result);
1433:
1434: if (!($result instanceof ResultSet) && $this->isBufferedResultsEnabled()) {
1435: $class = $this->_decoratorClass();
1436: $result = new $class($result->buffered());
1437: }
1438:
1439: return $result;
1440: }
1441: }
1442: