1 <?php
2 /**
3 * @package FrameworkOnFramework
4 * @subpackage database
5 * @copyright Copyright (C) 2010-2016 Nicholas K. Dionysopoulos / Akeeba Ltd. All rights reserved.
6 * @license GNU General Public License version 2 or later; see LICENSE.txt
7 *
8 * This file is adapted from the Joomla! Platform. It is used to iterate a database cursor returning FOFTable objects
9 * instead of plain stdClass objects
10 */
11
12 // Protect from unauthorized access
13 defined('FOF_INCLUDED') or die;
14
15 /**
16 * Query Building Class.
17 *
18 * @since 11.1
19 *
20 * @method string q() q($text, $escape = true) Alias for quote method
21 * @method string qn() qn($name, $as = null) Alias for quoteName method
22 * @method string e() e($text, $extra = false) Alias for escape method
23 * @property-read FOFDatabaseQueryElement $type
24 * @property-read FOFDatabaseQueryElement $select
25 * @property-read FOFDatabaseQueryElement $group
26 * @property-read FOFDatabaseQueryElement $having
27 */
28 abstract class FOFDatabaseQuery
29 {
30 /**
31 * @var FOFDatabaseDriver The database driver.
32 * @since 11.1
33 */
34 protected $db = null;
35
36 /**
37 * @var string The SQL query (if a direct query string was provided).
38 * @since 12.1
39 */
40 protected $sql = null;
41
42 /**
43 * @var string The query type.
44 * @since 11.1
45 */
46 protected $type = '';
47
48 /**
49 * @var FOFDatabaseQueryElement The query element for a generic query (type = null).
50 * @since 11.1
51 */
52 protected $element = null;
53
54 /**
55 * @var FOFDatabaseQueryElement The select element.
56 * @since 11.1
57 */
58 protected $select = null;
59
60 /**
61 * @var FOFDatabaseQueryElement The delete element.
62 * @since 11.1
63 */
64 protected $delete = null;
65
66 /**
67 * @var FOFDatabaseQueryElement The update element.
68 * @since 11.1
69 */
70 protected $update = null;
71
72 /**
73 * @var FOFDatabaseQueryElement The insert element.
74 * @since 11.1
75 */
76 protected $insert = null;
77
78 /**
79 * @var FOFDatabaseQueryElement The from element.
80 * @since 11.1
81 */
82 protected $from = null;
83
84 /**
85 * @var FOFDatabaseQueryElement The join element.
86 * @since 11.1
87 */
88 protected $join = null;
89
90 /**
91 * @var FOFDatabaseQueryElement The set element.
92 * @since 11.1
93 */
94 protected $set = null;
95
96 /**
97 * @var FOFDatabaseQueryElement The where element.
98 * @since 11.1
99 */
100 protected $where = null;
101
102 /**
103 * @var FOFDatabaseQueryElement The group by element.
104 * @since 11.1
105 */
106 protected $group = null;
107
108 /**
109 * @var FOFDatabaseQueryElement The having element.
110 * @since 11.1
111 */
112 protected $having = null;
113
114 /**
115 * @var FOFDatabaseQueryElement The column list for an INSERT statement.
116 * @since 11.1
117 */
118 protected $columns = null;
119
120 /**
121 * @var FOFDatabaseQueryElement The values list for an INSERT statement.
122 * @since 11.1
123 */
124 protected $values = null;
125
126 /**
127 * @var FOFDatabaseQueryElement The order element.
128 * @since 11.1
129 */
130 protected $order = null;
131
132 /**
133 * @var object The auto increment insert field element.
134 * @since 11.1
135 */
136 protected $autoIncrementField = null;
137
138 /**
139 * @var FOFDatabaseQueryElement The call element.
140 * @since 12.1
141 */
142 protected $call = null;
143
144 /**
145 * @var FOFDatabaseQueryElement The exec element.
146 * @since 12.1
147 */
148 protected $exec = null;
149
150 /**
151 * @var FOFDatabaseQueryElement The union element.
152 * @since 12.1
153 */
154 protected $union = null;
155
156 /**
157 * @var FOFDatabaseQueryElement The unionAll element.
158 * @since 13.1
159 */
160 protected $unionAll = null;
161
162 /**
163 * Magic method to provide method alias support for quote() and quoteName().
164 *
165 * @param string $method The called method.
166 * @param array $args The array of arguments passed to the method.
167 *
168 * @return string The aliased method's return value or null.
169 *
170 * @since 11.1
171 */
172 public function __call($method, $args)
173 {
174 if (empty($args))
175 {
176 return;
177 }
178
179 switch ($method)
180 {
181 case 'q':
182 return $this->quote($args[0], isset($args[1]) ? $args[1] : true);
183 break;
184
185 case 'qn':
186 return $this->quoteName($args[0], isset($args[1]) ? $args[1] : null);
187 break;
188
189 case 'e':
190 return $this->escape($args[0], isset($args[1]) ? $args[1] : false);
191 break;
192 }
193 }
194
195 /**
196 * Class constructor.
197 *
198 * @param FOFDatabaseDriver $db The database driver.
199 *
200 * @since 11.1
201 */
202 public function __construct(FOFDatabaseDriver $db = null)
203 {
204 $this->db = $db;
205 }
206
207 /**
208 * Magic function to convert the query to a string.
209 *
210 * @return string The completed query.
211 *
212 * @since 11.1
213 */
214 public function __toString()
215 {
216 $query = '';
217
218 if ($this->sql)
219 {
220 return $this->sql;
221 }
222
223 switch ($this->type)
224 {
225 case 'element':
226 $query .= (string) $this->element;
227 break;
228
229 case 'select':
230 $query .= (string) $this->select;
231 $query .= (string) $this->from;
232
233 if ($this->join)
234 {
235 // Special case for joins
236 foreach ($this->join as $join)
237 {
238 $query .= (string) $join;
239 }
240 }
241
242 if ($this->where)
243 {
244 $query .= (string) $this->where;
245 }
246
247 if ($this->group)
248 {
249 $query .= (string) $this->group;
250 }
251
252 if ($this->having)
253 {
254 $query .= (string) $this->having;
255 }
256
257 if ($this->order)
258 {
259 $query .= (string) $this->order;
260 }
261
262 if ($this->union)
263 {
264 $query .= (string) $this->union;
265 }
266
267 break;
268
269 case 'delete':
270 $query .= (string) $this->delete;
271 $query .= (string) $this->from;
272
273 if ($this->join)
274 {
275 // Special case for joins
276 foreach ($this->join as $join)
277 {
278 $query .= (string) $join;
279 }
280 }
281
282 if ($this->where)
283 {
284 $query .= (string) $this->where;
285 }
286
287 if ($this->order)
288 {
289 $query .= (string) $this->order;
290 }
291
292 break;
293
294 case 'update':
295 $query .= (string) $this->update;
296
297 if ($this->join)
298 {
299 // Special case for joins
300 foreach ($this->join as $join)
301 {
302 $query .= (string) $join;
303 }
304 }
305
306 $query .= (string) $this->set;
307
308 if ($this->where)
309 {
310 $query .= (string) $this->where;
311 }
312
313 if ($this->order)
314 {
315 $query .= (string) $this->order;
316 }
317
318 break;
319
320 case 'insert':
321 $query .= (string) $this->insert;
322
323 // Set method
324 if ($this->set)
325 {
326 $query .= (string) $this->set;
327 }
328 // Columns-Values method
329 elseif ($this->values)
330 {
331 if ($this->columns)
332 {
333 $query .= (string) $this->columns;
334 }
335
336 $elements = $this->values->getElements();
337
338 if (!($elements[0] instanceof $this))
339 {
340 $query .= ' VALUES ';
341 }
342
343 $query .= (string) $this->values;
344 }
345
346 break;
347
348 case 'call':
349 $query .= (string) $this->call;
350 break;
351
352 case 'exec':
353 $query .= (string) $this->exec;
354 break;
355 }
356
357 if ($this instanceof FOFDatabaseQueryLimitable)
358 {
359 $query = $this->processLimit($query, $this->limit, $this->offset);
360 }
361
362 return $query;
363 }
364
365 /**
366 * Magic function to get protected variable value
367 *
368 * @param string $name The name of the variable.
369 *
370 * @return mixed
371 *
372 * @since 11.1
373 */
374 public function __get($name)
375 {
376 return isset($this->$name) ? $this->$name : null;
377 }
378
379 /**
380 * Add a single column, or array of columns to the CALL clause of the query.
381 *
382 * Note that you must not mix insert, update, delete and select method calls when building a query.
383 * The call method can, however, be called multiple times in the same query.
384 *
385 * Usage:
386 * $query->call('a.*')->call('b.id');
387 * $query->call(array('a.*', 'b.id'));
388 *
389 * @param mixed $columns A string or an array of field names.
390 *
391 * @return FOFDatabaseQuery Returns this object to allow chaining.
392 *
393 * @since 12.1
394 */
395 public function call($columns)
396 {
397 $this->type = 'call';
398
399 if (is_null($this->call))
400 {
401 $this->call = new FOFDatabaseQueryElement('CALL', $columns);
402 }
403 else
404 {
405 $this->call->append($columns);
406 }
407
408 return $this;
409 }
410
411 /**
412 * Casts a value to a char.
413 *
414 * Ensure that the value is properly quoted before passing to the method.
415 *
416 * Usage:
417 * $query->select($query->castAsChar('a'));
418 *
419 * @param string $value The value to cast as a char.
420 *
421 * @return string Returns the cast value.
422 *
423 * @since 11.1
424 */
425 public function castAsChar($value)
426 {
427 return $value;
428 }
429
430 /**
431 * Gets the number of characters in a string.
432 *
433 * Note, use 'length' to find the number of bytes in a string.
434 *
435 * Usage:
436 * $query->select($query->charLength('a'));
437 *
438 * @param string $field A value.
439 * @param string $operator Comparison operator between charLength integer value and $condition
440 * @param string $condition Integer value to compare charLength with.
441 *
442 * @return string The required char length call.
443 *
444 * @since 11.1
445 */
446 public function charLength($field, $operator = null, $condition = null)
447 {
448 return 'CHAR_LENGTH(' . $field . ')' . (isset($operator) && isset($condition) ? ' ' . $operator . ' ' . $condition : '');
449 }
450
451 /**
452 * Clear data from the query or a specific clause of the query.
453 *
454 * @param string $clause Optionally, the name of the clause to clear, or nothing to clear the whole query.
455 *
456 * @return FOFDatabaseQuery Returns this object to allow chaining.
457 *
458 * @since 11.1
459 */
460 public function clear($clause = null)
461 {
462 $this->sql = null;
463
464 switch ($clause)
465 {
466 case 'select':
467 $this->select = null;
468 $this->type = null;
469 break;
470
471 case 'delete':
472 $this->delete = null;
473 $this->type = null;
474 break;
475
476 case 'update':
477 $this->update = null;
478 $this->type = null;
479 break;
480
481 case 'insert':
482 $this->insert = null;
483 $this->type = null;
484 $this->autoIncrementField = null;
485 break;
486
487 case 'from':
488 $this->from = null;
489 break;
490
491 case 'join':
492 $this->join = null;
493 break;
494
495 case 'set':
496 $this->set = null;
497 break;
498
499 case 'where':
500 $this->where = null;
501 break;
502
503 case 'group':
504 $this->group = null;
505 break;
506
507 case 'having':
508 $this->having = null;
509 break;
510
511 case 'order':
512 $this->order = null;
513 break;
514
515 case 'columns':
516 $this->columns = null;
517 break;
518
519 case 'values':
520 $this->values = null;
521 break;
522
523 case 'exec':
524 $this->exec = null;
525 $this->type = null;
526 break;
527
528 case 'call':
529 $this->call = null;
530 $this->type = null;
531 break;
532
533 case 'limit':
534 $this->offset = 0;
535 $this->limit = 0;
536 break;
537
538 case 'offset':
539 $this->offset = 0;
540 break;
541
542 case 'union':
543 $this->union = null;
544 break;
545
546 case 'unionAll':
547 $this->unionAll = null;
548 break;
549
550 default:
551 $this->type = null;
552 $this->select = null;
553 $this->delete = null;
554 $this->update = null;
555 $this->insert = null;
556 $this->from = null;
557 $this->join = null;
558 $this->set = null;
559 $this->where = null;
560 $this->group = null;
561 $this->having = null;
562 $this->order = null;
563 $this->columns = null;
564 $this->values = null;
565 $this->autoIncrementField = null;
566 $this->exec = null;
567 $this->call = null;
568 $this->union = null;
569 $this->unionAll = null;
570 $this->offset = 0;
571 $this->limit = 0;
572 break;
573 }
574
575 return $this;
576 }
577
578 /**
579 * Adds a column, or array of column names that would be used for an INSERT INTO statement.
580 *
581 * @param mixed $columns A column name, or array of column names.
582 *
583 * @return FOFDatabaseQuery Returns this object to allow chaining.
584 *
585 * @since 11.1
586 */
587 public function columns($columns)
588 {
589 if (is_null($this->columns))
590 {
591 $this->columns = new FOFDatabaseQueryElement('()', $columns);
592 }
593 else
594 {
595 $this->columns->append($columns);
596 }
597
598 return $this;
599 }
600
601 /**
602 * Concatenates an array of column names or values.
603 *
604 * Usage:
605 * $query->select($query->concatenate(array('a', 'b')));
606 *
607 * @param array $values An array of values to concatenate.
608 * @param string $separator As separator to place between each value.
609 *
610 * @return string The concatenated values.
611 *
612 * @since 11.1
613 */
614 public function concatenate($values, $separator = null)
615 {
616 if ($separator)
617 {
618 return 'CONCATENATE(' . implode(' || ' . $this->quote($separator) . ' || ', $values) . ')';
619 }
620 else
621 {
622 return 'CONCATENATE(' . implode(' || ', $values) . ')';
623 }
624 }
625
626 /**
627 * Gets the current date and time.
628 *
629 * Usage:
630 * $query->where('published_up < '.$query->currentTimestamp());
631 *
632 * @return string
633 *
634 * @since 11.1
635 */
636 public function currentTimestamp()
637 {
638 return 'CURRENT_TIMESTAMP()';
639 }
640
641 /**
642 * Returns a PHP date() function compliant date format for the database driver.
643 *
644 * This method is provided for use where the query object is passed to a function for modification.
645 * If you have direct access to the database object, it is recommended you use the getDateFormat method directly.
646 *
647 * @return string The format string.
648 *
649 * @since 11.1
650 */
651 public function dateFormat()
652 {
653 if (!($this->db instanceof FOFDatabaseDriver))
654 {
655 throw new RuntimeException('JLIB_DATABASE_ERROR_INVALID_DB_OBJECT');
656 }
657
658 return $this->db->getDateFormat();
659 }
660
661 /**
662 * Creates a formatted dump of the query for debugging purposes.
663 *
664 * Usage:
665 * echo $query->dump();
666 *
667 * @return string
668 *
669 * @since 11.3
670 */
671 public function dump()
672 {
673 return '<pre class="FOFDatabasequery">' . str_replace('#__', $this->db->getPrefix(), $this) . '</pre>';
674 }
675
676 /**
677 * Add a table name to the DELETE clause of the query.
678 *
679 * Note that you must not mix insert, update, delete and select method calls when building a query.
680 *
681 * Usage:
682 * $query->delete('#__a')->where('id = 1');
683 *
684 * @param string $table The name of the table to delete from.
685 *
686 * @return FOFDatabaseQuery Returns this object to allow chaining.
687 *
688 * @since 11.1
689 */
690 public function delete($table = null)
691 {
692 $this->type = 'delete';
693 $this->delete = new FOFDatabaseQueryElement('DELETE', null);
694
695 if (!empty($table))
696 {
697 $this->from($table);
698 }
699
700 return $this;
701 }
702
703 /**
704 * Method to escape a string for usage in an SQL statement.
705 *
706 * This method is provided for use where the query object is passed to a function for modification.
707 * If you have direct access to the database object, it is recommended you use the escape method directly.
708 *
709 * Note that 'e' is an alias for this method as it is in FOFDatabaseDriver.
710 *
711 * @param string $text The string to be escaped.
712 * @param boolean $extra Optional parameter to provide extra escaping.
713 *
714 * @return string The escaped string.
715 *
716 * @since 11.1
717 * @throws RuntimeException if the internal db property is not a valid object.
718 */
719 public function escape($text, $extra = false)
720 {
721 if (!($this->db instanceof FOFDatabaseDriver))
722 {
723 throw new RuntimeException('JLIB_DATABASE_ERROR_INVALID_DB_OBJECT');
724 }
725
726 return $this->db->escape($text, $extra);
727 }
728
729 /**
730 * Add a single column, or array of columns to the EXEC clause of the query.
731 *
732 * Note that you must not mix insert, update, delete and select method calls when building a query.
733 * The exec method can, however, be called multiple times in the same query.
734 *
735 * Usage:
736 * $query->exec('a.*')->exec('b.id');
737 * $query->exec(array('a.*', 'b.id'));
738 *
739 * @param mixed $columns A string or an array of field names.
740 *
741 * @return FOFDatabaseQuery Returns this object to allow chaining.
742 *
743 * @since 12.1
744 */
745 public function exec($columns)
746 {
747 $this->type = 'exec';
748
749 if (is_null($this->exec))
750 {
751 $this->exec = new FOFDatabaseQueryElement('EXEC', $columns);
752 }
753 else
754 {
755 $this->exec->append($columns);
756 }
757
758 return $this;
759 }
760
761 /**
762 * Add a table to the FROM clause of the query.
763 *
764 * Note that while an array of tables can be provided, it is recommended you use explicit joins.
765 *
766 * Usage:
767 * $query->select('*')->from('#__a');
768 *
769 * @param mixed $tables A string or array of table names.
770 * This can be a FOFDatabaseQuery object (or a child of it) when used
771 * as a subquery in FROM clause along with a value for $subQueryAlias.
772 * @param string $subQueryAlias Alias used when $tables is a FOFDatabaseQuery.
773 *
774 * @return FOFDatabaseQuery Returns this object to allow chaining.
775 *
776 * @throws RuntimeException
777 *
778 * @since 11.1
779 */
780 public function from($tables, $subQueryAlias = null)
781 {
782 if (is_null($this->from))
783 {
784 if ($tables instanceof $this)
785 {
786 if (is_null($subQueryAlias))
787 {
788 throw new RuntimeException('JLIB_DATABASE_ERROR_NULL_SUBQUERY_ALIAS');
789 }
790
791 $tables = '( ' . (string) $tables . ' ) AS ' . $this->quoteName($subQueryAlias);
792 }
793
794 $this->from = new FOFDatabaseQueryElement('FROM', $tables);
795 }
796 else
797 {
798 $this->from->append($tables);
799 }
800
801 return $this;
802 }
803
804 /**
805 * Used to get a string to extract year from date column.
806 *
807 * Usage:
808 * $query->select($query->year($query->quoteName('dateColumn')));
809 *
810 * @param string $date Date column containing year to be extracted.
811 *
812 * @return string Returns string to extract year from a date.
813 *
814 * @since 12.1
815 */
816 public function year($date)
817 {
818 return 'YEAR(' . $date . ')';
819 }
820
821 /**
822 * Used to get a string to extract month from date column.
823 *
824 * Usage:
825 * $query->select($query->month($query->quoteName('dateColumn')));
826 *
827 * @param string $date Date column containing month to be extracted.
828 *
829 * @return string Returns string to extract month from a date.
830 *
831 * @since 12.1
832 */
833 public function month($date)
834 {
835 return 'MONTH(' . $date . ')';
836 }
837
838 /**
839 * Used to get a string to extract day from date column.
840 *
841 * Usage:
842 * $query->select($query->day($query->quoteName('dateColumn')));
843 *
844 * @param string $date Date column containing day to be extracted.
845 *
846 * @return string Returns string to extract day from a date.
847 *
848 * @since 12.1
849 */
850 public function day($date)
851 {
852 return 'DAY(' . $date . ')';
853 }
854
855 /**
856 * Used to get a string to extract hour from date column.
857 *
858 * Usage:
859 * $query->select($query->hour($query->quoteName('dateColumn')));
860 *
861 * @param string $date Date column containing hour to be extracted.
862 *
863 * @return string Returns string to extract hour from a date.
864 *
865 * @since 12.1
866 */
867 public function hour($date)
868 {
869 return 'HOUR(' . $date . ')';
870 }
871
872 /**
873 * Used to get a string to extract minute from date column.
874 *
875 * Usage:
876 * $query->select($query->minute($query->quoteName('dateColumn')));
877 *
878 * @param string $date Date column containing minute to be extracted.
879 *
880 * @return string Returns string to extract minute from a date.
881 *
882 * @since 12.1
883 */
884 public function minute($date)
885 {
886 return 'MINUTE(' . $date . ')';
887 }
888
889 /**
890 * Used to get a string to extract seconds from date column.
891 *
892 * Usage:
893 * $query->select($query->second($query->quoteName('dateColumn')));
894 *
895 * @param string $date Date column containing second to be extracted.
896 *
897 * @return string Returns string to extract second from a date.
898 *
899 * @since 12.1
900 */
901 public function second($date)
902 {
903 return 'SECOND(' . $date . ')';
904 }
905
906 /**
907 * Add a grouping column to the GROUP clause of the query.
908 *
909 * Usage:
910 * $query->group('id');
911 *
912 * @param mixed $columns A string or array of ordering columns.
913 *
914 * @return FOFDatabaseQuery Returns this object to allow chaining.
915 *
916 * @since 11.1
917 */
918 public function group($columns)
919 {
920 if (is_null($this->group))
921 {
922 $this->group = new FOFDatabaseQueryElement('GROUP BY', $columns);
923 }
924 else
925 {
926 $this->group->append($columns);
927 }
928
929 return $this;
930 }
931
932 /**
933 * A conditions to the HAVING clause of the query.
934 *
935 * Usage:
936 * $query->group('id')->having('COUNT(id) > 5');
937 *
938 * @param mixed $conditions A string or array of columns.
939 * @param string $glue The glue by which to join the conditions. Defaults to AND.
940 *
941 * @return FOFDatabaseQuery Returns this object to allow chaining.
942 *
943 * @since 11.1
944 */
945 public function having($conditions, $glue = 'AND')
946 {
947 if (is_null($this->having))
948 {
949 $glue = strtoupper($glue);
950 $this->having = new FOFDatabaseQueryElement('HAVING', $conditions, " $glue ");
951 }
952 else
953 {
954 $this->having->append($conditions);
955 }
956
957 return $this;
958 }
959
960 /**
961 * Add an INNER JOIN clause to the query.
962 *
963 * Usage:
964 * $query->innerJoin('b ON b.id = a.id')->innerJoin('c ON c.id = b.id');
965 *
966 * @param string $condition The join condition.
967 *
968 * @return FOFDatabaseQuery Returns this object to allow chaining.
969 *
970 * @since 11.1
971 */
972 public function innerJoin($condition)
973 {
974 $this->join('INNER', $condition);
975
976 return $this;
977 }
978
979 /**
980 * Add a table name to the INSERT clause of the query.
981 *
982 * Note that you must not mix insert, update, delete and select method calls when building a query.
983 *
984 * Usage:
985 * $query->insert('#__a')->set('id = 1');
986 * $query->insert('#__a')->columns('id, title')->values('1,2')->values('3,4');
987 * $query->insert('#__a')->columns('id, title')->values(array('1,2', '3,4'));
988 *
989 * @param mixed $table The name of the table to insert data into.
990 * @param boolean $incrementField The name of the field to auto increment.
991 *
992 * @return FOFDatabaseQuery Returns this object to allow chaining.
993 *
994 * @since 11.1
995 */
996 public function insert($table, $incrementField=false)
997 {
998 $this->type = 'insert';
999 $this->insert = new FOFDatabaseQueryElement('INSERT INTO', $table);
1000 $this->autoIncrementField = $incrementField;
1001
1002 return $this;
1003 }
1004
1005 /**
1006 * Add a JOIN clause to the query.
1007 *
1008 * Usage:
1009 * $query->join('INNER', 'b ON b.id = a.id);
1010 *
1011 * @param string $type The type of join. This string is prepended to the JOIN keyword.
1012 * @param string $conditions A string or array of conditions.
1013 *
1014 * @return FOFDatabaseQuery Returns this object to allow chaining.
1015 *
1016 * @since 11.1
1017 */
1018 public function join($type, $conditions)
1019 {
1020 if (is_null($this->join))
1021 {
1022 $this->join = array();
1023 }
1024
1025 $this->join[] = new FOFDatabaseQueryElement(strtoupper($type) . ' JOIN', $conditions);
1026
1027 return $this;
1028 }
1029
1030 /**
1031 * Add a LEFT JOIN clause to the query.
1032 *
1033 * Usage:
1034 * $query->leftJoin('b ON b.id = a.id')->leftJoin('c ON c.id = b.id');
1035 *
1036 * @param string $condition The join condition.
1037 *
1038 * @return FOFDatabaseQuery Returns this object to allow chaining.
1039 *
1040 * @since 11.1
1041 */
1042 public function leftJoin($condition)
1043 {
1044 $this->join('LEFT', $condition);
1045
1046 return $this;
1047 }
1048
1049 /**
1050 * Get the length of a string in bytes.
1051 *
1052 * Note, use 'charLength' to find the number of characters in a string.
1053 *
1054 * Usage:
1055 * query->where($query->length('a').' > 3');
1056 *
1057 * @param string $value The string to measure.
1058 *
1059 * @return int
1060 *
1061 * @since 11.1
1062 */
1063 public function length($value)
1064 {
1065 return 'LENGTH(' . $value . ')';
1066 }
1067
1068 /**
1069 * Get the null or zero representation of a timestamp for the database driver.
1070 *
1071 * This method is provided for use where the query object is passed to a function for modification.
1072 * If you have direct access to the database object, it is recommended you use the nullDate method directly.
1073 *
1074 * Usage:
1075 * $query->where('modified_date <> '.$query->nullDate());
1076 *
1077 * @param boolean $quoted Optionally wraps the null date in database quotes (true by default).
1078 *
1079 * @return string Null or zero representation of a timestamp.
1080 *
1081 * @since 11.1
1082 */
1083 public function nullDate($quoted = true)
1084 {
1085 if (!($this->db instanceof FOFDatabaseDriver))
1086 {
1087 throw new RuntimeException('JLIB_DATABASE_ERROR_INVALID_DB_OBJECT');
1088 }
1089
1090 $result = $this->db->getNullDate($quoted);
1091
1092 if ($quoted)
1093 {
1094 return $this->db->quote($result);
1095 }
1096
1097 return $result;
1098 }
1099
1100 /**
1101 * Add a ordering column to the ORDER clause of the query.
1102 *
1103 * Usage:
1104 * $query->order('foo')->order('bar');
1105 * $query->order(array('foo','bar'));
1106 *
1107 * @param mixed $columns A string or array of ordering columns.
1108 *
1109 * @return FOFDatabaseQuery Returns this object to allow chaining.
1110 *
1111 * @since 11.1
1112 */
1113 public function order($columns)
1114 {
1115 if (is_null($this->order))
1116 {
1117 $this->order = new FOFDatabaseQueryElement('ORDER BY', $columns);
1118 }
1119 else
1120 {
1121 $this->order->append($columns);
1122 }
1123
1124 return $this;
1125 }
1126
1127 /**
1128 * Add an OUTER JOIN clause to the query.
1129 *
1130 * Usage:
1131 * $query->outerJoin('b ON b.id = a.id')->outerJoin('c ON c.id = b.id');
1132 *
1133 * @param string $condition The join condition.
1134 *
1135 * @return FOFDatabaseQuery Returns this object to allow chaining.
1136 *
1137 * @since 11.1
1138 */
1139 public function outerJoin($condition)
1140 {
1141 $this->join('OUTER', $condition);
1142
1143 return $this;
1144 }
1145
1146 /**
1147 * Method to quote and optionally escape a string to database requirements for insertion into the database.
1148 *
1149 * This method is provided for use where the query object is passed to a function for modification.
1150 * If you have direct access to the database object, it is recommended you use the quote method directly.
1151 *
1152 * Note that 'q' is an alias for this method as it is in FOFDatabaseDriver.
1153 *
1154 * Usage:
1155 * $query->quote('fulltext');
1156 * $query->q('fulltext');
1157 * $query->q(array('option', 'fulltext'));
1158 *
1159 * @param mixed $text A string or an array of strings to quote.
1160 * @param boolean $escape True to escape the string, false to leave it unchanged.
1161 *
1162 * @return string The quoted input string.
1163 *
1164 * @since 11.1
1165 * @throws RuntimeException if the internal db property is not a valid object.
1166 */
1167 public function quote($text, $escape = true)
1168 {
1169 if (!($this->db instanceof FOFDatabaseDriver))
1170 {
1171 throw new RuntimeException('JLIB_DATABASE_ERROR_INVALID_DB_OBJECT');
1172 }
1173
1174 return $this->db->quote($text, $escape);
1175 }
1176
1177 /**
1178 * Wrap an SQL statement identifier name such as column, table or database names in quotes to prevent injection
1179 * risks and reserved word conflicts.
1180 *
1181 * This method is provided for use where the query object is passed to a function for modification.
1182 * If you have direct access to the database object, it is recommended you use the quoteName method directly.
1183 *
1184 * Note that 'qn' is an alias for this method as it is in FOFDatabaseDriver.
1185 *
1186 * Usage:
1187 * $query->quoteName('#__a');
1188 * $query->qn('#__a');
1189 *
1190 * @param mixed $name The identifier name to wrap in quotes, or an array of identifier names to wrap in quotes.
1191 * Each type supports dot-notation name.
1192 * @param mixed $as The AS query part associated to $name. It can be string or array, in latter case it has to be
1193 * same length of $name; if is null there will not be any AS part for string or array element.
1194 *
1195 * @return mixed The quote wrapped name, same type of $name.
1196 *
1197 * @since 11.1
1198 * @throws RuntimeException if the internal db property is not a valid object.
1199 */
1200 public function quoteName($name, $as = null)
1201 {
1202 if (!($this->db instanceof FOFDatabaseDriver))
1203 {
1204 throw new RuntimeException('JLIB_DATABASE_ERROR_INVALID_DB_OBJECT');
1205 }
1206
1207 return $this->db->quoteName($name, $as);
1208 }
1209
1210 /**
1211 * Add a RIGHT JOIN clause to the query.
1212 *
1213 * Usage:
1214 * $query->rightJoin('b ON b.id = a.id')->rightJoin('c ON c.id = b.id');
1215 *
1216 * @param string $condition The join condition.
1217 *
1218 * @return FOFDatabaseQuery Returns this object to allow chaining.
1219 *
1220 * @since 11.1
1221 */
1222 public function rightJoin($condition)
1223 {
1224 $this->join('RIGHT', $condition);
1225
1226 return $this;
1227 }
1228
1229 /**
1230 * Add a single column, or array of columns to the SELECT clause of the query.
1231 *
1232 * Note that you must not mix insert, update, delete and select method calls when building a query.
1233 * The select method can, however, be called multiple times in the same query.
1234 *
1235 * Usage:
1236 * $query->select('a.*')->select('b.id');
1237 * $query->select(array('a.*', 'b.id'));
1238 *
1239 * @param mixed $columns A string or an array of field names.
1240 *
1241 * @return FOFDatabaseQuery Returns this object to allow chaining.
1242 *
1243 * @since 11.1
1244 */
1245 public function select($columns)
1246 {
1247 $this->type = 'select';
1248
1249 if (is_null($this->select))
1250 {
1251 $this->select = new FOFDatabaseQueryElement('SELECT', $columns);
1252 }
1253 else
1254 {
1255 $this->select->append($columns);
1256 }
1257
1258 return $this;
1259 }
1260
1261 /**
1262 * Add a single condition string, or an array of strings to the SET clause of the query.
1263 *
1264 * Usage:
1265 * $query->set('a = 1')->set('b = 2');
1266 * $query->set(array('a = 1', 'b = 2');
1267 *
1268 * @param mixed $conditions A string or array of string conditions.
1269 * @param string $glue The glue by which to join the condition strings. Defaults to ,.
1270 * Note that the glue is set on first use and cannot be changed.
1271 *
1272 * @return FOFDatabaseQuery Returns this object to allow chaining.
1273 *
1274 * @since 11.1
1275 */
1276 public function set($conditions, $glue = ',')
1277 {
1278 if (is_null($this->set))
1279 {
1280 $glue = strtoupper($glue);
1281 $this->set = new FOFDatabaseQueryElement('SET', $conditions, "\n\t$glue ");
1282 }
1283 else
1284 {
1285 $this->set->append($conditions);
1286 }
1287
1288 return $this;
1289 }
1290
1291 /**
1292 * Allows a direct query to be provided to the database
1293 * driver's setQuery() method, but still allow queries
1294 * to have bounded variables.
1295 *
1296 * Usage:
1297 * $query->setQuery('select * from #__users');
1298 *
1299 * @param mixed $sql An SQL Query
1300 *
1301 * @return FOFDatabaseQuery Returns this object to allow chaining.
1302 *
1303 * @since 12.1
1304 */
1305 public function setQuery($sql)
1306 {
1307 $this->sql = $sql;
1308
1309 return $this;
1310 }
1311
1312 /**
1313 * Add a table name to the UPDATE clause of the query.
1314 *
1315 * Note that you must not mix insert, update, delete and select method calls when building a query.
1316 *
1317 * Usage:
1318 * $query->update('#__foo')->set(...);
1319 *
1320 * @param string $table A table to update.
1321 *
1322 * @return FOFDatabaseQuery Returns this object to allow chaining.
1323 *
1324 * @since 11.1
1325 */
1326 public function update($table)
1327 {
1328 $this->type = 'update';
1329 $this->update = new FOFDatabaseQueryElement('UPDATE', $table);
1330
1331 return $this;
1332 }
1333
1334 /**
1335 * Adds a tuple, or array of tuples that would be used as values for an INSERT INTO statement.
1336 *
1337 * Usage:
1338 * $query->values('1,2,3')->values('4,5,6');
1339 * $query->values(array('1,2,3', '4,5,6'));
1340 *
1341 * @param string $values A single tuple, or array of tuples.
1342 *
1343 * @return FOFDatabaseQuery Returns this object to allow chaining.
1344 *
1345 * @since 11.1
1346 */
1347 public function values($values)
1348 {
1349 if (is_null($this->values))
1350 {
1351 $this->values = new FOFDatabaseQueryElement('()', $values, '),(');
1352 }
1353 else
1354 {
1355 $this->values->append($values);
1356 }
1357
1358 return $this;
1359 }
1360
1361 /**
1362 * Add a single condition, or an array of conditions to the WHERE clause of the query.
1363 *
1364 * Usage:
1365 * $query->where('a = 1')->where('b = 2');
1366 * $query->where(array('a = 1', 'b = 2'));
1367 *
1368 * @param mixed $conditions A string or array of where conditions.
1369 * @param string $glue The glue by which to join the conditions. Defaults to AND.
1370 * Note that the glue is set on first use and cannot be changed.
1371 *
1372 * @return FOFDatabaseQuery Returns this object to allow chaining.
1373 *
1374 * @since 11.1
1375 */
1376 public function where($conditions, $glue = 'AND')
1377 {
1378 if (is_null($this->where))
1379 {
1380 $glue = strtoupper($glue);
1381 $this->where = new FOFDatabaseQueryElement('WHERE', $conditions, " $glue ");
1382 }
1383 else
1384 {
1385 $this->where->append($conditions);
1386 }
1387
1388 return $this;
1389 }
1390
1391 /**
1392 * Method to provide deep copy support to nested objects and
1393 * arrays when cloning.
1394 *
1395 * @return void
1396 *
1397 * @since 11.3
1398 */
1399 public function __clone()
1400 {
1401 foreach ($this as $k => $v)
1402 {
1403 if ($k === 'db')
1404 {
1405 continue;
1406 }
1407
1408 if (is_object($v) || is_array($v))
1409 {
1410 $this->{$k} = unserialize(serialize($v));
1411 }
1412 }
1413 }
1414
1415 /**
1416 * Add a query to UNION with the current query.
1417 * Multiple unions each require separate statements and create an array of unions.
1418 *
1419 * Usage (the $query base query MUST be a select query):
1420 * $query->union('SELECT name FROM #__foo')
1421 * $query->union('SELECT name FROM #__foo', true)
1422 * $query->union(array('SELECT name FROM #__foo','SELECT name FROM #__bar'))
1423 * $query->union($query2)->union($query3)
1424 * $query->union(array($query2, $query3))
1425 *
1426 * @param mixed $query The FOFDatabaseQuery object or string to union.
1427 * @param boolean $distinct True to only return distinct rows from the union.
1428 * @param string $glue The glue by which to join the conditions.
1429 *
1430 * @return mixed The FOFDatabaseQuery object on success or boolean false on failure.
1431 *
1432 * @see http://dev.mysql.com/doc/refman/5.0/en/union.html
1433 *
1434 * @since 12.1
1435 */
1436 public function union($query, $distinct = false, $glue = '')
1437 {
1438 // Set up the DISTINCT flag, the name with parentheses, and the glue.
1439 if ($distinct)
1440 {
1441 $name = 'UNION DISTINCT ()';
1442 $glue = ')' . PHP_EOL . 'UNION DISTINCT (';
1443 }
1444 else
1445 {
1446 $glue = ')' . PHP_EOL . 'UNION (';
1447 $name = 'UNION ()';
1448 }
1449
1450 // Get the FOFDatabaseQueryElement if it does not exist
1451 if (is_null($this->union))
1452 {
1453 $this->union = new FOFDatabaseQueryElement($name, $query, "$glue");
1454 }
1455 // Otherwise append the second UNION.
1456 else
1457 {
1458 $this->union->append($query);
1459 }
1460
1461 return $this;
1462 }
1463
1464 /**
1465 * Add a query to UNION DISTINCT with the current query. Simply a proxy to union with the DISTINCT keyword.
1466 *
1467 * Usage:
1468 * $query->unionDistinct('SELECT name FROM #__foo')
1469 *
1470 * @param mixed $query The FOFDatabaseQuery object or string to union.
1471 * @param string $glue The glue by which to join the conditions.
1472 *
1473 * @return mixed The FOFDatabaseQuery object on success or boolean false on failure.
1474 *
1475 * @see union
1476 *
1477 * @since 12.1
1478 */
1479 public function unionDistinct($query, $glue = '')
1480 {
1481 $distinct = true;
1482
1483 // Apply the distinct flag to the union.
1484 return $this->union($query, $distinct, $glue);
1485 }
1486
1487 /**
1488 * Find and replace sprintf-like tokens in a format string.
1489 * Each token takes one of the following forms:
1490 * %% - A literal percent character.
1491 * %[t] - Where [t] is a type specifier.
1492 * %[n]$[x] - Where [n] is an argument specifier and [t] is a type specifier.
1493 *
1494 * Types:
1495 * a - Numeric: Replacement text is coerced to a numeric type but not quoted or escaped.
1496 * e - Escape: Replacement text is passed to $this->escape().
1497 * E - Escape (extra): Replacement text is passed to $this->escape() with true as the second argument.
1498 * n - Name Quote: Replacement text is passed to $this->quoteName().
1499 * q - Quote: Replacement text is passed to $this->quote().
1500 * Q - Quote (no escape): Replacement text is passed to $this->quote() with false as the second argument.
1501 * r - Raw: Replacement text is used as-is. (Be careful)
1502 *
1503 * Date Types:
1504 * - Replacement text automatically quoted (use uppercase for Name Quote).
1505 * - Replacement text should be a string in date format or name of a date column.
1506 * y/Y - Year
1507 * m/M - Month
1508 * d/D - Day
1509 * h/H - Hour
1510 * i/I - Minute
1511 * s/S - Second
1512 *
1513 * Invariable Types:
1514 * - Takes no argument.
1515 * - Argument index not incremented.
1516 * t - Replacement text is the result of $this->currentTimestamp().
1517 * z - Replacement text is the result of $this->nullDate(false).
1518 * Z - Replacement text is the result of $this->nullDate(true).
1519 *
1520 * Usage:
1521 * $query->format('SELECT %1$n FROM %2$n WHERE %3$n = %4$a', 'foo', '#__foo', 'bar', 1);
1522 * Returns: SELECT `foo` FROM `#__foo` WHERE `bar` = 1
1523 *
1524 * Notes:
1525 * The argument specifier is optional but recommended for clarity.
1526 * The argument index used for unspecified tokens is incremented only when used.
1527 *
1528 * @param string $format The formatting string.
1529 *
1530 * @return string Returns a string produced according to the formatting string.
1531 *
1532 * @since 12.3
1533 */
1534 public function format($format)
1535 {
1536 $query = $this;
1537 $args = array_slice(func_get_args(), 1);
1538 array_unshift($args, null);
1539
1540 $i = 1;
1541 $func = function ($match) use ($query, $args, &$i)
1542 {
1543 if (isset($match[6]) && $match[6] == '%')
1544 {
1545 return '%';
1546 }
1547
1548 // No argument required, do not increment the argument index.
1549 switch ($match[5])
1550 {
1551 case 't':
1552 return $query->currentTimestamp();
1553 break;
1554
1555 case 'z':
1556 return $query->nullDate(false);
1557 break;
1558
1559 case 'Z':
1560 return $query->nullDate(true);
1561 break;
1562 }
1563
1564 // Increment the argument index only if argument specifier not provided.
1565 $index = is_numeric($match[4]) ? (int) $match[4] : $i++;
1566
1567 if (!$index || !isset($args[$index]))
1568 {
1569 // TODO - What to do? sprintf() throws a Warning in these cases.
1570 $replacement = '';
1571 }
1572 else
1573 {
1574 $replacement = $args[$index];
1575 }
1576
1577 switch ($match[5])
1578 {
1579 case 'a':
1580 return 0 + $replacement;
1581 break;
1582
1583 case 'e':
1584 return $query->escape($replacement);
1585 break;
1586
1587 case 'E':
1588 return $query->escape($replacement, true);
1589 break;
1590
1591 case 'n':
1592 return $query->quoteName($replacement);
1593 break;
1594
1595 case 'q':
1596 return $query->quote($replacement);
1597 break;
1598
1599 case 'Q':
1600 return $query->quote($replacement, false);
1601 break;
1602
1603 case 'r':
1604 return $replacement;
1605 break;
1606
1607 // Dates
1608 case 'y':
1609 return $query->year($query->quote($replacement));
1610 break;
1611
1612 case 'Y':
1613 return $query->year($query->quoteName($replacement));
1614 break;
1615
1616 case 'm':
1617 return $query->month($query->quote($replacement));
1618 break;
1619
1620 case 'M':
1621 return $query->month($query->quoteName($replacement));
1622 break;
1623
1624 case 'd':
1625 return $query->day($query->quote($replacement));
1626 break;
1627
1628 case 'D':
1629 return $query->day($query->quoteName($replacement));
1630 break;
1631
1632 case 'h':
1633 return $query->hour($query->quote($replacement));
1634 break;
1635
1636 case 'H':
1637 return $query->hour($query->quoteName($replacement));
1638 break;
1639
1640 case 'i':
1641 return $query->minute($query->quote($replacement));
1642 break;
1643
1644 case 'I':
1645 return $query->minute($query->quoteName($replacement));
1646 break;
1647
1648 case 's':
1649 return $query->second($query->quote($replacement));
1650 break;
1651
1652 case 'S':
1653 return $query->second($query->quoteName($replacement));
1654 break;
1655 }
1656
1657 return '';
1658 };
1659
1660 /**
1661 * Regexp to find an replace all tokens.
1662 * Matched fields:
1663 * 0: Full token
1664 * 1: Everything following '%'
1665 * 2: Everything following '%' unless '%'
1666 * 3: Argument specifier and '$'
1667 * 4: Argument specifier
1668 * 5: Type specifier
1669 * 6: '%' if full token is '%%'
1670 */
1671 return preg_replace_callback('#%(((([\d]+)\$)?([aeEnqQryYmMdDhHiIsStzZ]))|(%))#', $func, $format);
1672 }
1673
1674 /**
1675 * Add to the current date and time.
1676 * Usage:
1677 * $query->select($query->dateAdd());
1678 * Prefixing the interval with a - (negative sign) will cause subtraction to be used.
1679 * Note: Not all drivers support all units.
1680 *
1681 * @param datetime $date The date to add to. May be date or datetime
1682 * @param string $interval The string representation of the appropriate number of units
1683 * @param string $datePart The part of the date to perform the addition on
1684 *
1685 * @return string The string with the appropriate sql for addition of dates
1686 *
1687 * @link http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add
1688 * @since 13.1
1689 */
1690 public function dateAdd($date, $interval, $datePart)
1691 {
1692 return trim("DATE_ADD('" . $date . "', INTERVAL " . $interval . ' ' . $datePart . ')');
1693 }
1694
1695 /**
1696 * Add a query to UNION ALL with the current query.
1697 * Multiple unions each require separate statements and create an array of unions.
1698 *
1699 * Usage:
1700 * $query->union('SELECT name FROM #__foo')
1701 * $query->union(array('SELECT name FROM #__foo','SELECT name FROM #__bar'))
1702 *
1703 * @param mixed $query The FOFDatabaseQuery object or string to union.
1704 * @param boolean $distinct Not used - ignored.
1705 * @param string $glue Not used - ignored.
1706 *
1707 * @return mixed The FOFDatabaseQuery object on success or boolean false on failure.
1708 *
1709 * @see union
1710 *
1711 * @since 13.1
1712 */
1713 public function unionAll($query, $distinct = false, $glue = '')
1714 {
1715 $glue = ')' . PHP_EOL . 'UNION ALL (';
1716 $name = 'UNION ALL ()';
1717
1718 // Get the FOFDatabaseQueryElement if it does not exist
1719 if (is_null($this->unionAll))
1720 {
1721 $this->unionAll = new FOFDatabaseQueryElement($name, $query, "$glue");
1722 }
1723
1724 // Otherwise append the second UNION.
1725 else
1726 {
1727 $this->unionAll->append($query);
1728 }
1729
1730 return $this;
1731 }
1732 }
1733