[Summary view] [Print] [Text view]

   1  <?php
   2  /**
   3   * Part of the Joomla Framework Database Package
   4   *
   5   * @copyright  Copyright (C) 2005 - 2021 Open Source Matters, Inc. All rights reserved.
   6   * @license    GNU General Public License version 2 or later; see LICENSE
   7   */
   8  
   9  namespace Joomla\Database;
  10  
  11  use Joomla\Database\Exception\QueryTypeAlreadyDefinedException;
  12  use Joomla\Database\Query\LimitableInterface;
  13  use Joomla\Database\Query\PreparableInterface;
  14  use Joomla\Database\Exception\UnknownTypeException;
  15  
  16  /**
  17   * Joomla Framework Query Building Interface.
  18   *
  19   * @since  2.0.0
  20   */
  21  interface QueryInterface extends PreparableInterface, LimitableInterface
  22  {
  23      /**
  24       * Convert the query object to a string.
  25       *
  26       * @return  string
  27       *
  28       * @since   2.0.0
  29       */
  30  	public function __toString();
  31  
  32      /**
  33       * Add a single column, or array of columns to the CALL clause of the query.
  34       *
  35       * Usage:
  36       * $query->call('a.*')->call('b.id');
  37       * $query->call(array('a.*', 'b.id'));
  38       *
  39       * @param   array|string  $columns  A string or an array of field names.
  40       *
  41       * @return  $this
  42       *
  43       * @since   2.0.0
  44       * @throws  QueryTypeAlreadyDefinedException if the query type has already been defined
  45       */
  46  	public function call($columns);
  47  
  48      /**
  49       * Casts a value to a specified type.
  50       *
  51       * Ensure that the value is properly quoted before passing to the method.
  52       *
  53       * Usage:
  54       * $query->select($query->castAs('CHAR', 'a'));
  55       *
  56       * @param   string  $type    The type of string to cast as.
  57       * @param   string  $value   The value to cast as a char.
  58       * @param   string  $length  Optionally specify the length of the field (if the type supports it otherwise
  59       *                           ignored).
  60       *
  61       * @return  string  SQL statement to cast the value as a char type.
  62       *
  63       * @since   2.0.0
  64       * @throws  UnknownTypeException  When unsupported cast for a database driver
  65       */
  66  	public function castAs(string $type, string $value, ?string $length = null);
  67  
  68      /**
  69       * Gets the number of characters in a string.
  70       *
  71       * Note, use 'length' to find the number of bytes in a string.
  72       *
  73       * Usage:
  74       * $query->select($query->charLength('a'));
  75       *
  76       * @param   string       $field      A value.
  77       * @param   string|null  $operator   Comparison operator between charLength integer value and $condition
  78       * @param   string|null  $condition  Integer value to compare charLength with.
  79       *
  80       * @return  string  SQL statement to get the length of a character.
  81       *
  82       * @since   2.0.0
  83       */
  84  	public function charLength($field, $operator = null, $condition = null);
  85  
  86      /**
  87       * Clear data from the query or a specific clause of the query.
  88       *
  89       * @param   string  $clause  Optionally, the name of the clause to clear, or nothing to clear the whole query.
  90       *
  91       * @return  $this
  92       *
  93       * @since   2.0.0
  94       */
  95  	public function clear($clause = null);
  96  
  97      /**
  98       * Adds a column, or array of column names that would be used for an INSERT INTO statement.
  99       *
 100       * @param   array|string  $columns  A column name, or array of column names.
 101       *
 102       * @return  $this
 103       *
 104       * @since   2.0.0
 105       */
 106  	public function columns($columns);
 107  
 108      /**
 109       * Concatenates an array of column names or values.
 110       *
 111       * Usage:
 112       * $query->select($query->concatenate(array('a', 'b')));
 113       *
 114       * @param   string[]     $values     An array of values to concatenate.
 115       * @param   string|null  $separator  As separator to place between each value.
 116       *
 117       * @return  string  SQL statement representing the concatenated values.
 118       *
 119       * @since   2.0.0
 120       */
 121  	public function concatenate($values, $separator = null);
 122  
 123      /**
 124       * Gets the current date and time.
 125       *
 126       * Usage:
 127       * $query->where('published_up < '.$query->currentTimestamp());
 128       *
 129       * @return  string  SQL statement to get the current timestamp.
 130       *
 131       * @since   2.0.0
 132       */
 133  	public function currentTimestamp();
 134  
 135      /**
 136       * Add a table name to the DELETE clause of the query.
 137       *
 138       * Usage:
 139       * $query->delete('#__a')->where('id = 1');
 140       *
 141       * @param   string  $table  The name of the table to delete from.
 142       *
 143       * @return  $this
 144       *
 145       * @since   2.0.0
 146       * @throws  QueryTypeAlreadyDefinedException if the query type has already been defined
 147       */
 148  	public function delete($table = null);
 149  
 150      /**
 151       * Add a single column, or array of columns to the EXEC clause of the query.
 152       *
 153       * Usage:
 154       * $query->exec('a.*')->exec('b.id');
 155       * $query->exec(array('a.*', 'b.id'));
 156       *
 157       * @param   array|string  $columns  A string or an array of field names.
 158       *
 159       * @return  $this
 160       *
 161       * @since   2.0.0
 162       * @throws  QueryTypeAlreadyDefinedException if the query type has already been defined
 163       */
 164  	public function exec($columns);
 165  
 166      /**
 167       * Find a value in a varchar used like a set.
 168       *
 169       * Ensure that the value is an integer before passing to the method.
 170       *
 171       * Usage:
 172       * $query->findInSet((int) $parent->id, 'a.assigned_cat_ids')
 173       *
 174       * @param   string  $value  The value to search for.
 175       * @param   string  $set    The set of values.
 176       *
 177       * @return  string  A representation of the MySQL find_in_set() function for the driver.
 178       *
 179       * @since   2.0.0
 180       */
 181  	public function findInSet($value, $set);
 182  
 183      /**
 184       * Add a table to the FROM clause of the query.
 185       *
 186       * Usage:
 187       * $query->select('*')->from('#__a');
 188       * $query->select('*')->from($subquery->alias('a'));
 189       *
 190       * @param   string|QueryInterface  $table  The name of the table or a QueryInterface object (or a child of it) with alias set.
 191       *
 192       * @return  $this
 193       *
 194       * @since   2.0.0
 195       */
 196  	public function from($table);
 197  
 198      /**
 199       * Add alias for current query.
 200       *
 201       * Usage:
 202       * $query->select('*')->from('#__a')->alias('subquery');
 203       *
 204       * @param   string  $alias  Alias used for a JDatabaseQuery.
 205       *
 206       * @return  $this
 207       *
 208       * @since   2.0.0
 209       */
 210  	public function alias($alias);
 211  
 212      /**
 213       * Used to get a string to extract year from date column.
 214       *
 215       * Usage:
 216       * $query->select($query->year($query->quoteName('dateColumn')));
 217       *
 218       * @param   string  $date  Date column containing year to be extracted.
 219       *
 220       * @return  string  SQL statement to get the year from a date value.
 221       *
 222       * @since   2.0.0
 223       */
 224  	public function year($date);
 225  
 226      /**
 227       * Used to get a string to extract month from date column.
 228       *
 229       * Usage:
 230       * $query->select($query->month($query->quoteName('dateColumn')));
 231       *
 232       * @param   string  $date  Date column containing month to be extracted.
 233       *
 234       * @return  string  SQL statement to get the month from a date value.
 235       *
 236       * @since   2.0.0
 237       */
 238  	public function month($date);
 239  
 240      /**
 241       * Used to get a string to extract day from date column.
 242       *
 243       * Usage:
 244       * $query->select($query->day($query->quoteName('dateColumn')));
 245       *
 246       * @param   string  $date  Date column containing day to be extracted.
 247       *
 248       * @return  string  SQL statement to get the day from a date value.
 249       *
 250       * @since   2.0.0
 251       */
 252  	public function day($date);
 253  
 254      /**
 255       * Used to get a string to extract hour from date column.
 256       *
 257       * Usage:
 258       * $query->select($query->hour($query->quoteName('dateColumn')));
 259       *
 260       * @param   string  $date  Date column containing hour to be extracted.
 261       *
 262       * @return  string  SQL statement to get the hour from a date/time value.
 263       *
 264       * @since   2.0.0
 265       */
 266  	public function hour($date);
 267  
 268      /**
 269       * Used to get a string to extract minute from date column.
 270       *
 271       * Usage:
 272       * $query->select($query->minute($query->quoteName('dateColumn')));
 273       *
 274       * @param   string  $date  Date column containing minute to be extracted.
 275       *
 276       * @return  string  SQL statement to get the minute from a date/time value.
 277       *
 278       * @since   2.0.0
 279       */
 280  	public function minute($date);
 281  
 282      /**
 283       * Used to get a string to extract seconds from date column.
 284       *
 285       * Usage:
 286       * $query->select($query->second($query->quoteName('dateColumn')));
 287       *
 288       * @param   string  $date  Date column containing second to be extracted.
 289       *
 290       * @return  string  SQL statement to get the second from a date/time value.
 291       *
 292       * @since   2.0.0
 293       */
 294  	public function second($date);
 295  
 296      /**
 297       * Add a grouping column to the GROUP clause of the query.
 298       *
 299       * Usage:
 300       * $query->group('id');
 301       *
 302       * @param   array|string  $columns  A string or array of ordering columns.
 303       *
 304       * @return  $this
 305       *
 306       * @since   2.0.0
 307       */
 308  	public function group($columns);
 309  
 310      /**
 311       * Aggregate function to get input values concatenated into a string, separated by delimiter
 312       *
 313       * Usage:
 314       * $query->groupConcat('id', ',');
 315       *
 316       * @param   string  $expression  The expression to apply concatenation to, this may be a column name or complex SQL statement.
 317       * @param   string  $separator   The delimiter of each concatenated value
 318       *
 319       * @return  string  Input values concatenated into a string, separated by delimiter
 320       *
 321       * @since   2.0.0
 322       */
 323  	public function groupConcat($expression, $separator = ',');
 324  
 325      /**
 326       * A conditions to the HAVING clause of the query.
 327       *
 328       * Usage:
 329       * $query->group('id')->having('COUNT(id) > 5');
 330       *
 331       * @param   array|string  $conditions  A string or array of columns.
 332       * @param   string        $glue        The glue by which to join the conditions. Defaults to AND.
 333       *
 334       * @return  $this
 335       *
 336       * @since   2.0.0
 337       */
 338  	public function having($conditions, $glue = 'AND');
 339  
 340      /**
 341       * Add a table name to the INSERT clause of the query.
 342       *
 343       * Usage:
 344       * $query->insert('#__a')->set('id = 1');
 345       * $query->insert('#__a')->columns('id, title')->values('1,2')->values('3,4');
 346       * $query->insert('#__a')->columns('id, title')->values(array('1,2', '3,4'));
 347       *
 348       * @param   string   $table           The name of the table to insert data into.
 349       * @param   boolean  $incrementField  The name of the field to auto increment.
 350       *
 351       * @return  $this
 352       *
 353       * @since   2.0.0
 354       * @throws  QueryTypeAlreadyDefinedException if the query type has already been defined
 355       */
 356  	public function insert($table, $incrementField = false);
 357  
 358      /**
 359       * Add a JOIN clause to the query.
 360       *
 361       * Usage:
 362       * $query->join('INNER', 'b', 'b.id = a.id);
 363       *
 364       * @param   string  $type       The type of join. This string is prepended to the JOIN keyword.
 365       * @param   string  $table      The name of table.
 366       * @param   string  $condition  The join condition.
 367       *
 368       * @return  $this
 369       *
 370       * @since   2.0.0
 371       */
 372  	public function join($type, $table, $condition = null);
 373  
 374      /**
 375       * Get the length of a string in bytes.
 376       *
 377       * Note, use 'charLength' to find the number of characters in a string.
 378       *
 379       * Usage:
 380       * query->where($query->length('a').' > 3');
 381       *
 382       * @param   string  $value  The string to measure.
 383       *
 384       * @return  integer
 385       *
 386       * @since   2.0.0
 387       */
 388  	public function length($value);
 389  
 390      /**
 391       * Get the null or zero representation of a timestamp for the database driver.
 392       *
 393       * This method is provided for use where the query object is passed to a function for modification.
 394       * If you have direct access to the database object, it is recommended you use the nullDate method directly.
 395       *
 396       * Usage:
 397       * $query->where('modified_date <> '.$query->nullDate());
 398       *
 399       * @param   boolean  $quoted  Optionally wraps the null date in database quotes (true by default).
 400       *
 401       * @return  string  Null or zero representation of a timestamp.
 402       *
 403       * @since   2.0.0
 404       * @throws  \RuntimeException
 405       */
 406  	public function nullDate($quoted = true);
 407  
 408      /**
 409       * Generate a SQL statement to check if column represents a zero or null datetime.
 410       *
 411       * Usage:
 412       * $query->where($query->isNullDatetime('modified_date'));
 413       *
 414       * @param   string  $column  A column name.
 415       *
 416       * @return  string
 417       *
 418       * @since   2.0.0
 419       */
 420  	public function isNullDatetime($column);
 421  
 422      /**
 423       * Add an ordering column to the ORDER clause of the query.
 424       *
 425       * Usage:
 426       * $query->order('foo')->order('bar');
 427       * $query->order(array('foo','bar'));
 428       *
 429       * @param   array|string  $columns  A string or array of ordering columns.
 430       *
 431       * @return  $this
 432       *
 433       * @since   2.0.0
 434       */
 435  	public function order($columns);
 436  
 437      /**
 438       * Get the function to return a random floating-point value
 439       *
 440       * Usage:
 441       * $query->rand();
 442       *
 443       * @return  string
 444       *
 445       * @since   2.0.0
 446       */
 447  	public function rand();
 448  
 449      /**
 450       * Get the regular expression operator
 451       *
 452       * Usage:
 453       * $query->where('field ' . $query->regexp($search));
 454       *
 455       * @param   string  $value  The regex pattern.
 456       *
 457       * @return  string
 458       *
 459       * @since   2.0.0
 460       */
 461  	public function regexp($value);
 462  
 463      /**
 464       * Add a single column, or array of columns to the SELECT clause of the query.
 465       *
 466       * Usage:
 467       * $query->select('a.*')->select('b.id');
 468       * $query->select(array('a.*', 'b.id'));
 469       *
 470       * @param   array|string  $columns  A string or an array of field names.
 471       *
 472       * @return  $this
 473       *
 474       * @since   2.0.0
 475       * @throws  QueryTypeAlreadyDefinedException if the query type has already been defined
 476       */
 477  	public function select($columns);
 478  
 479      /**
 480       * Return the number of the current row.
 481       *
 482       * Usage:
 483       * $query->select('id');
 484       * $query->selectRowNumber('ordering,publish_up DESC', 'new_ordering');
 485       * $query->from('#__content');
 486       *
 487       * @param   string  $orderBy           An expression of ordering for window function.
 488       * @param   string  $orderColumnAlias  An alias for new ordering column.
 489       *
 490       * @return  $this
 491       *
 492       * @since   2.0.0
 493       * @throws  \RuntimeException
 494       */
 495  	public function selectRowNumber($orderBy, $orderColumnAlias);
 496  
 497      /**
 498       * Add a single condition string, or an array of strings to the SET clause of the query.
 499       *
 500       * Usage:
 501       * $query->set('a = 1')->set('b = 2');
 502       * $query->set(array('a = 1', 'b = 2');
 503       *
 504       * @param   array|string  $conditions  A string or array of string conditions.
 505       * @param   string        $glue        The glue by which to join the condition strings. Defaults to `,`.
 506       *                                     Note that the glue is set on first use and cannot be changed.
 507       *
 508       * @return  $this
 509       *
 510       * @since   2.0.0
 511       */
 512  	public function set($conditions, $glue = ',');
 513  
 514      /**
 515       * Add a table name to the UPDATE clause of the query.
 516       *
 517       * Usage:
 518       * $query->update('#__foo')->set(...);
 519       *
 520       * @param   string  $table  A table to update.
 521       *
 522       * @return  $this
 523       *
 524       * @since   2.0.0
 525       * @throws  QueryTypeAlreadyDefinedException if the query type has already been defined
 526       */
 527  	public function update($table);
 528  
 529      /**
 530       * Adds a tuple, or array of tuples that would be used as values for an INSERT INTO statement.
 531       *
 532       * Usage:
 533       * $query->values('1,2,3')->values('4,5,6');
 534       * $query->values(array('1,2,3', '4,5,6'));
 535       *
 536       * @param   array|string  $values  A single tuple, or array of tuples.
 537       *
 538       * @return  $this
 539       *
 540       * @since   2.0.0
 541       */
 542  	public function values($values);
 543  
 544      /**
 545       * Add a single condition, or an array of conditions to the WHERE clause of the query.
 546       *
 547       * Usage:
 548       * $query->where('a = 1')->where('b = 2');
 549       * $query->where(array('a = 1', 'b = 2'));
 550       *
 551       * @param   array|string  $conditions  A string or array of where conditions.
 552       * @param   string        $glue        The glue by which to join the conditions. Defaults to AND.
 553       *                                     Note that the glue is set on first use and cannot be changed.
 554       *
 555       * @return  $this
 556       *
 557       * @since   2.0.0
 558       */
 559  	public function where($conditions, $glue = 'AND');
 560  
 561      /**
 562       * Add a WHERE IN statement to the query.
 563       *
 564       * Note that all values must be the same data type.
 565       *
 566       * Usage
 567       * $query->whereIn('id', [1, 2, 3]);
 568       *
 569       * @param   string        $keyName    Key name for the where clause
 570       * @param   array         $keyValues  Array of values to be matched
 571       * @param   array|string  $dataType   Constant corresponding to a SQL datatype. It can be an array, in this case it
 572       *                                    has to be same length of $keyValues
 573       *
 574       * @return  $this
 575       *
 576       * @since 2.0.0
 577       */
 578  	public function whereIn(string $keyName, array $keyValues, $dataType = ParameterType::INTEGER);
 579  
 580      /**
 581       * Add a WHERE NOT IN statement to the query.
 582       *
 583       * Note that all values must be the same data type.
 584       *
 585       * Usage
 586       * $query->whereNotIn('id', [1, 2, 3]);
 587       *
 588       * @param   string        $keyName    Key name for the where clause
 589       * @param   array         $keyValues  Array of values to be matched
 590       * @param   array|string  $dataType   Constant corresponding to a SQL datatype. It can be an array, in this case it
 591       *                                    has to be same length of $keyValues
 592       *
 593       * @return  $this
 594       *
 595       * @since 2.0.0
 596       */
 597  	public function whereNotIn(string $keyName, array $keyValues, $dataType = ParameterType::INTEGER);
 598  
 599      /**
 600       * Extend the WHERE clause with a single condition or an array of conditions, with a potentially different logical operator from the one in the
 601       * current WHERE clause.
 602       *
 603       * Usage:
 604       * $query->where(array('a = 1', 'b = 2'))->extendWhere('XOR', array('c = 3', 'd = 4'));
 605       * will produce: WHERE ((a = 1 AND b = 2) XOR (c = 3 AND d = 4)
 606       *
 607       * @param   string  $outerGlue   The glue by which to join the conditions to the current WHERE conditions.
 608       * @param   mixed   $conditions  A string or array of WHERE conditions.
 609       * @param   string  $innerGlue   The glue by which to join the conditions. Defaults to AND.
 610       *
 611       * @return  $this
 612       *
 613       * @since   2.0.0
 614       */
 615  	public function extendWhere($outerGlue, $conditions, $innerGlue = 'AND');
 616  
 617      /**
 618       * Binds an array of values and returns an array of prepared parameter names.
 619       *
 620       * Note that all values must be the same data type.
 621       *
 622       * Usage:
 623       * $query->whereIn('column in (' . implode(',', $query->bindArray($keyValues, $dataType)) . ')');
 624       *
 625       * @param   array         $values    Values to bind
 626       * @param   array|string  $dataType  Constant corresponding to a SQL datatype. It can be an array, in this case it
 627       *                                   has to be same length of $key
 628       *
 629       * @return  array   An array with parameter names
 630       *
 631       * @since 2.0.0
 632       */
 633  	public function bindArray(array $values, $dataType = ParameterType::INTEGER);
 634  
 635      /**
 636       * Add a query to UNION with the current query.
 637       *
 638       * Usage:
 639       * $query->union('SELECT name FROM  #__foo')
 640       * $query->union('SELECT name FROM  #__foo', true)
 641       *
 642       * @param   DatabaseQuery|string  $query     The DatabaseQuery object or string to union.
 643       * @param   boolean               $distinct  True to only return distinct rows from the union.
 644       *
 645       * @return  $this
 646       *
 647       * @since   1.0
 648       */
 649  	public function union($query, $distinct = true);
 650  
 651      /**
 652       * Add a query to UNION ALL with the current query.
 653       *
 654       * Usage:
 655       * $query->unionAll('SELECT name FROM  #__foo')
 656       *
 657       * @param   DatabaseQuery|string  $query  The DatabaseQuery object or string to union.
 658       *
 659       * @return  $this
 660       *
 661       * @see     union
 662       * @since   1.5.0
 663       */
 664  	public function unionAll($query);
 665  
 666      /**
 667       * Set a single query to the query set.
 668       * On this type of DatabaseQuery you can use union(), unionAll(), order() and setLimit()
 669       *
 670       * Usage:
 671       * $query->querySet($query2->select('name')->from('#__foo')->order('id DESC')->setLimit(1))
 672       *       ->unionAll($query3->select('name')->from('#__foo')->order('id')->setLimit(1))
 673       *       ->order('name')
 674       *       ->setLimit(1)
 675       *
 676       * @param   DatabaseQuery|string  $query  The DatabaseQuery object or string.
 677       *
 678       * @return  $this
 679       *
 680       * @since   2.0.0
 681       */
 682  	public function querySet($query);
 683  
 684      /**
 685       * Create a DatabaseQuery object of type querySet from current query.
 686       *
 687       * Usage:
 688       * $query->select('name')->from('#__foo')->order('id DESC')->setLimit(1)
 689       *       ->toQuerySet()
 690       *       ->unionAll($query2->select('name')->from('#__foo')->order('id')->setLimit(1))
 691       *       ->order('name')
 692       *       ->setLimit(1)
 693       *
 694       * @return  DatabaseQuery  A new object of the DatabaseQuery.
 695       *
 696       * @since   2.0.0
 697       */
 698  	public function toQuerySet();
 699  }