1 <?php
  2 
  3 /*
  4  * This file is part of the ICanBoogie package.
  5  *
  6  * (c) Olivier Laviale <olivier.laviale@gmail.com>
  7  *
  8  * For the full copyright and license information, please view the LICENSE
  9  * file that was distributed with this source code.
 10  */
 11 
 12 namespace ICanBoogie\ActiveRecord;
 13 
 14 use ICanBoogie\DateTime;
 15 use ICanBoogie\Prototype\MethodNotDefined;
 16 
 17 /**
 18  * The class offers many features to compose model queries. Most query related
 19  * methods of the {@link Model} class create a {@link Query} object that is returned for
 20  * further specification, such as filters or limits.
 21  *
 22  * @method Query and() and($conditions, $conditions_args=null) Alias to {@link where()}.
 23  *
 24  * @property-read array $all An array with all the records matching the query.
 25  * @property-read mixed $one The first record matching the query.
 26  * @property-read array $pairs An array of key/value pairs.
 27  * @property-read array $rc The first column of the first row matching the query.
 28  * @property-read int $count The number of records matching the query.
 29  * @property-read bool|array $exists `true` if a record matching the query exists, `false`
 30  * otherwise. If there is multiple records, the property is an array of booleans.
 31  * @property-read Model $model The target model of the query.
 32  *
 33  * @see http://dev.mysql.com/doc/refman/5.6/en/select.html
 34  */
 35 class Query extends \ICanBoogie\Object implements \IteratorAggregate
 36 {
 37     const LIMIT_MAX = '18446744073709551615';
 38 
 39     protected $select;
 40     protected $join;
 41 
 42     protected $conditions = [];
 43     protected $conditions_args = [];
 44 
 45     protected $group;
 46     protected $order;
 47     protected $having;
 48     protected $having_args = [];
 49 
 50     protected $offset;
 51     protected $limit;
 52 
 53     protected $mode;
 54 
 55     /**
 56      * The target model of the query.
 57      *
 58      * @var Model
 59      */
 60     protected $model;
 61 
 62     /**
 63      * Constructor.
 64      *
 65      * @param Model $model The model to query.
 66      */
 67     public function __construct(Model $model)
 68     {
 69         $this->model = $model;
 70     }
 71 
 72     /**
 73      * Adds support for model's scopes.
 74      */
 75     public function __get($property)
 76     {
 77         if ($property == 'model')
 78         {
 79             return $this->model;
 80         }
 81 
 82         $scopes = $this->get_model_scope();
 83 
 84         if (in_array($property, $scopes))
 85         {
 86             return $this->model->scope($property, [ $this ]);
 87         }
 88 
 89         return parent::__get($property);
 90     }
 91 
 92     /**
 93      * Override the method to handle magic 'filter_by_' methods.
 94      */
 95     public function __call($method, $arguments)
 96     {
 97         if ($method === 'and')
 98         {
 99             return call_user_func_array([ $this, 'where' ], $arguments);
100         }
101 
102         if (strpos($method, 'filter_by_') === 0)
103         {
104             return $this->dynamic_filter(substr($method, 10), $arguments); // 10 is for: strlen('filter_by_')
105         }
106 
107         $scopes = $this->get_model_scope();
108 
109         if (in_array($method, $scopes))
110         {
111             array_unshift($arguments, $this);
112 
113             return $this->model->scope($method, $arguments);
114         }
115 
116         try
117         {
118             return parent::__call($method, $arguments);
119         }
120         catch (MethodNotDefined $e)
121         {
122             throw new ScopeNotDefined($method, $this->model, 500, $e);
123         }
124     }
125 
126     /**
127      * Converts the query into a string.
128      *
129      * @return string
130      */
131     public function __toString()
132     {
133         return $this->model->resolve_statement
134         (
135             'SELECT ' . ($this->select ? $this->select : '*') . ' FROM {self_and_related}' . $this->build()
136         );
137     }
138 
139     /**
140      * Caches available scopes by model class.
141      *
142      * @var array[]string
143      */
144     static protected $scopes_by_classes = [];
145 
146     /**
147      * Returns the available scopes for a model class.
148      *
149      * The method uses reflexion to find the scopes, the result is cached.
150      *
151      * @return array[]string
152      */
153     protected function get_model_scope()
154     {
155         $class = get_class($this->model);
156 
157         if (isset(self::$scopes_by_classes[$class]))
158         {
159             return self::$scopes_by_classes[$class];
160         }
161 
162         $reflexion = new \ReflectionClass($class);
163         $methods = $reflexion->getMethods(\ReflectionMethod::IS_PROTECTED);
164 
165         $scopes = [];
166 
167         foreach ($methods as $method)
168         {
169             $name = $method->name;
170 
171             if (strpos($name, 'scope_') !== 0)
172             {
173                 continue;
174             }
175 
176             $scopes[] = substr($name, 6);
177         }
178 
179         return self::$scopes_by_classes[$class] = $scopes;
180     }
181 
182     /**
183      * Defines the SELECT clause.
184      *
185      * @param string $expression The expression of the SELECT clause. e.g. 'nid, title'.
186      *
187      * @return Query
188      */
189     public function select($expression)
190     {
191         $this->select = $expression;
192 
193         return $this;
194     }
195 
196     /**
197      * Adds a JOIN clause.
198      *
199      * @param string $expression The expression can be a full JOIN clause or a reference to a
200      * model defined as ":<model_id>" e.g. ":nodes".
201      *
202      * @return Query
203      */
204     public function joins($expression)
205     {
206         if ($expression{0} == ':')
207         {
208             $primary = $this->model->primary;
209 
210             $model = get_model(substr($expression, 1));
211             $model_schema = $model->extended_schema;
212 
213             if (is_array($primary))
214             {
215                 foreach ($primary as $column)
216                 {
217                     if (isset($model_schema['fields'][$column]))
218                     {
219                         $primary = $column;
220 
221                         break;
222                     }
223                 }
224             }
225             else if (empty($model_schema['fields'][$primary]))
226             {
227                 $primary = $model_schema['primary'];
228 
229                 if (is_array($primary))
230                 {
231                     $primary = current($primary);
232                 }
233             }
234 
235             $expression = $model->resolve_statement("INNER JOIN `{self}` AS `{alias}` USING(`{$primary}`)");
236         }
237 
238         $this->join .= ($this->join ? ' ' : '') . $expression;
239 
240         return $this;
241     }
242 
243     /**
244      * Parses the conditions for the {@link where()} and {@link having()} methods.
245      *
246      * {@link \DateTime} conditions are converted to strings.
247      *
248      * @return array An array made of the condition string and its arguments.
249      */
250     private function defered_parse_conditions()
251     {
252         $trace = debug_backtrace(false);
253         $args = $trace[1]['args'];
254 
255         $conditions = array_shift($args);
256 
257         if (is_array($conditions))
258         {
259             $c = '';
260             $conditions_args = [];
261 
262             foreach ($conditions as $column => $arg)
263             {
264                 if (is_array($arg))
265                 {
266                     $joined = '';
267 
268                     foreach ($arg as $value)
269                     {
270                         $joined .= ',' . (is_numeric($value) ? $value : $this->model->quote($value));
271                     }
272 
273                     $joined = substr($joined, 1);
274 
275                     $c .= ' AND `' . ($column{0} == '!' ? substr($column, 1) . '` NOT' : $column . '`') . ' IN(' . $joined . ')';
276                 }
277                 else
278                 {
279                     $conditions_args[] = $arg;
280 
281                     $c .= ' AND `' . ($column{0} == '!' ? substr($column, 1) . '` !' : $column . '` ') . '= ?';
282                 }
283             }
284 
285             $conditions = substr($c, 5);
286         }
287         else
288         {
289             $conditions_args = [];
290 
291             if ($args)
292             {
293                 if (is_array($args[0]))
294                 {
295                     $conditions_args = $args[0];
296                 }
297                 else
298                 {
299                     #
300                     # We dereference values otherwise the caller would get a corrupted array.
301                     #
302 
303                     foreach ($args as $key => $value)
304                     {
305                         $conditions_args[$key] = $value;
306                     }
307                 }
308             }
309         }
310 
311         foreach ($conditions_args as &$value)
312         {
313             if ($value instanceof \DateTime)
314             {
315                 $value = DateTime::from($value)->utc->as_db;
316             }
317         }
318 
319         return [ $conditions ? '(' . $conditions . ')' : null, $conditions_args ];
320     }
321 
322     private function dynamic_filter($filter, array $conditions_args=[])
323     {
324         $conditions = explode('_and_', $filter);
325 
326         return $this->where(array_combine($conditions, $conditions_args));
327     }
328 
329     /**
330      * Add conditions to the SQL statement.
331      *
332      * Conditions can either be specified as string or array.
333      *
334      * 1. Pure string conditions
335      *
336      * If you'de like to add conditions to your statement, you could just specify them in there,
337      * just like `$model->where('order_count = 2');`. This will find all the entries, where the
338      * `order_count` field's value is 2.
339      *
340      * 2. Array conditions
341      *
342      * Now what if that number could vary, say as an argument from somewhere, or perhaps from the
343      * user’s level status somewhere? The find then becomes something like:
344      *
345      * `$model->where('order_count = ?', 2);`
346      *
347      * or
348      *
349      * `$model->where([ 'order_count' => 2 ]);`
350      *
351      * Or if you want to specify two conditions, you can do it like:
352      *
353      * `$model->where('order_count = ? AND locked = ?', 2, false);`
354      *
355      * or
356      *
357      * `$model->where([ 'order_count' => 2, 'locked' => false ]);`
358      *
359      * Or if you want to specify subset conditions:
360      *
361      * `$model->where([ 'order_id' => [ 123, 456, 789 ] ]);`
362      *
363      * This will return the orders with the `order_id` 123, 456 or 789.
364      *
365      * 3. Modifiers
366      *
367      * When using the "identifier" => "value" notation, you can switch the comparison method by
368      * prefixing the identifier with a bang "!"
369      *
370      * `$model->where([ '!order_id' => [ 123, 456, 789 ]]);`
371      *
372      * This will return the orders with the `order_id` different than 123, 456 and 789.
373      *
374      * `$model->where([ '!order_count' => 2 ];`
375      *
376      * This will return the orders with the `order_count` different than 2.
377      *
378      * @param mixed $conditions
379      * @param mixed $conditions_args
380      *
381      * @return Query
382      */
383     public function where($conditions, $conditions_args=null)
384     {
385         list($conditions, $conditions_args) = $this->defered_parse_conditions();
386 
387         if ($conditions)
388         {
389             $this->conditions[] = $conditions;
390 
391             if ($conditions_args)
392             {
393                 $this->conditions_args = array_merge($this->conditions_args, $conditions_args);
394             }
395         }
396 
397         return $this;
398     }
399 
400     /**
401      * Defines the ORDER clause.
402      *
403      * @param string $order The order for the ORDER clause e.g. 'weight, date DESC'.
404      *
405      * @return Query
406      */
407     public function order($order_or_field_name, $field_values=null)
408     {
409         $this->order = func_get_args();
410 
411         return $this;
412     }
413 
414     /**
415      * Defines the GROUP clause.
416      *
417      * @param $group
418      *
419      * @return Query
420      */
421     public function group($group)
422     {
423         $this->group = $group;
424 
425         return $this;
426     }
427 
428     /**
429      * Defines the HAVING clause.
430      *
431      * @param $conditions
432      * @param array|null $conditions_args
433      *
434      * @return Query
435      */
436     public function having($conditions, $conditions_args=null)
437     {
438         list($having, $having_args) = $this->defered_parse_conditions();
439 
440         $this->having = $having;
441         $this->having_args = $having_args;
442 
443         return $this;
444     }
445 
446     /**
447      * Defines the offset of the LIMIT clause.
448      *
449      * @param $offset
450      *
451      * @return Query
452      */
453     public function offset($offset)
454     {
455         $this->offset = (int) $offset;
456 
457         return $this;
458     }
459 
460     /**
461      * Apply the limit and/or offset to the SQL fired.
462      *
463      * You can use the limit to specify the number of records to be retrieved, ad use the offset to
464      * specify the number of records to skip before starting to return records:
465      *
466      *   $model->limit(10);
467      *
468      * Will return a maximum of 10 clients and because ti specifies no offset it will return the
469      * first 10 in the table:
470      *
471      *   $model->limit(5, 10);
472      *
473      * Will return a maximum of 10 clients beginning with the 5th.
474      *
475      * @param int $limit
476      *
477      * @return Query
478      */
479     public function limit($limit)
480     {
481         $offset = null;
482 
483         if (func_num_args() == 2)
484         {
485             $offset = $limit;
486             $limit = func_get_arg(1);
487         }
488 
489         $this->offset = (int) $offset;
490         $this->limit = (int) $limit;
491 
492         return $this;
493     }
494 
495     /**
496      * Set the fetch mode for the query.
497      *
498      * @param mixed $mode
499      *
500      * @return \ICanBoogie\ActiveRecord\Query
501      *
502      * @see http://www.php.net/manual/en/pdostatement.setfetchmode.php
503      */
504     public function mode($mode)
505     {
506         $this->mode = func_get_args();
507 
508         return $this;
509     }
510 
511     /**
512      * Builds the query except for the SELECT and FROM parts.
513      *
514      * @return string The query as a string.
515      */
516     protected function build()
517     {
518         $query = '';
519 
520         if ($this->join)
521         {
522             $query .= ' ' . $this->join;
523         }
524 
525         if ($this->conditions)
526         {
527             $query .= ' WHERE ' . implode(' AND ', $this->conditions);
528         }
529 
530         if ($this->group)
531         {
532             $query .= ' GROUP BY ' . $this->group;
533 
534             if ($this->having)
535             {
536                 $query .= ' HAVING ' . $this->having;
537             }
538         }
539 
540         $order = $this->order;
541 
542         if ($order)
543         {
544             $query .= ' ' . $this->render_order($order);
545         }
546 
547         $offset = $this->offset;
548         $limit = $this->limit;
549 
550         if ($offset || $limit)
551         {
552             $query .= ' ' . $this->render_offset_and_limit($offset, $limit);
553         }
554 
555         return $query;
556     }
557 
558     protected function render_order($order)
559     {
560         if (count($order) == 1)
561         {
562             return 'ORDER BY ' . $order[0];
563         }
564 
565         $connection = $this->model->connection;
566 
567         $field = array_shift($order);
568         $field_values = is_array($order[0]) ? $order[0] : $order;
569         $field_values = array_map(function($v) use($connection) {
570 
571             return $connection->quote($v);
572 
573         }, $field_values);
574 
575         return "ORDER BY FIELD($field, " . implode(', ', $field_values) . ")";
576     }
577 
578     protected function render_offset_and_limit($offset, $limit)
579     {
580         if ($offset && $limit)
581         {
582             return "LIMIT $offset, $limit";
583         }
584         else if ($offset)
585         {
586             return "LIMIT $offset, " . self::LIMIT_MAX;
587         }
588         else if ($limit)
589         {
590             return "LIMIT $limit";
591         }
592     }
593 
594     /**
595      * Prepares the query.
596      *
597      * We use the connection's prepare() method because the statement has already been resolved
598      * during the __toString() method and we don't want for the statement to be parsed twice.
599      *
600      * @return \ICanBoogie\Database\Statement
601      */
602     protected function prepare()
603     {
604         return $this->model->connection->prepare((string) $this);
605     }
606 
607     /**
608      * Prepares and executes the query.
609      *
610      * @return \ICanBoogie\Database\Statement
611      */
612     public function query()
613     {
614         $statement = $this->prepare();
615         $statement->execute(array_merge($this->conditions_args, $this->having_args));
616 
617         return $statement;
618     }
619 
620     /**
621      * Returns a prepared query.
622      *
623      * @return \ICanBoogie\Database\Statement
624      */
625     protected function get_prepared()
626     {
627         return $this->prepare();
628     }
629 
630     /*
631      * FINISHER
632      */
633 
634     private function resolve_fetch_mode()
635     {
636         $trace = debug_backtrace(false);
637 
638         if ($trace[1]['args'])
639         {
640             $args = $trace[1]['args'];
641         }
642         else if ($this->mode)
643         {
644             $args = $this->mode;
645         }
646         else if ($this->select)
647         {
648             $args = [ \PDO::FETCH_ASSOC ];
649         }
650         else if ($this->model->activerecord_class)
651         {
652             $args = [ \PDO::FETCH_CLASS, $this->model->activerecord_class, [ $this->model ]];
653         }
654         else
655         {
656             $args = [ \PDO::FETCH_CLASS, 'ICanBoogie\ActiveRecord', [ $this->model ]];
657         }
658 
659         return $args;
660     }
661 
662     /**
663      * Executes the query and returns an array of records.
664      *
665      * @return array
666      */
667     public function all()
668     {
669         $statement = $this->query();
670         $args = $this->resolve_fetch_mode();
671 
672         return call_user_func_array([ $statement, 'fetchAll' ], $args);
673     }
674 
675     /**
676      * Getter for the {@link $all} magic property.
677      *
678      * @return array
679      */
680     protected function get_all()
681     {
682         return $this->all();
683     }
684 
685     /**
686      * Returns the first result of the query and close the cursor.
687      *
688      * @return mixed The return value of this function on success depends on the fetch mode. In
689      * all cases, FALSE is returned on failure.
690      */
691     public function one()
692     {
693         $previous_limit = $this->limit;
694 
695         $this->limit = 1;
696 
697         $statement = $this->query();
698 
699         $this->limit = $previous_limit;
700 
701         $args = $this->resolve_fetch_mode();
702 
703         if (count($args) > 1 && $args[0] == \PDO::FETCH_CLASS)
704         {
705             array_shift($args);
706 
707             $rc = call_user_func_array([ $statement, 'fetchObject' ], $args);
708 
709             $statement->closeCursor();
710 
711             return $rc;
712         }
713 
714         return call_user_func_array([ $statement, 'fetchAndClose' ], $args);
715     }
716 
717     /**
718      * Getter for the {@link $one} magic property.
719      *
720      * @return mixed
721      *
722      * @see one()
723      */
724     protected function get_one()
725     {
726         return $this->one();
727     }
728 
729     /**
730      * Execute que query and returns an array of key/value pairs, where the key is the value of
731      * the first column and the value of the key the value of the second column.
732      *
733      * @return array
734      */
735     protected function get_pairs()
736     {
737         return $this->all(\PDO::FETCH_KEY_PAIR);
738     }
739 
740     /**
741      * Returns the value of the first column of the first row.
742      *
743      * @return string
744      */
745     protected function get_rc()
746     {
747         $previous_limit = $this->limit;
748 
749         $this->limit = 1;
750 
751         $statement = $this->query();
752 
753         $this->limit = $previous_limit;
754 
755         return $statement->fetchColumnAndClose();
756     }
757 
758     /**
759      * Checks the existence of records in the model.
760      *
761      * $model->exists;
762      * $model->where('name = "max"')->exists;
763      * $model->exists(1);
764      * $model->exists(1, 2);
765      * $model->exists([ 1, 2 ]);
766      *
767      * @param mixed $key
768      *
769      * @return bool|array
770      */
771     public function exists($key=null)
772     {
773         $suffix = '';
774 
775         if ($key !== null)
776         {
777             if (func_num_args() > 1)
778             {
779                 $key = func_get_args();
780             }
781 
782             $this->where([ '{primary}' => $key ]);
783         }
784         else if (!$this->limit)
785         {
786             $suffix = ' LIMIT 1';
787         }
788 
789         $rc = $this
790         ->model
791         ->query('SELECT `{primary}` FROM {self_and_related}' . $this->build() . $suffix, array_merge($this->conditions_args, $this->having_args))
792         ->fetchAll(\PDO::FETCH_COLUMN);
793 
794         if ($rc && is_array($key))
795         {
796             $exists = array_combine($key, array_fill(0, count($key), false));
797 
798             foreach ($rc as $key)
799             {
800                 $exists[$key] = true;
801             }
802 
803             foreach ($exists as $v)
804             {
805                 if (!$v)
806                 {
807                     return $exists;
808                 }
809             }
810 
811             # all true
812 
813             return true;
814         }
815         else
816         {
817             $rc = !empty($rc);
818         }
819 
820         return $rc;
821     }
822 
823     /**
824      * Getter for the {@link $exists} magic property.
825      *
826      * @return bool|array
827      *
828      * @see exists()
829      */
830     protected function get_exists()
831     {
832         return $this->exists();
833     }
834 
835     /**
836      * Handles all the computations.
837      *
838      * @param string $method
839      * @param string $column
840      *
841      * @return int|array
842      */
843     private function compute($method, $column)
844     {
845         $query = 'SELECT ';
846 
847         if ($column)
848         {
849             if ($method == 'COUNT')
850             {
851                 $query .= "`$column`, $method(`$column`)";
852 
853                 $this->group($column);
854             }
855             else
856             {
857                 $query .= "$method(`$column`)";
858             }
859         }
860         else
861         {
862             $query .= $method . '(*)';
863         }
864 
865         $query .= ' AS count FROM {self_and_related}' . $this->build();
866         $query = $this->model->query($query, array_merge($this->conditions_args, $this->having_args));
867 
868         if ($method == 'COUNT' && $column)
869         {
870             return $query->fetchAll(\PDO::FETCH_KEY_PAIR);
871         }
872 
873         return (int) $query->fetchColumnAndClose();
874     }
875 
876     /**
877      * Implements the 'COUNT' computation.
878      *
879      * @return int|array
880      */
881     public function count($column=null)
882     {
883         return $this->compute('COUNT', $column);
884     }
885 
886     /**
887      * Getter for the {@link $count} magic property.
888      *
889      * @return int
890      */
891     protected function get_count()
892     {
893         return $this->count();
894     }
895 
896     /**
897      * Implements the 'AVG' computation.
898      *
899      * @param string $column
900      *
901      * @return int
902      */
903     public function average($column)
904     {
905         return $this->compute('AVG', $column);
906     }
907 
908     /**
909      * Implements the 'MIN' computation.
910      *
911      * @param string $column
912      *
913      * @return int
914      */
915     public function minimum($column)
916     {
917         return $this->compute('MIN', $column);
918     }
919 
920     /**
921      * Implements the 'MAX' computation.
922      *
923      * @param string $column
924      *
925      * @return int
926      */
927     public function maximum($column)
928     {
929         return $this->compute('MAX', $column);
930     }
931 
932     /**
933      * Implements the 'SUM' computation.
934      *
935      * @param string $column
936      *
937      * @return int
938      */
939     public function sum($column)
940     {
941         return $this->compute('SUM', $column);
942     }
943 
944     /**
945      * Deletes the records matching the conditions and limits of the query.
946      *
947      * @return mixed The result of the operation.
948      */
949     public function delete()
950     {
951         $query = 'DELETE FROM {self} ' . $this->build();
952 
953         return $this->model->execute($query, $this->conditions_args);
954     }
955 
956     /**
957      * Returns an iterator for the query.
958      */
959     public function getIterator()
960     {
961         return new \ArrayIterator($this->all());
962     }
963 }