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