| [ Index ] |
PHP Cross Reference of Joomla 4.2.2 documentation |
[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\Exception\UnknownTypeException; 13 14 /** 15 * Joomla Framework Query Building Class. 16 * 17 * @since 1.0 18 * 19 * @property-read array $bounded Holds key / value pair of bound objects. 20 * @property-read array $parameterMapping Mapping array for parameter types. 21 * @property-read DatabaseInterface $db The database driver. 22 * @property-read string $sql The SQL query (if a direct query string was provided). 23 * @property-read string $type The query type. 24 * @property-read string|null $alias The query alias. 25 * @property-read Query\QueryElement $element The query element for a generic query (type = null). 26 * @property-read Query\QueryElement $select The select element. 27 * @property-read Query\QueryElement $delete The delete element. 28 * @property-read Query\QueryElement $update The update element. 29 * @property-read Query\QueryElement $insert The insert element. 30 * @property-read Query\QueryElement $from The from element. 31 * @property-read Query\QueryElement[]|null $join The join elements. 32 * @property-read Query\QueryElement $set The set element. 33 * @property-read Query\QueryElement $where The where element. 34 * @property-read Query\QueryElement $group The group element. 35 * @property-read Query\QueryElement $having The having element. 36 * @property-read Query\QueryElement $columns The column list for an INSERT statement. 37 * @property-read Query\QueryElement $values The values list for an INSERT statement. 38 * @property-read Query\QueryElement $order The order element. 39 * @property-read boolean $autoIncrementField The auto increment insert field element. 40 * @property-read Query\QueryElement $call The call element. 41 * @property-read Query\QueryElement $exec The exec element. 42 * @property-read Query\QueryElement[]|null $merge The list of query elements. 43 * @property-read DatabaseQuery|null $querySet The query object. 44 * @property-read array|null $selectRowNumber Details of window function. 45 * @property-read string[] $nullDatetimeList The list of zero or null representation of a datetime. 46 * @property-read integer|null $offset The offset for the result set. 47 * @property-read integer|null $limit The limit for the result set. 48 * @property-read integer $preparedIndex An internal index for the bindArray function for unique prepared parameters. 49 */ 50 abstract class DatabaseQuery implements QueryInterface 51 { 52 /** 53 * Holds key / value pair of bound objects. 54 * 55 * @var array 56 * @since 2.0.0 57 */ 58 protected $bounded = []; 59 60 /** 61 * Mapping array for parameter types. 62 * 63 * @var array 64 * @since 2.0.0 65 */ 66 protected $parameterMapping = [ 67 ParameterType::BOOLEAN => ParameterType::BOOLEAN, 68 ParameterType::INTEGER => ParameterType::INTEGER, 69 ParameterType::LARGE_OBJECT => ParameterType::LARGE_OBJECT, 70 ParameterType::NULL => ParameterType::NULL, 71 ParameterType::STRING => ParameterType::STRING, 72 ]; 73 74 /** 75 * The database driver. 76 * 77 * @var DatabaseInterface 78 * @since 1.0 79 */ 80 protected $db; 81 82 /** 83 * The SQL query (if a direct query string was provided). 84 * 85 * @var string 86 * @since 1.0 87 */ 88 protected $sql; 89 90 /** 91 * The query type. 92 * 93 * @var string|null 94 * @since 1.0 95 */ 96 protected $type = ''; 97 98 /** 99 * The query alias. 100 * 101 * @var string|null 102 * @since 2.0.0 103 */ 104 protected $alias = null; 105 106 /** 107 * The query element for a generic query (type = null). 108 * 109 * @var Query\QueryElement 110 * @since 1.0 111 */ 112 protected $element; 113 114 /** 115 * The select element. 116 * 117 * @var Query\QueryElement 118 * @since 1.0 119 */ 120 protected $select; 121 122 /** 123 * The delete element. 124 * 125 * @var Query\QueryElement 126 * @since 1.0 127 */ 128 protected $delete; 129 130 /** 131 * The update element. 132 * 133 * @var Query\QueryElement 134 * @since 1.0 135 */ 136 protected $update; 137 138 /** 139 * The insert element. 140 * 141 * @var Query\QueryElement 142 * @since 1.0 143 */ 144 protected $insert; 145 146 /** 147 * The from element. 148 * 149 * @var Query\QueryElement 150 * @since 1.0 151 */ 152 protected $from; 153 154 /** 155 * The join elements. 156 * 157 * @var Query\QueryElement[] 158 * @since 1.0 159 */ 160 protected $join; 161 162 /** 163 * The set element. 164 * 165 * @var Query\QueryElement 166 * @since 1.0 167 */ 168 protected $set; 169 170 /** 171 * The where element. 172 * 173 * @var Query\QueryElement 174 * @since 1.0 175 */ 176 protected $where; 177 178 /** 179 * The group by element. 180 * 181 * @var Query\QueryElement 182 * @since 1.0 183 */ 184 protected $group; 185 186 /** 187 * The having element. 188 * 189 * @var Query\QueryElement 190 * @since 1.0 191 */ 192 protected $having; 193 194 /** 195 * The column list for an INSERT statement. 196 * 197 * @var Query\QueryElement 198 * @since 1.0 199 */ 200 protected $columns; 201 202 /** 203 * The values list for an INSERT statement. 204 * 205 * @var Query\QueryElement 206 * @since 1.0 207 */ 208 protected $values; 209 210 /** 211 * The order element. 212 * 213 * @var Query\QueryElement 214 * @since 1.0 215 */ 216 protected $order; 217 218 /** 219 * The auto increment insert field element. 220 * 221 * @var boolean 222 * @since 1.0 223 */ 224 protected $autoIncrementField = false; 225 226 /** 227 * The call element. 228 * 229 * @var Query\QueryElement 230 * @since 1.0 231 */ 232 protected $call; 233 234 /** 235 * The exec element. 236 * 237 * @var Query\QueryElement 238 * @since 1.0 239 */ 240 protected $exec; 241 242 /** 243 * The list of query elements, which may include UNION, UNION ALL, EXCEPT and INTERSECT. 244 * 245 * @var Query\QueryElement[] 246 * @since 2.0.0 247 */ 248 protected $merge; 249 250 /** 251 * The query object. 252 * 253 * @var Query\DatabaseQuery 254 * @since 2.0.0 255 */ 256 protected $querySet; 257 258 /** 259 * Details of window function. 260 * 261 * @var array|null 262 * @since 2.0.0 263 */ 264 protected $selectRowNumber; 265 266 /** 267 * The list of zero or null representation of a datetime. 268 * 269 * @var string[] 270 * @since 2.0.0 271 */ 272 protected $nullDatetimeList = []; 273 274 /** 275 * The offset for the result set. 276 * 277 * @var integer|null 278 * @since 2.0.0 279 */ 280 protected $offset; 281 282 /** 283 * The limit for the result set. 284 * 285 * @var integer|null 286 * @since 2.0.0 287 */ 288 protected $limit; 289 290 /** 291 * An internal index for the bindArray function for unique prepared parameters. 292 * 293 * @var integer 294 * @since 2.0.0 295 */ 296 protected $preparedIndex = 0; 297 298 /** 299 * Class constructor. 300 * 301 * @param DatabaseInterface $db The database driver. 302 * 303 * @since 1.0 304 */ 305 public function __construct(DatabaseInterface $db = null) 306 { 307 $this->db = $db; 308 } 309 310 /** 311 * Magic function to convert the query to a string. 312 * 313 * @return string The completed query. 314 * 315 * @since 1.0 316 */ 317 public function __toString() 318 { 319 if ($this->sql) 320 { 321 return $this->processLimit($this->sql, $this->limit, $this->offset); 322 } 323 324 $query = ''; 325 326 switch ($this->type) 327 { 328 case 'element': 329 $query .= (string) $this->element; 330 331 break; 332 333 case 'select': 334 $query .= (string) $this->select; 335 $query .= (string) $this->from; 336 337 if ($this->join) 338 { 339 // Special case for joins 340 foreach ($this->join as $join) 341 { 342 $query .= (string) $join; 343 } 344 } 345 346 if ($this->where) 347 { 348 $query .= (string) $this->where; 349 } 350 351 if ($this->selectRowNumber === null) 352 { 353 if ($this->group) 354 { 355 $query .= (string) $this->group; 356 } 357 358 if ($this->having) 359 { 360 $query .= (string) $this->having; 361 } 362 363 if ($this->merge) 364 { 365 // Special case for merge 366 foreach ($this->merge as $element) 367 { 368 $query .= (string) $element; 369 } 370 } 371 } 372 373 if ($this->order) 374 { 375 $query .= (string) $this->order; 376 } 377 378 break; 379 380 case 'querySet': 381 $query = $this->querySet; 382 383 if ($query->order || ($query->limit || $query->offset)) 384 { 385 // If ORDER BY or LIMIT statement exist then parentheses is required for the first query 386 $query = "($query)"; 387 } 388 389 if ($this->merge) 390 { 391 // Special case for merge 392 foreach ($this->merge as $element) 393 { 394 $query .= (string) $element; 395 } 396 } 397 398 if ($this->order) 399 { 400 $query .= (string) $this->order; 401 } 402 403 break; 404 405 case 'delete': 406 $query .= (string) $this->delete; 407 $query .= (string) $this->from; 408 409 if ($this->join) 410 { 411 // Special case for joins 412 foreach ($this->join as $join) 413 { 414 $query .= (string) $join; 415 } 416 } 417 418 if ($this->where) 419 { 420 $query .= (string) $this->where; 421 } 422 423 break; 424 425 case 'update': 426 $query .= (string) $this->update; 427 428 if ($this->join) 429 { 430 // Special case for joins 431 foreach ($this->join as $join) 432 { 433 $query .= (string) $join; 434 } 435 } 436 437 $query .= (string) $this->set; 438 439 if ($this->where) 440 { 441 $query .= (string) $this->where; 442 } 443 444 break; 445 446 case 'insert': 447 $query .= (string) $this->insert; 448 449 // Set method 450 if ($this->set) 451 { 452 $query .= (string) $this->set; 453 } 454 elseif ($this->values) 455 { 456 // Columns-Values method 457 if ($this->columns) 458 { 459 $query .= (string) $this->columns; 460 } 461 462 $elements = $this->values->getElements(); 463 464 if (!($elements[0] instanceof $this)) 465 { 466 $query .= ' VALUES '; 467 } 468 469 $query .= (string) $this->values; 470 } 471 472 break; 473 474 case 'call': 475 $query .= (string) $this->call; 476 477 break; 478 479 case 'exec': 480 $query .= (string) $this->exec; 481 482 break; 483 } 484 485 $query = $this->processLimit($query, $this->limit, $this->offset); 486 487 if ($this->type === 'select' && $this->alias !== null) 488 { 489 $query = '(' . $query . ') AS ' . $this->alias; 490 } 491 492 return $query; 493 } 494 495 /** 496 * Magic function to get protected variable value 497 * 498 * @param string $name The name of the variable. 499 * 500 * @return mixed 501 * 502 * @since 1.0 503 */ 504 public function __get($name) 505 { 506 if (property_exists($this, $name)) 507 { 508 return $this->$name; 509 } 510 511 $trace = debug_backtrace(); 512 trigger_error( 513 'Undefined property via __get(): ' . $name . ' in ' . $trace[0]['file'] . ' on line ' . $trace[0]['line'], 514 E_USER_NOTICE 515 ); 516 } 517 518 /** 519 * Add a single column, or array of columns to the CALL clause of the query. 520 * 521 * Usage: 522 * $query->call('a.*')->call('b.id'); 523 * $query->call(array('a.*', 'b.id')); 524 * 525 * @param mixed $columns A string or an array of field names. 526 * 527 * @return $this 528 * 529 * @since 1.0 530 * @throws QueryTypeAlreadyDefinedException if the query type has already been defined 531 */ 532 public function call($columns) 533 { 534 if ($this->type !== null && $this->type !== '' && $this->type !== 'call') 535 { 536 throw new QueryTypeAlreadyDefinedException( 537 \sprintf( 538 'Cannot set the query type to "call" as the query type is already set to "%s".' 539 . ' You should either call the `clear()` method to reset the type or create a new query object.', 540 $this->type 541 ) 542 ); 543 } 544 545 $this->type = 'call'; 546 547 if ($this->call === null) 548 { 549 $this->call = new Query\QueryElement('CALL', $columns); 550 } 551 else 552 { 553 $this->call->append($columns); 554 } 555 556 return $this; 557 } 558 559 /** 560 * Casts a value to a char. 561 * 562 * Ensure that the value is properly quoted before passing to the method. 563 * 564 * Usage: 565 * $query->select($query->castAs('CHAR', 'a')); 566 * 567 * @param string $type The type of string to cast as. 568 * @param string $value The value to cast as a char. 569 * @param string $length Optionally specify the length of the field (if the type supports it otherwise 570 * ignored). 571 * 572 * @return string SQL statement to cast the value as a char type. 573 * 574 * @since 1.0 575 */ 576 public function castAs(string $type, string $value, ?string $length = null) 577 { 578 switch (strtoupper($type)) 579 { 580 case 'CHAR': 581 return $value; 582 583 default: 584 throw new UnknownTypeException( 585 sprintf( 586 'Type %s was not recognised by the database driver as valid for casting', 587 $type 588 ) 589 ); 590 } 591 } 592 593 /** 594 * Casts a value to a char. 595 * 596 * Ensure that the value is properly quoted before passing to the method. 597 * 598 * Usage: 599 * $query->select($query->castAsChar('a')); 600 * 601 * @param string $value The value to cast as a char. 602 * 603 * @return string SQL statement to cast the value as a char type. 604 * 605 * @since 1.0 606 * @deprecated 3.0 Use $query->castAs('CHAR', $value) 607 */ 608 public function castAsChar($value) 609 { 610 return $this->castAs('CHAR', $value); 611 } 612 613 /** 614 * Gets the number of characters in a string. 615 * 616 * Note, use 'length' to find the number of bytes in a string. 617 * 618 * Usage: 619 * $query->select($query->charLength('a')); 620 * 621 * @param string $field A value. 622 * @param string|null $operator Comparison operator between charLength integer value and $condition 623 * @param string|null $condition Integer value to compare charLength with. 624 * 625 * @return string The required char length call. 626 * 627 * @since 1.0 628 */ 629 public function charLength($field, $operator = null, $condition = null) 630 { 631 $statement = 'CHAR_LENGTH(' . $field . ')'; 632 633 if ($operator !== null && $condition !== null) 634 { 635 $statement .= ' ' . $operator . ' ' . $condition; 636 } 637 638 return $statement; 639 } 640 641 /** 642 * Clear data from the query or a specific clause of the query. 643 * 644 * @param string $clause Optionally, the name of the clause to clear, or nothing to clear the whole query. 645 * 646 * @return $this 647 * 648 * @since 1.0 649 */ 650 public function clear($clause = null) 651 { 652 $this->sql = null; 653 654 switch ($clause) 655 { 656 case 'alias': 657 $this->alias = null; 658 break; 659 660 case 'select': 661 $this->select = null; 662 $this->type = null; 663 $this->selectRowNumber = null; 664 665 break; 666 667 case 'delete': 668 $this->delete = null; 669 $this->type = null; 670 671 break; 672 673 case 'update': 674 $this->update = null; 675 $this->type = null; 676 677 break; 678 679 case 'insert': 680 $this->insert = null; 681 $this->type = null; 682 $this->autoIncrementField = null; 683 684 break; 685 686 case 'querySet': 687 $this->querySet = null; 688 $this->type = null; 689 690 break; 691 692 case 'from': 693 $this->from = null; 694 695 break; 696 697 case 'join': 698 $this->join = null; 699 700 break; 701 702 case 'set': 703 $this->set = null; 704 705 break; 706 707 case 'where': 708 $this->where = null; 709 710 break; 711 712 case 'group': 713 $this->group = null; 714 715 break; 716 717 case 'having': 718 $this->having = null; 719 720 break; 721 722 case 'merge': 723 $this->merge = null; 724 725 break; 726 727 case 'order': 728 $this->order = null; 729 730 break; 731 732 case 'columns': 733 $this->columns = null; 734 735 break; 736 737 case 'values': 738 $this->values = null; 739 740 break; 741 742 case 'exec': 743 $this->exec = null; 744 $this->type = null; 745 746 break; 747 748 case 'call': 749 $this->call = null; 750 $this->type = null; 751 752 break; 753 754 case 'limit': 755 $this->offset = 0; 756 $this->limit = 0; 757 758 break; 759 760 case 'offset': 761 $this->offset = 0; 762 763 break; 764 765 case 'bounded': 766 $this->bounded = []; 767 768 break; 769 770 default: 771 $this->type = null; 772 $this->alias = null; 773 $this->bounded = []; 774 $this->select = null; 775 $this->selectRowNumber = null; 776 $this->delete = null; 777 $this->update = null; 778 $this->insert = null; 779 $this->querySet = null; 780 $this->from = null; 781 $this->join = null; 782 $this->set = null; 783 $this->where = null; 784 $this->group = null; 785 $this->having = null; 786 $this->merge = null; 787 $this->order = null; 788 $this->columns = null; 789 $this->values = null; 790 $this->autoIncrementField = null; 791 $this->exec = null; 792 $this->call = null; 793 $this->offset = 0; 794 $this->limit = 0; 795 796 break; 797 } 798 799 return $this; 800 } 801 802 /** 803 * Adds a column, or array of column names that would be used for an INSERT INTO statement. 804 * 805 * @param array|string $columns A column name, or array of column names. 806 * 807 * @return $this 808 * 809 * @since 1.0 810 */ 811 public function columns($columns) 812 { 813 if ($this->columns === null) 814 { 815 $this->columns = new Query\QueryElement('()', $columns); 816 } 817 else 818 { 819 $this->columns->append($columns); 820 } 821 822 return $this; 823 } 824 825 /** 826 * Concatenates an array of column names or values. 827 * 828 * Usage: 829 * $query->select($query->concatenate(array('a', 'b'))); 830 * 831 * @param string[] $values An array of values to concatenate. 832 * @param string|null $separator As separator to place between each value. 833 * 834 * @return string The concatenated values. 835 * 836 * @since 1.0 837 */ 838 public function concatenate($values, $separator = null) 839 { 840 if ($separator !== null) 841 { 842 return 'CONCATENATE(' . implode(' || ' . $this->quote($separator) . ' || ', $values) . ')'; 843 } 844 845 return 'CONCATENATE(' . implode(' || ', $values) . ')'; 846 } 847 848 /** 849 * Gets the current date and time. 850 * 851 * Usage: 852 * $query->where('published_up < '.$query->currentTimestamp()); 853 * 854 * @return string 855 * 856 * @since 1.0 857 */ 858 public function currentTimestamp() 859 { 860 return 'CURRENT_TIMESTAMP()'; 861 } 862 863 /** 864 * Add to the current date and time. 865 * 866 * Usage: 867 * $query->select($query->dateAdd()); 868 * 869 * Prefixing the interval with a - (negative sign) will cause subtraction to be used. 870 * Note: Not all drivers support all units. 871 * 872 * @param string $date The db quoted string representation of the date to add to. May be date or datetime 873 * @param string $interval The string representation of the appropriate number of units 874 * @param string $datePart The part of the date to perform the addition on 875 * 876 * @return string The string with the appropriate sql for addition of dates 877 * 878 * @link https://dev.mysql.com/doc/en/date-and-time-functions.html 879 * @since 1.5.0 880 */ 881 public function dateAdd($date, $interval, $datePart) 882 { 883 return 'DATE_ADD(' . $date . ', INTERVAL ' . $interval . ' ' . $datePart . ')'; 884 } 885 886 /** 887 * Returns a PHP date() function compliant date format for the database driver. 888 * 889 * This method is provided for use where the query object is passed to a function for modification. 890 * If you have direct access to the database object, it is recommended you use the getDateFormat method directly. 891 * 892 * @return string The format string. 893 * 894 * @since 1.0 895 * @throws \RuntimeException 896 */ 897 public function dateFormat() 898 { 899 if (!($this->db instanceof DatabaseInterface)) 900 { 901 throw new \RuntimeException(sprintf('A %s instance is not set to the query object.', DatabaseInterface::class)); 902 } 903 904 return $this->db->getDateFormat(); 905 } 906 907 /** 908 * Creates a HTML formatted dump of the query for debugging purposes. 909 * 910 * Usage: 911 * echo $query->dump(); 912 * 913 * @return string 914 * 915 * @since 1.0 916 * @deprecated 3.0 Deprecated without replacement 917 */ 918 public function dump() 919 { 920 trigger_deprecation( 921 'joomla/database', 922 '2.0.0', 923 '%s() is deprecated and will be removed in 3.0.', 924 __METHOD__ 925 ); 926 927 return '<pre class="jdatabasequery">' . str_replace('#__', $this->db->getPrefix(), $this) . '</pre>'; 928 } 929 930 /** 931 * Add a table name to the DELETE clause of the query. 932 * 933 * Usage: 934 * $query->delete('#__a')->where('id = 1'); 935 * 936 * @param string $table The name of the table to delete from. 937 * 938 * @return $this 939 * 940 * @since 1.0 941 * @throws QueryTypeAlreadyDefinedException if the query type has already been defined 942 */ 943 public function delete($table = null) 944 { 945 if ($this->type !== null && $this->type !== '' && $this->type !== 'delete') 946 { 947 throw new QueryTypeAlreadyDefinedException( 948 \sprintf( 949 'Cannot set the query type to "delete" as the query type is already set to "%s".' 950 . ' You should either call the `clear()` method to reset the type or create a new query object.', 951 $this->type 952 ) 953 ); 954 } 955 956 $this->type = 'delete'; 957 $this->delete = new Query\QueryElement('DELETE', null); 958 959 if (!empty($table)) 960 { 961 $this->from($table); 962 } 963 964 return $this; 965 } 966 967 /** 968 * Alias for escape method 969 * 970 * @param string $text The string to be escaped. 971 * @param boolean $extra Optional parameter to provide extra escaping. 972 * 973 * @return string The escaped string. 974 * 975 * @since 1.0 976 * @throws \RuntimeException if the internal db property is not a valid object. 977 */ 978 public function e($text, $extra = false) 979 { 980 return $this->escape($text, $extra); 981 } 982 983 /** 984 * Method to escape a string for usage in an SQL statement. 985 * 986 * This method is provided for use where the query object is passed to a function for modification. 987 * If you have direct access to the database object, it is recommended you use the escape method directly. 988 * 989 * Note that 'e' is an alias for this method as it is in DatabaseDriver. 990 * 991 * @param string $text The string to be escaped. 992 * @param boolean $extra Optional parameter to provide extra escaping. 993 * 994 * @return string The escaped string. 995 * 996 * @since 1.0 997 * @throws \RuntimeException if the internal db property is not a valid object. 998 */ 999 public function escape($text, $extra = false) 1000 { 1001 if (!($this->db instanceof DatabaseInterface)) 1002 { 1003 throw new \RuntimeException(sprintf('A %s instance is not set to the query object.', DatabaseInterface::class)); 1004 } 1005 1006 return $this->db->escape($text, $extra); 1007 } 1008 1009 /** 1010 * Add a single column, or array of columns to the EXEC clause of the query. 1011 * 1012 * Usage: 1013 * $query->exec('a.*')->exec('b.id'); 1014 * $query->exec(array('a.*', 'b.id')); 1015 * 1016 * @param array|string $columns A string or an array of field names. 1017 * 1018 * @return $this 1019 * 1020 * @since 1.0 1021 * @throws QueryTypeAlreadyDefinedException if the query type has already been defined 1022 */ 1023 public function exec($columns) 1024 { 1025 if ($this->type !== null && $this->type !== '' && $this->type !== 'exec') 1026 { 1027 throw new QueryTypeAlreadyDefinedException( 1028 \sprintf( 1029 'Cannot set the query type to "exec" as the query type is already set to "%s".' 1030 . ' You should either call the `clear()` method to reset the type or create a new query object.', 1031 $this->type 1032 ) 1033 ); 1034 } 1035 1036 $this->type = 'exec'; 1037 1038 if ($this->exec === null) 1039 { 1040 $this->exec = new Query\QueryElement('EXEC', $columns); 1041 } 1042 else 1043 { 1044 $this->exec->append($columns); 1045 } 1046 1047 return $this; 1048 } 1049 1050 /** 1051 * Find a value in a varchar used like a set. 1052 * 1053 * Ensure that the value is an integer before passing to the method. 1054 * 1055 * Usage: 1056 * $query->findInSet((int) $parent->id, 'a.assigned_cat_ids') 1057 * 1058 * @param string $value The value to search for. 1059 * @param string $set The set of values. 1060 * 1061 * @return string A representation of the MySQL find_in_set() function for the driver. 1062 * 1063 * @since 1.5.0 1064 */ 1065 public function findInSet($value, $set) 1066 { 1067 return ''; 1068 } 1069 1070 /** 1071 * Add a table to the FROM clause of the query. 1072 * 1073 * Usage: 1074 * $query->select('*')->from('#__a'); 1075 * $query->select('*')->from($subquery->alias('a')); 1076 * 1077 * @param string|DatabaseQuery $table The name of the table or a DatabaseQuery object (or a child of it) with alias set. 1078 * 1079 * @return $this 1080 * 1081 * @since 1.0 1082 * @throws \RuntimeException 1083 */ 1084 public function from($table) 1085 { 1086 if ($table instanceof $this && $table->alias === null) 1087 { 1088 throw new \RuntimeException('JLIB_DATABASE_ERROR_NULL_SUBQUERY_ALIAS'); 1089 } 1090 1091 if ($this->from === null) 1092 { 1093 $this->from = new Query\QueryElement('FROM', $table); 1094 } 1095 else 1096 { 1097 $this->from->append($table); 1098 } 1099 1100 return $this; 1101 } 1102 1103 /** 1104 * Add alias for current query. 1105 * 1106 * Usage: 1107 * $query->select('*')->from('#__a')->alias('subquery'); 1108 * 1109 * @param string $alias Alias used for a JDatabaseQuery. 1110 * 1111 * @return $this 1112 * 1113 * @since 2.0.0 1114 */ 1115 public function alias($alias) 1116 { 1117 $this->alias = $alias; 1118 1119 return $this; 1120 } 1121 1122 /** 1123 * Used to get a string to extract year from date column. 1124 * 1125 * Usage: 1126 * $query->select($query->year($query->quoteName('dateColumn'))); 1127 * 1128 * @param string $date Date column containing year to be extracted. 1129 * 1130 * @return string Returns string to extract year from a date. 1131 * 1132 * @since 1.0 1133 */ 1134 public function year($date) 1135 { 1136 return 'YEAR(' . $date . ')'; 1137 } 1138 1139 /** 1140 * Used to get a string to extract month from date column. 1141 * 1142 * Usage: 1143 * $query->select($query->month($query->quoteName('dateColumn'))); 1144 * 1145 * @param string $date Date column containing month to be extracted. 1146 * 1147 * @return string Returns string to extract month from a date. 1148 * 1149 * @since 1.0 1150 */ 1151 public function month($date) 1152 { 1153 return 'MONTH(' . $date . ')'; 1154 } 1155 1156 /** 1157 * Used to get a string to extract day from date column. 1158 * 1159 * Usage: 1160 * $query->select($query->day($query->quoteName('dateColumn'))); 1161 * 1162 * @param string $date Date column containing day to be extracted. 1163 * 1164 * @return string Returns string to extract day from a date. 1165 * 1166 * @since 1.0 1167 */ 1168 public function day($date) 1169 { 1170 return 'DAY(' . $date . ')'; 1171 } 1172 1173 /** 1174 * Used to get a string to extract hour from date column. 1175 * 1176 * Usage: 1177 * $query->select($query->hour($query->quoteName('dateColumn'))); 1178 * 1179 * @param string $date Date column containing hour to be extracted. 1180 * 1181 * @return string Returns string to extract hour from a date. 1182 * 1183 * @since 1.0 1184 */ 1185 public function hour($date) 1186 { 1187 return 'HOUR(' . $date . ')'; 1188 } 1189 1190 /** 1191 * Used to get a string to extract minute from date column. 1192 * 1193 * Usage: 1194 * $query->select($query->minute($query->quoteName('dateColumn'))); 1195 * 1196 * @param string $date Date column containing minute to be extracted. 1197 * 1198 * @return string Returns string to extract minute from a date. 1199 * 1200 * @since 1.0 1201 */ 1202 public function minute($date) 1203 { 1204 return 'MINUTE(' . $date . ')'; 1205 } 1206 1207 /** 1208 * Used to get a string to extract seconds from date column. 1209 * 1210 * Usage: 1211 * $query->select($query->second($query->quoteName('dateColumn'))); 1212 * 1213 * @param string $date Date column containing second to be extracted. 1214 * 1215 * @return string Returns string to extract second from a date. 1216 * 1217 * @since 1.0 1218 */ 1219 public function second($date) 1220 { 1221 return 'SECOND(' . $date . ')'; 1222 } 1223 1224 /** 1225 * Add a grouping column to the GROUP clause of the query. 1226 * 1227 * Usage: 1228 * $query->group('id'); 1229 * 1230 * @param array|string $columns A string or array of ordering columns. 1231 * 1232 * @return $this 1233 * 1234 * @since 1.0 1235 */ 1236 public function group($columns) 1237 { 1238 if ($this->group === null) 1239 { 1240 $this->group = new Query\QueryElement('GROUP BY', $columns); 1241 } 1242 else 1243 { 1244 $this->group->append($columns); 1245 } 1246 1247 return $this; 1248 } 1249 1250 /** 1251 * A conditions to the HAVING clause of the query. 1252 * 1253 * Usage: 1254 * $query->group('id')->having('COUNT(id) > 5'); 1255 * 1256 * @param array|string $conditions A string or array of columns. 1257 * @param string $glue The glue by which to join the conditions. Defaults to AND. 1258 * 1259 * @return $this 1260 * 1261 * @since 1.0 1262 */ 1263 public function having($conditions, $glue = 'AND') 1264 { 1265 if ($this->having === null) 1266 { 1267 $glue = strtoupper($glue); 1268 $this->having = new Query\QueryElement('HAVING', $conditions, " $glue "); 1269 } 1270 else 1271 { 1272 $this->having->append($conditions); 1273 } 1274 1275 return $this; 1276 } 1277 1278 /** 1279 * Add a table name to the INSERT clause of the query. 1280 * 1281 * Usage: 1282 * $query->insert('#__a')->set('id = 1'); 1283 * $query->insert('#__a')->columns('id, title')->values('1,2')->values('3,4'); 1284 * $query->insert('#__a')->columns('id, title')->values(array('1,2', '3,4')); 1285 * 1286 * @param string $table The name of the table to insert data into. 1287 * @param boolean $incrementField The name of the field to auto increment. 1288 * 1289 * @return $this 1290 * 1291 * @since 1.0 1292 * @throws QueryTypeAlreadyDefinedException if the query type has already been defined 1293 */ 1294 public function insert($table, $incrementField = false) 1295 { 1296 if ($this->type !== null && $this->type !== '' && $this->type !== 'insert') 1297 { 1298 throw new QueryTypeAlreadyDefinedException( 1299 \sprintf( 1300 'Cannot set the query type to "insert" as the query type is already set to "%s".' 1301 . ' You should either call the `clear()` method to reset the type or create a new query object.', 1302 $this->type 1303 ) 1304 ); 1305 } 1306 1307 $this->type = 'insert'; 1308 $this->insert = new Query\QueryElement('INSERT INTO', $table); 1309 $this->autoIncrementField = $incrementField; 1310 1311 return $this; 1312 } 1313 1314 /** 1315 * Add a JOIN clause to the query. 1316 * 1317 * Usage: 1318 * $query->join('INNER', 'b', 'b.id = a.id); 1319 * 1320 * @param string $type The type of join. This string is prepended to the JOIN keyword. 1321 * @param string $table The name of table. 1322 * @param string $condition The join condition. 1323 * 1324 * @return $this 1325 * 1326 * @since 1.0 1327 */ 1328 public function join($type, $table, $condition = null) 1329 { 1330 $type = strtoupper($type) . ' JOIN'; 1331 1332 if ($condition !== null) 1333 { 1334 $this->join[] = new Query\QueryElement($type, [$table, $condition], ' ON '); 1335 } 1336 else 1337 { 1338 $this->join[] = new Query\QueryElement($type, $table); 1339 } 1340 1341 return $this; 1342 } 1343 1344 /** 1345 * Add an INNER JOIN clause to the query. 1346 * 1347 * Usage: 1348 * $query->innerJoin('b', 'b.id = a.id')->innerJoin('c', 'c.id = b.id'); 1349 * 1350 * @param string $table The name of table. 1351 * @param string $condition The join condition. 1352 * 1353 * @return $this 1354 * 1355 * @since 1.0 1356 */ 1357 public function innerJoin($table, $condition = null) 1358 { 1359 return $this->join('INNER', $table, $condition); 1360 } 1361 1362 /** 1363 * Add an OUTER JOIN clause to the query. 1364 * 1365 * Usage: 1366 * $query->outerJoin('b', 'b.id = a.id')->leftJoin('c', 'c.id = b.id'); 1367 * 1368 * @param string $table The name of table. 1369 * @param string $condition The join condition. 1370 * 1371 * @return $this 1372 * 1373 * @since 1.0 1374 */ 1375 public function outerJoin($table, $condition = null) 1376 { 1377 return $this->join('OUTER', $table, $condition); 1378 } 1379 1380 /** 1381 * Add a LEFT JOIN clause to the query. 1382 * 1383 * Usage: 1384 * $query->leftJoin('b', 'b.id = a.id')->leftJoin('c', 'c.id = b.id'); 1385 * 1386 * @param string $table The name of table. 1387 * @param string $condition The join condition. 1388 * 1389 * @return $this 1390 * 1391 * @since 1.0 1392 */ 1393 public function leftJoin($table, $condition = null) 1394 { 1395 return $this->join('LEFT', $table, $condition); 1396 } 1397 1398 /** 1399 * Add a RIGHT JOIN clause to the query. 1400 * 1401 * Usage: 1402 * $query->rightJoin('b', 'b.id = a.id')->rightJoin('c', 'c.id = b.id'); 1403 * 1404 * @param string $table The name of table. 1405 * @param string $condition The join condition. 1406 * 1407 * @return $this 1408 * 1409 * @since 1.0 1410 */ 1411 public function rightJoin($table, $condition = null) 1412 { 1413 return $this->join('RIGHT', $table, $condition); 1414 } 1415 1416 /** 1417 * Get the length of a string in bytes. 1418 * 1419 * Note, use 'charLength' to find the number of characters in a string. 1420 * 1421 * Usage: 1422 * query->where($query->length('a').' > 3'); 1423 * 1424 * @param string $value The string to measure. 1425 * 1426 * @return integer 1427 * 1428 * @since 1.0 1429 */ 1430 public function length($value) 1431 { 1432 return 'LENGTH(' . $value . ')'; 1433 } 1434 1435 /** 1436 * Get the null or zero representation of a timestamp for the database driver. 1437 * 1438 * This method is provided for use where the query object is passed to a function for modification. 1439 * If you have direct access to the database object, it is recommended you use the nullDate method directly. 1440 * 1441 * Usage: 1442 * $query->where('modified_date <> '.$query->nullDate()); 1443 * 1444 * @param boolean $quoted Optionally wraps the null date in database quotes (true by default). 1445 * 1446 * @return string Null or zero representation of a timestamp. 1447 * 1448 * @since 1.0 1449 * @throws \RuntimeException 1450 */ 1451 public function nullDate($quoted = true) 1452 { 1453 if (!($this->db instanceof DatabaseInterface)) 1454 { 1455 throw new \RuntimeException(sprintf('A %s instance is not set to the query object.', DatabaseInterface::class)); 1456 } 1457 1458 $result = $this->db->getNullDate(); 1459 1460 if ($quoted) 1461 { 1462 return $this->db->quote($result); 1463 } 1464 1465 return $result; 1466 } 1467 1468 /** 1469 * Generate a SQL statement to check if column represents a zero or null datetime. 1470 * 1471 * Usage: 1472 * $query->where($query->isNullDatetime('modified_date')); 1473 * 1474 * @param string $column A column name. 1475 * 1476 * @return string 1477 * 1478 * @since 2.0.0 1479 */ 1480 public function isNullDatetime($column) 1481 { 1482 if (!$this->db instanceof DatabaseInterface) 1483 { 1484 throw new \RuntimeException(sprintf('A %s instance is not set to the query object.', DatabaseInterface::class)); 1485 } 1486 1487 if ($this->nullDatetimeList) 1488 { 1489 return "($column IN (" 1490 . implode(', ', $this->db->quote($this->nullDatetimeList)) 1491 . ") OR $column IS NULL)"; 1492 } 1493 1494 return "$column IS NULL"; 1495 } 1496 1497 /** 1498 * Add a ordering column to the ORDER clause of the query. 1499 * 1500 * Usage: 1501 * $query->order('foo')->order('bar'); 1502 * $query->order(array('foo','bar')); 1503 * 1504 * @param array|string $columns A string or array of ordering columns. 1505 * 1506 * @return $this 1507 * 1508 * @since 1.0 1509 */ 1510 public function order($columns) 1511 { 1512 if ($this->order === null) 1513 { 1514 $this->order = new Query\QueryElement('ORDER BY', $columns); 1515 } 1516 else 1517 { 1518 $this->order->append($columns); 1519 } 1520 1521 return $this; 1522 } 1523 1524 /** 1525 * Alias for quote method 1526 * 1527 * @param array|string $text A string or an array of strings to quote. 1528 * @param boolean $escape True (default) to escape the string, false to leave it unchanged. 1529 * 1530 * @return string The quoted input string. 1531 * 1532 * @since 1.0 1533 * @throws \RuntimeException if the internal db property is not a valid object. 1534 */ 1535 public function q($text, $escape = true) 1536 { 1537 return $this->quote($text, $escape); 1538 } 1539 1540 /** 1541 * Method to quote and optionally escape a string to database requirements for insertion into the database. 1542 * 1543 * This method is provided for use where the query object is passed to a function for modification. 1544 * If you have direct access to the database object, it is recommended you use the quote method directly. 1545 * 1546 * Note that 'q' is an alias for this method as it is in DatabaseDriver. 1547 * 1548 * Usage: 1549 * $query->quote('fulltext'); 1550 * $query->q('fulltext'); 1551 * $query->q(array('option', 'fulltext')); 1552 * 1553 * @param array|string $text A string or an array of strings to quote. 1554 * @param boolean $escape True (default) to escape the string, false to leave it unchanged. 1555 * 1556 * @return string The quoted input string. 1557 * 1558 * @since 1.0 1559 * @throws \RuntimeException if the internal db property is not a valid object. 1560 */ 1561 public function quote($text, $escape = true) 1562 { 1563 if (!($this->db instanceof DatabaseInterface)) 1564 { 1565 throw new \RuntimeException(sprintf('A %s instance is not set to the query object.', DatabaseInterface::class)); 1566 } 1567 1568 return $this->db->quote($text, $escape); 1569 } 1570 1571 /** 1572 * Alias for quoteName method 1573 * 1574 * @param array|string $name The identifier name to wrap in quotes, or an array of identifier names to wrap in quotes. 1575 * Each type supports dot-notation name. 1576 * @param array|string $as The AS query part associated to $name. It can be string or array, in latter case it has to be 1577 * same length of $name; if is null there will not be any AS part for string or array element. 1578 * 1579 * @return array|string The quote wrapped name, same type of $name. 1580 * 1581 * @since 1.0 1582 * @throws \RuntimeException if the internal db property is not a valid object. 1583 */ 1584 public function qn($name, $as = null) 1585 { 1586 return $this->quoteName($name, $as); 1587 } 1588 1589 /** 1590 * Wrap an SQL statement identifier name such as column, table or database names in quotes to prevent injection 1591 * risks and reserved word conflicts. 1592 * 1593 * This method is provided for use where the query object is passed to a function for modification. 1594 * If you have direct access to the database object, it is recommended you use the quoteName method directly. 1595 * 1596 * Note that 'qn' is an alias for this method as it is in DatabaseDriver. 1597 * 1598 * Usage: 1599 * $query->quoteName('#__a'); 1600 * $query->qn('#__a'); 1601 * 1602 * @param array|string $name The identifier name to wrap in quotes, or an array of identifier names to wrap in quotes. 1603 * Each type supports dot-notation name. 1604 * @param array|string $as The AS query part associated to $name. It can be string or array, in latter case it has to be 1605 * same length of $name; if is null there will not be any AS part for string or array element. 1606 * 1607 * @return array|string The quote wrapped name, same type of $name. 1608 * 1609 * @since 1.0 1610 * @throws \RuntimeException if the internal db property is not a valid object. 1611 */ 1612 public function quoteName($name, $as = null) 1613 { 1614 if (!($this->db instanceof DatabaseInterface)) 1615 { 1616 throw new \RuntimeException(sprintf('A %s instance is not set to the query object.', DatabaseInterface::class)); 1617 } 1618 1619 return $this->db->quoteName($name, $as); 1620 } 1621 1622 /** 1623 * Get the function to return a random floating-point value 1624 * 1625 * Usage: 1626 * $query->rand(); 1627 * 1628 * @return string 1629 * 1630 * @since 1.5.0 1631 */ 1632 public function rand() 1633 { 1634 return ''; 1635 } 1636 1637 /** 1638 * Get the regular expression operator 1639 * 1640 * Usage: 1641 * $query->where('field ' . $query->regexp($search)); 1642 * 1643 * @param string $value The regex pattern. 1644 * 1645 * @return string 1646 * 1647 * @since 1.5.0 1648 */ 1649 public function regexp($value) 1650 { 1651 return ' ' . $value; 1652 } 1653 1654 /** 1655 * Add a single column, or array of columns to the SELECT clause of the query. 1656 * 1657 * Note that you must not mix insert, update, delete and select method calls when building a query. 1658 * The select method can, however, be called multiple times in the same query. 1659 * 1660 * Usage: 1661 * $query->select('a.*')->select('b.id'); 1662 * $query->select(array('a.*', 'b.id')); 1663 * 1664 * @param array|string $columns A string or an array of field names. 1665 * 1666 * @return $this 1667 * 1668 * @since 1.0 1669 * @throws QueryTypeAlreadyDefinedException if the query type has already been defined 1670 */ 1671 public function select($columns) 1672 { 1673 if ($this->type !== null && $this->type !== '' && $this->type !== 'select') 1674 { 1675 throw new QueryTypeAlreadyDefinedException( 1676 \sprintf( 1677 'Cannot set the query type to "select" as the query type is already set to "%s".' 1678 . ' You should either call the `clear()` method to reset the type or create a new query object.', 1679 $this->type 1680 ) 1681 ); 1682 } 1683 1684 $this->type = 'select'; 1685 1686 if ($this->select === null) 1687 { 1688 $this->select = new Query\QueryElement('SELECT', $columns); 1689 } 1690 else 1691 { 1692 $this->select->append($columns); 1693 } 1694 1695 return $this; 1696 } 1697 1698 /** 1699 * Add a single condition string, or an array of strings to the SET clause of the query. 1700 * 1701 * Usage: 1702 * $query->set('a = 1')->set('b = 2'); 1703 * $query->set(array('a = 1', 'b = 2'); 1704 * 1705 * @param array|string $conditions A string or array of string conditions. 1706 * @param string $glue The glue by which to join the condition strings. Defaults to ,. 1707 * Note that the glue is set on first use and cannot be changed. 1708 * 1709 * @return $this 1710 * 1711 * @since 1.0 1712 */ 1713 public function set($conditions, $glue = ',') 1714 { 1715 if ($this->set === null) 1716 { 1717 $glue = strtoupper($glue); 1718 $this->set = new Query\QueryElement('SET', $conditions, \PHP_EOL . "\t$glue "); 1719 } 1720 else 1721 { 1722 $this->set->append($conditions); 1723 } 1724 1725 return $this; 1726 } 1727 1728 /** 1729 * Sets the offset and limit for the result set, if the database driver supports it. 1730 * 1731 * Usage: 1732 * $query->setLimit(100, 0); (retrieve 100 rows, starting at first record) 1733 * $query->setLimit(50, 50); (retrieve 50 rows, starting at 50th record) 1734 * 1735 * @param integer $limit The limit for the result set 1736 * @param integer $offset The offset for the result set 1737 * 1738 * @return $this 1739 * 1740 * @since 2.0.0 1741 */ 1742 public function setLimit($limit = 0, $offset = 0) 1743 { 1744 $this->limit = (int) $limit; 1745 $this->offset = (int) $offset; 1746 1747 return $this; 1748 } 1749 1750 /** 1751 * Allows a direct query to be provided to the database driver's setQuery() method, but still allow queries 1752 * to have bounded variables. 1753 * 1754 * Usage: 1755 * $query->setQuery('select * from #__users'); 1756 * 1757 * @param DatabaseQuery|string $sql A SQL query string or DatabaseQuery object 1758 * 1759 * @return $this 1760 * 1761 * @since 1.0 1762 */ 1763 public function setQuery($sql) 1764 { 1765 $this->sql = $sql; 1766 1767 return $this; 1768 } 1769 1770 /** 1771 * Add a table name to the UPDATE clause of the query. 1772 * 1773 * Usage: 1774 * $query->update('#__foo')->set(...); 1775 * 1776 * @param string $table A table to update. 1777 * 1778 * @return $this 1779 * 1780 * @since 1.0 1781 * @throws QueryTypeAlreadyDefinedException if the query type has already been defined 1782 */ 1783 public function update($table) 1784 { 1785 if ($this->type !== null && $this->type !== '' && $this->type !== 'update') 1786 { 1787 throw new QueryTypeAlreadyDefinedException( 1788 \sprintf( 1789 'Cannot set the query type to "update" as the query type is already set to "%s".' 1790 . ' You should either call the `clear()` method to reset the type or create a new query object.', 1791 $this->type 1792 ) 1793 ); 1794 } 1795 1796 $this->type = 'update'; 1797 $this->update = new Query\QueryElement('UPDATE', $table); 1798 1799 return $this; 1800 } 1801 1802 /** 1803 * Adds a tuple, or array of tuples that would be used as values for an INSERT INTO statement. 1804 * 1805 * Usage: 1806 * $query->values('1,2,3')->values('4,5,6'); 1807 * $query->values(array('1,2,3', '4,5,6')); 1808 * 1809 * @param array|string $values A single tuple, or array of tuples. 1810 * 1811 * @return $this 1812 * 1813 * @since 1.0 1814 */ 1815 public function values($values) 1816 { 1817 if ($this->values === null) 1818 { 1819 $this->values = new Query\QueryElement('()', $values, '),('); 1820 } 1821 else 1822 { 1823 $this->values->append($values); 1824 } 1825 1826 return $this; 1827 } 1828 1829 /** 1830 * Add a single condition, or an array of conditions to the WHERE clause of the query. 1831 * 1832 * Usage: 1833 * $query->where('a = 1')->where('b = 2'); 1834 * $query->where(array('a = 1', 'b = 2')); 1835 * 1836 * @param array|string $conditions A string or array of where conditions. 1837 * @param string $glue The glue by which to join the conditions. Defaults to AND. 1838 * Note that the glue is set on first use and cannot be changed. 1839 * 1840 * @return $this 1841 * 1842 * @since 1.0 1843 */ 1844 public function where($conditions, $glue = 'AND') 1845 { 1846 if ($this->where === null) 1847 { 1848 $glue = strtoupper($glue); 1849 $this->where = new Query\QueryElement('WHERE', $conditions, " $glue "); 1850 } 1851 else 1852 { 1853 $this->where->append($conditions); 1854 } 1855 1856 return $this; 1857 } 1858 1859 /** 1860 * Add a WHERE IN statement to the query. 1861 * 1862 * Note that all values must be the same data type. 1863 * 1864 * Usage 1865 * $query->whereIn('id', [1, 2, 3]); 1866 * 1867 * @param string $keyName Key name for the where clause 1868 * @param array $keyValues Array of values to be matched 1869 * @param array|string $dataType Constant corresponding to a SQL datatype. It can be an array, in this case it 1870 * has to be same length of $keyValues 1871 * 1872 * @return $this 1873 * 1874 * @since 2.0.0 1875 */ 1876 public function whereIn(string $keyName, array $keyValues, $dataType = ParameterType::INTEGER) 1877 { 1878 return $this->where( 1879 $keyName . ' IN (' . implode(',', $this->bindArray($keyValues, $dataType)) . ')' 1880 ); 1881 } 1882 1883 /** 1884 * Add a WHERE NOT IN statement to the query. 1885 * 1886 * Note that all values must be the same data type. 1887 * 1888 * Usage 1889 * $query->whereNotIn('id', [1, 2, 3]); 1890 * 1891 * @param string $keyName Key name for the where clause 1892 * @param array $keyValues Array of values to be matched 1893 * @param array|string $dataType Constant corresponding to a SQL datatype. It can be an array, in this case it 1894 * has to be same length of $keyValues 1895 * 1896 * @return $this 1897 * 1898 * @since 2.0.0 1899 */ 1900 public function whereNotIn(string $keyName, array $keyValues, $dataType = ParameterType::INTEGER) 1901 { 1902 return $this->where( 1903 $keyName . ' NOT IN (' . implode(',', $this->bindArray($keyValues, $dataType)) . ')' 1904 ); 1905 } 1906 1907 /** 1908 * Extend the WHERE clause with a single condition or an array of conditions, with a potentially 1909 * different logical operator from the one in the current WHERE clause. 1910 * 1911 * Usage: 1912 * $query->where(array('a = 1', 'b = 2'))->extendWhere('XOR', array('c = 3', 'd = 4')); 1913 * will produce: WHERE ((a = 1 AND b = 2) XOR (c = 3 AND d = 4) 1914 * 1915 * @param string $outerGlue The glue by which to join the conditions to the current WHERE conditions. 1916 * @param mixed $conditions A string or array of WHERE conditions. 1917 * @param string $innerGlue The glue by which to join the conditions. Defaults to AND. 1918 * 1919 * @return $this 1920 * 1921 * @since 1.3.0 1922 */ 1923 public function extendWhere($outerGlue, $conditions, $innerGlue = 'AND') 1924 { 1925 // Replace the current WHERE with a new one which has the old one as an unnamed child. 1926 $this->where = new Query\QueryElement('WHERE', $this->where->setName('()'), " $outerGlue "); 1927 1928 // Append the new conditions as a new unnamed child. 1929 $this->where->append(new Query\QueryElement('()', $conditions, " $innerGlue ")); 1930 1931 return $this; 1932 } 1933 1934 /** 1935 * Extend the WHERE clause with an OR and a single condition or an array of conditions. 1936 * 1937 * Usage: 1938 * $query->where(array('a = 1', 'b = 2'))->orWhere(array('c = 3', 'd = 4')); 1939 * will produce: WHERE ((a = 1 AND b = 2) OR (c = 3 AND d = 4) 1940 * 1941 * @param mixed $conditions A string or array of WHERE conditions. 1942 * @param string $glue The glue by which to join the conditions. Defaults to AND. 1943 * 1944 * @return $this 1945 * 1946 * @since 1.3.0 1947 */ 1948 public function orWhere($conditions, $glue = 'AND') 1949 { 1950 return $this->extendWhere('OR', $conditions, $glue); 1951 } 1952 1953 /** 1954 * Extend the WHERE clause with an AND and a single condition or an array of conditions. 1955 * 1956 * Usage: 1957 * $query->where(array('a = 1', 'b = 2'))->andWhere(array('c = 3', 'd = 4')); 1958 * will produce: WHERE ((a = 1 AND b = 2) AND (c = 3 OR d = 4) 1959 * 1960 * @param mixed $conditions A string or array of WHERE conditions. 1961 * @param string $glue The glue by which to join the conditions. Defaults to OR. 1962 * 1963 * @return $this 1964 * 1965 * @since 1.3.0 1966 */ 1967 public function andWhere($conditions, $glue = 'OR') 1968 { 1969 return $this->extendWhere('AND', $conditions, $glue); 1970 } 1971 1972 /** 1973 * Method to add a variable to an internal array that will be bound to a prepared SQL statement before query execution. 1974 * 1975 * @param array|string|integer $key The key that will be used in your SQL query to reference the value. Usually of 1976 * the form ':key', but can also be an integer. 1977 * @param mixed $value The value that will be bound. It can be an array, in this case it has to be 1978 * same length of $key; The value is passed by reference to support output 1979 * parameters such as those possible with stored procedures. 1980 * @param array|string $dataType Constant corresponding to a SQL datatype. It can be an array, in this case it 1981 * has to be same length of $key 1982 * @param integer $length The length of the variable. Usually required for OUTPUT parameters. 1983 * @param array $driverOptions Optional driver options to be used. 1984 * 1985 * @return $this 1986 * 1987 * @since 1.5.0 1988 * @throws \InvalidArgumentException 1989 */ 1990 public function bind($key, &$value, $dataType = ParameterType::STRING, $length = 0, $driverOptions = []) 1991 { 1992 if (!$key) 1993 { 1994 throw new \InvalidArgumentException('A key is required'); 1995 } 1996 1997 $key = (array) $key; 1998 $count = \count($key); 1999 2000 if (\is_array($value)) 2001 { 2002 if ($count != \count($value)) 2003 { 2004 throw new \InvalidArgumentException('Array length of $key and $value are not equal'); 2005 } 2006 2007 reset($value); 2008 } 2009 2010 if (\is_array($dataType) && $count != \count($dataType)) 2011 { 2012 throw new \InvalidArgumentException('Array length of $key and $dataType are not equal'); 2013 } 2014 2015 foreach ($key as $index) 2016 { 2017 if (\is_array($value)) 2018 { 2019 $localValue = &$value[key($value)]; 2020 next($value); 2021 } 2022 else 2023 { 2024 $localValue = &$value; 2025 } 2026 2027 if (\is_array($dataType)) 2028 { 2029 $localDataType = array_shift($dataType); 2030 } 2031 else 2032 { 2033 $localDataType = $dataType; 2034 } 2035 2036 // Validate parameter type 2037 if (!isset($this->parameterMapping[$localDataType])) 2038 { 2039 throw new \InvalidArgumentException(sprintf('Unsupported parameter type `%s`', $localDataType)); 2040 } 2041 2042 $obj = new \stdClass; 2043 $obj->value = &$localValue; 2044 $obj->dataType = $this->parameterMapping[$localDataType]; 2045 $obj->length = $length; 2046 $obj->driverOptions = $driverOptions; 2047 2048 // Add the Key/Value into the bounded array 2049 $this->bounded[$index] = $obj; 2050 2051 unset($localValue); 2052 } 2053 2054 return $this; 2055 } 2056 2057 /** 2058 * Method to unbind a bound variable. 2059 * 2060 * @param array|string|integer $key The key or array of keys to unbind. 2061 * 2062 * @return $this 2063 * 2064 * @since 2.0.0 2065 */ 2066 public function unbind($key) 2067 { 2068 if (\is_array($key)) 2069 { 2070 foreach ($key as $k) 2071 { 2072 unset($this->bounded[$k]); 2073 } 2074 } 2075 else 2076 { 2077 unset($this->bounded[$key]); 2078 } 2079 2080 return $this; 2081 } 2082 2083 /** 2084 * Binds an array of values and returns an array of prepared parameter names. 2085 * 2086 * Note that all values must be the same data type. 2087 * 2088 * Usage: 2089 * $query->where('column in (' . implode(',', $query->bindArray($keyValues, $dataType)) . ')'); 2090 * 2091 * @param array $values Values to bind 2092 * @param array|string $dataType Constant corresponding to a SQL datatype. It can be an array, in this case it 2093 * has to be same length of $key 2094 * 2095 * @return array An array with parameter names 2096 * 2097 * @since 2.0.0 2098 */ 2099 public function bindArray(array $values, $dataType = ParameterType::INTEGER) 2100 { 2101 $parameterNames = []; 2102 2103 for ($i = 0; $i < count($values); $i++) 2104 { 2105 $parameterNames[] = ':preparedArray' . (++$this->preparedIndex); 2106 } 2107 2108 $this->bind($parameterNames, $values, $dataType); 2109 2110 return $parameterNames; 2111 } 2112 2113 /** 2114 * Method to provide basic copy support. 2115 * 2116 * Any object pushed into the data of this class should have its own __clone() implementation. 2117 * This method does not support copying objects in a multidimensional array. 2118 * 2119 * @return void 2120 * 2121 * @since 1.0 2122 */ 2123 public function __clone() 2124 { 2125 foreach ($this as $k => $v) 2126 { 2127 if ($k === 'db') 2128 { 2129 continue; 2130 } 2131 2132 if (\is_object($v)) 2133 { 2134 $this->{$k} = clone $v; 2135 } 2136 elseif (\is_array($v)) 2137 { 2138 foreach ($v as $i => $element) 2139 { 2140 if (\is_object($element)) 2141 { 2142 $this->{$k}[$i] = clone $element; 2143 } 2144 } 2145 } 2146 } 2147 } 2148 2149 /** 2150 * Retrieves the bound parameters array when key is null and returns it by reference. If a key is provided then that item is 2151 * returned. 2152 * 2153 * @param mixed $key The bounded variable key to retrieve. 2154 * 2155 * @return mixed 2156 * 2157 * @since 1.5.0 2158 */ 2159 public function &getBounded($key = null) 2160 { 2161 if (empty($key)) 2162 { 2163 return $this->bounded; 2164 } 2165 2166 if (isset($this->bounded[$key])) 2167 { 2168 return $this->bounded[$key]; 2169 } 2170 } 2171 2172 /** 2173 * Combine a select statement to the current query by one of the set operators. 2174 * Operators: UNION, UNION ALL, EXCEPT or INTERSECT. 2175 * 2176 * @param string $name The name of the set operator with parentheses. 2177 * @param DatabaseQuery|string $query The DatabaseQuery object or string. 2178 * 2179 * @return $this 2180 * 2181 * @since 2.0.0 2182 */ 2183 protected function merge($name, $query) 2184 { 2185 $this->type = $this->type ?: 'select'; 2186 2187 $this->merge[] = new Query\QueryElement($name, $query); 2188 2189 return $this; 2190 } 2191 2192 /** 2193 * Add a query to UNION with the current query. 2194 * 2195 * Usage: 2196 * $query->union('SELECT name FROM #__foo') 2197 * $query->union('SELECT name FROM #__foo', true) 2198 * 2199 * @param DatabaseQuery|string $query The DatabaseQuery object or string to union. 2200 * @param boolean $distinct True to only return distinct rows from the union. 2201 * 2202 * @return $this 2203 * 2204 * @since 1.0 2205 */ 2206 public function union($query, $distinct = true) 2207 { 2208 // Set up the name with parentheses, the DISTINCT flag is redundant 2209 return $this->merge($distinct ? 'UNION ()' : 'UNION ALL ()', $query); 2210 } 2211 2212 /** 2213 * Add a query to UNION ALL with the current query. 2214 * 2215 * Usage: 2216 * $query->unionAll('SELECT name FROM #__foo') 2217 * 2218 * @param DatabaseQuery|string $query The DatabaseQuery object or string to union. 2219 * 2220 * @return $this 2221 * 2222 * @see union 2223 * @since 1.5.0 2224 */ 2225 public function unionAll($query) 2226 { 2227 return $this->union($query, false); 2228 } 2229 2230 /** 2231 * Set a single query to the query set. 2232 * On this type of DatabaseQuery you can use union(), unionAll(), order() and setLimit() 2233 * 2234 * Usage: 2235 * $query->querySet($query2->select('name')->from('#__foo')->order('id DESC')->setLimit(1)) 2236 * ->unionAll($query3->select('name')->from('#__foo')->order('id')->setLimit(1)) 2237 * ->order('name') 2238 * ->setLimit(1) 2239 * 2240 * @param DatabaseQuery $query The DatabaseQuery object or string. 2241 * 2242 * @return $this 2243 * 2244 * @since 2.0.0 2245 */ 2246 public function querySet($query) 2247 { 2248 $this->type = 'querySet'; 2249 2250 $this->querySet = $query; 2251 2252 return $this; 2253 } 2254 2255 /** 2256 * Create a DatabaseQuery object of type querySet from current query. 2257 * 2258 * Usage: 2259 * $query->select('name')->from('#__foo')->order('id DESC')->setLimit(1) 2260 * ->toQuerySet() 2261 * ->unionAll($query2->select('name')->from('#__foo')->order('id')->setLimit(1)) 2262 * ->order('name') 2263 * ->setLimit(1) 2264 * 2265 * @return DatabaseQuery A new object of the DatabaseQuery. 2266 * 2267 * @since 2.0.0 2268 */ 2269 public function toQuerySet() 2270 { 2271 return (new static($this->db))->querySet($this); 2272 } 2273 2274 /** 2275 * Find and replace sprintf-like tokens in a format string. 2276 * Each token takes one of the following forms: 2277 * %% - A literal percent character. 2278 * %[t] - Where [t] is a type specifier. 2279 * %[n]$[x] - Where [n] is an argument specifier and [t] is a type specifier. 2280 * 2281 * Types: 2282 * a - Numeric: Replacement text is coerced to a numeric type but not quoted or escaped. 2283 * e - Escape: Replacement text is passed to $this->escape(). 2284 * E - Escape (extra): Replacement text is passed to $this->escape() with true as the second argument. 2285 * n - Name Quote: Replacement text is passed to $this->quoteName(). 2286 * q - Quote: Replacement text is passed to $this->quote(). 2287 * Q - Quote (no escape): Replacement text is passed to $this->quote() with false as the second argument. 2288 * r - Raw: Replacement text is used as-is. (Be careful) 2289 * 2290 * Date Types: 2291 * - Replacement text automatically quoted (use uppercase for Name Quote). 2292 * - Replacement text should be a string in date format or name of a date column. 2293 * y/Y - Year 2294 * m/M - Month 2295 * d/D - Day 2296 * h/H - Hour 2297 * i/I - Minute 2298 * s/S - Second 2299 * 2300 * Invariable Types: 2301 * - Takes no argument. 2302 * - Argument index not incremented. 2303 * t - Replacement text is the result of $this->currentTimestamp(). 2304 * z - Replacement text is the result of $this->nullDate(false). 2305 * Z - Replacement text is the result of $this->nullDate(true). 2306 * 2307 * Usage: 2308 * $query->format('SELECT %1$n FROM %2$n WHERE %3$n = %4$a', 'foo', '#__foo', 'bar', 1); 2309 * Returns: SELECT `foo` FROM `#__foo` WHERE `bar` = 1 2310 * 2311 * Notes: 2312 * The argument specifier is optional but recommended for clarity. 2313 * The argument index used for unspecified tokens is incremented only when used. 2314 * 2315 * @param string $format The formatting string. 2316 * 2317 * @return string Returns a string produced according to the formatting string. 2318 * 2319 * @since 1.0 2320 */ 2321 public function format($format) 2322 { 2323 $query = $this; 2324 $args = \array_slice(\func_get_args(), 1); 2325 array_unshift($args, null); 2326 2327 $i = 1; 2328 $func = function ($match) use ($query, $args, &$i) 2329 { 2330 if (isset($match[6]) && $match[6] === '%') 2331 { 2332 return '%'; 2333 } 2334 2335 // No argument required, do not increment the argument index. 2336 switch ($match[5]) 2337 { 2338 case 't': 2339 return $query->currentTimestamp(); 2340 2341 case 'z': 2342 return $query->nullDate(false); 2343 2344 case 'Z': 2345 return $query->nullDate(true); 2346 } 2347 2348 // Increment the argument index only if argument specifier not provided. 2349 $index = is_numeric($match[4]) ? (int) $match[4] : $i++; 2350 2351 if (!$index || !isset($args[$index])) 2352 { 2353 // TODO - What to do? sprintf() throws a Warning in these cases. 2354 $replacement = ''; 2355 } 2356 else 2357 { 2358 $replacement = $args[$index]; 2359 } 2360 2361 switch ($match[5]) 2362 { 2363 case 'a': 2364 return 0 + $replacement; 2365 2366 case 'e': 2367 return $query->escape($replacement); 2368 2369 case 'E': 2370 return $query->escape($replacement, true); 2371 2372 case 'n': 2373 return $query->quoteName($replacement); 2374 2375 case 'q': 2376 return $query->quote($replacement); 2377 2378 case 'Q': 2379 return $query->quote($replacement, false); 2380 2381 case 'r': 2382 return $replacement; 2383 2384 // Dates 2385 case 'y': 2386 return $query->year($query->quote($replacement)); 2387 2388 case 'Y': 2389 return $query->year($query->quoteName($replacement)); 2390 2391 case 'm': 2392 return $query->month($query->quote($replacement)); 2393 2394 case 'M': 2395 return $query->month($query->quoteName($replacement)); 2396 2397 case 'd': 2398 return $query->day($query->quote($replacement)); 2399 2400 case 'D': 2401 return $query->day($query->quoteName($replacement)); 2402 2403 case 'h': 2404 return $query->hour($query->quote($replacement)); 2405 2406 case 'H': 2407 return $query->hour($query->quoteName($replacement)); 2408 2409 case 'i': 2410 return $query->minute($query->quote($replacement)); 2411 2412 case 'I': 2413 return $query->minute($query->quoteName($replacement)); 2414 2415 case 's': 2416 return $query->second($query->quote($replacement)); 2417 2418 case 'S': 2419 return $query->second($query->quoteName($replacement)); 2420 } 2421 2422 return ''; 2423 }; 2424 2425 /** 2426 * Regexp to find an replace all tokens. 2427 * Matched fields: 2428 * 0: Full token 2429 * 1: Everything following '%' 2430 * 2: Everything following '%' unless '%' 2431 * 3: Argument specifier and '$' 2432 * 4: Argument specifier 2433 * 5: Type specifier 2434 * 6: '%' if full token is '%%' 2435 */ 2436 return preg_replace_callback('#%(((([\d]+)\$)?([aeEnqQryYmMdDhHiIsStzZ]))|(%))#', $func, $format); 2437 } 2438 2439 /** 2440 * Validate arguments which are passed to selectRowNumber method and set up common variables. 2441 * 2442 * @param string $orderBy An expression of ordering for window function. 2443 * @param string $orderColumnAlias An alias for new ordering column. 2444 * 2445 * @return void 2446 * 2447 * @since 2.0.0 2448 * @throws \RuntimeException 2449 */ 2450 protected function validateRowNumber($orderBy, $orderColumnAlias) 2451 { 2452 if ($this->selectRowNumber) 2453 { 2454 throw new \RuntimeException("Method 'selectRowNumber' can be called only once per instance."); 2455 } 2456 2457 $this->type = 'select'; 2458 2459 $this->selectRowNumber = [ 2460 'orderBy' => $orderBy, 2461 'orderColumnAlias' => $orderColumnAlias, 2462 ]; 2463 } 2464 2465 /** 2466 * Return the number of the current row. 2467 * 2468 * Usage: 2469 * $query->select('id'); 2470 * $query->selectRowNumber('ordering,publish_up DESC', 'new_ordering'); 2471 * $query->from('#__content'); 2472 * 2473 * @param string $orderBy An expression of ordering for window function. 2474 * @param string $orderColumnAlias An alias for new ordering column. 2475 * 2476 * @return $this 2477 * 2478 * @since 2.0.0 2479 * @throws \RuntimeException 2480 */ 2481 public function selectRowNumber($orderBy, $orderColumnAlias) 2482 { 2483 $this->validateRowNumber($orderBy, $orderColumnAlias); 2484 2485 return $this->select("ROW_NUMBER() OVER (ORDER BY $orderBy) AS $orderColumnAlias"); 2486 } 2487 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Wed Sep 7 05:41:13 2022 | Chilli.vc Blog - For Webmaster,Blog-Writer,System Admin and Domainer |