1<?php 2 3namespace Doctrine\DBAL\Query; 4 5use Doctrine\DBAL\Connection; 6use Doctrine\DBAL\Driver\ResultStatement; 7use Doctrine\DBAL\Exception; 8use Doctrine\DBAL\ParameterType; 9use Doctrine\DBAL\Query\Expression\CompositeExpression; 10use Doctrine\DBAL\Query\Expression\ExpressionBuilder; 11use Doctrine\DBAL\Types\Type; 12use Doctrine\Deprecations\Deprecation; 13 14use function array_filter; 15use function array_key_exists; 16use function array_keys; 17use function array_unshift; 18use function count; 19use function func_get_args; 20use function func_num_args; 21use function implode; 22use function is_array; 23use function is_object; 24use function key; 25use function strtoupper; 26use function substr; 27 28/** 29 * QueryBuilder class is responsible to dynamically create SQL queries. 30 * 31 * Important: Verify that every feature you use will work with your database vendor. 32 * SQL Query Builder does not attempt to validate the generated SQL at all. 33 * 34 * The query builder does no validation whatsoever if certain features even work with the 35 * underlying database vendor. Limit queries and joins are NOT applied to UPDATE and DELETE statements 36 * even if some vendors such as MySQL support it. 37 */ 38class QueryBuilder 39{ 40 /* 41 * The query types. 42 */ 43 public const SELECT = 0; 44 public const DELETE = 1; 45 public const UPDATE = 2; 46 public const INSERT = 3; 47 48 /* 49 * The builder states. 50 */ 51 public const STATE_DIRTY = 0; 52 public const STATE_CLEAN = 1; 53 54 /** 55 * The DBAL Connection. 56 * 57 * @var Connection 58 */ 59 private $connection; 60 61 /* 62 * The default values of SQL parts collection 63 */ 64 private const SQL_PARTS_DEFAULTS = [ 65 'select' => [], 66 'distinct' => false, 67 'from' => [], 68 'join' => [], 69 'set' => [], 70 'where' => null, 71 'groupBy' => [], 72 'having' => null, 73 'orderBy' => [], 74 'values' => [], 75 ]; 76 77 /** 78 * The array of SQL parts collected. 79 * 80 * @var mixed[] 81 */ 82 private $sqlParts = self::SQL_PARTS_DEFAULTS; 83 84 /** 85 * The complete SQL string for this query. 86 * 87 * @var string|null 88 */ 89 private $sql; 90 91 /** 92 * The query parameters. 93 * 94 * @var array<int, mixed>|array<string, mixed> 95 */ 96 private $params = []; 97 98 /** 99 * The parameter type map of this query. 100 * 101 * @var array<int, int|string|Type|null>|array<string, int|string|Type|null> 102 */ 103 private $paramTypes = []; 104 105 /** 106 * The type of query this is. Can be select, update or delete. 107 * 108 * @var int 109 */ 110 private $type = self::SELECT; 111 112 /** 113 * The state of the query object. Can be dirty or clean. 114 * 115 * @var int 116 */ 117 private $state = self::STATE_CLEAN; 118 119 /** 120 * The index of the first result to retrieve. 121 * 122 * @var int 123 */ 124 private $firstResult = 0; 125 126 /** 127 * The maximum number of results to retrieve or NULL to retrieve all results. 128 * 129 * @var int|null 130 */ 131 private $maxResults; 132 133 /** 134 * The counter of bound parameters used with {@see bindValue). 135 * 136 * @var int 137 */ 138 private $boundCounter = 0; 139 140 /** 141 * Initializes a new <tt>QueryBuilder</tt>. 142 * 143 * @param Connection $connection The DBAL Connection. 144 */ 145 public function __construct(Connection $connection) 146 { 147 $this->connection = $connection; 148 } 149 150 /** 151 * Gets an ExpressionBuilder used for object-oriented construction of query expressions. 152 * This producer method is intended for convenient inline usage. Example: 153 * 154 * <code> 155 * $qb = $conn->createQueryBuilder() 156 * ->select('u') 157 * ->from('users', 'u') 158 * ->where($qb->expr()->eq('u.id', 1)); 159 * </code> 160 * 161 * For more complex expression construction, consider storing the expression 162 * builder object in a local variable. 163 * 164 * @return ExpressionBuilder 165 */ 166 public function expr() 167 { 168 return $this->connection->getExpressionBuilder(); 169 } 170 171 /** 172 * Gets the type of the currently built query. 173 * 174 * @return int 175 */ 176 public function getType() 177 { 178 return $this->type; 179 } 180 181 /** 182 * Gets the associated DBAL Connection for this query builder. 183 * 184 * @return Connection 185 */ 186 public function getConnection() 187 { 188 return $this->connection; 189 } 190 191 /** 192 * Gets the state of this query builder instance. 193 * 194 * @return int Either QueryBuilder::STATE_DIRTY or QueryBuilder::STATE_CLEAN. 195 */ 196 public function getState() 197 { 198 return $this->state; 199 } 200 201 /** 202 * Executes this query using the bound parameters and their types. 203 * 204 * @return ResultStatement|int 205 * 206 * @throws Exception 207 */ 208 public function execute() 209 { 210 if ($this->type === self::SELECT) { 211 return $this->connection->executeQuery($this->getSQL(), $this->params, $this->paramTypes); 212 } 213 214 return $this->connection->executeStatement($this->getSQL(), $this->params, $this->paramTypes); 215 } 216 217 /** 218 * Gets the complete SQL string formed by the current specifications of this QueryBuilder. 219 * 220 * <code> 221 * $qb = $em->createQueryBuilder() 222 * ->select('u') 223 * ->from('User', 'u') 224 * echo $qb->getSQL(); // SELECT u FROM User u 225 * </code> 226 * 227 * @return string The SQL query string. 228 */ 229 public function getSQL() 230 { 231 if ($this->sql !== null && $this->state === self::STATE_CLEAN) { 232 return $this->sql; 233 } 234 235 switch ($this->type) { 236 case self::INSERT: 237 $sql = $this->getSQLForInsert(); 238 break; 239 240 case self::DELETE: 241 $sql = $this->getSQLForDelete(); 242 break; 243 244 case self::UPDATE: 245 $sql = $this->getSQLForUpdate(); 246 break; 247 248 case self::SELECT: 249 default: 250 $sql = $this->getSQLForSelect(); 251 break; 252 } 253 254 $this->state = self::STATE_CLEAN; 255 $this->sql = $sql; 256 257 return $sql; 258 } 259 260 /** 261 * Sets a query parameter for the query being constructed. 262 * 263 * <code> 264 * $qb = $conn->createQueryBuilder() 265 * ->select('u') 266 * ->from('users', 'u') 267 * ->where('u.id = :user_id') 268 * ->setParameter(':user_id', 1); 269 * </code> 270 * 271 * @param int|string $key Parameter position or name 272 * @param mixed $value Parameter value 273 * @param int|string|Type|null $type Parameter type 274 * 275 * @return $this This QueryBuilder instance. 276 */ 277 public function setParameter($key, $value, $type = null) 278 { 279 if ($type !== null) { 280 $this->paramTypes[$key] = $type; 281 } 282 283 $this->params[$key] = $value; 284 285 return $this; 286 } 287 288 /** 289 * Sets a collection of query parameters for the query being constructed. 290 * 291 * <code> 292 * $qb = $conn->createQueryBuilder() 293 * ->select('u') 294 * ->from('users', 'u') 295 * ->where('u.id = :user_id1 OR u.id = :user_id2') 296 * ->setParameters(array( 297 * ':user_id1' => 1, 298 * ':user_id2' => 2 299 * )); 300 * </code> 301 * 302 * @param array<int, mixed>|array<string, mixed> $params Parameters to set 303 * @param array<int, int|string|Type|null>|array<string, int|string|Type|null> $types Parameter types 304 * 305 * @return $this This QueryBuilder instance. 306 */ 307 public function setParameters(array $params, array $types = []) 308 { 309 $this->paramTypes = $types; 310 $this->params = $params; 311 312 return $this; 313 } 314 315 /** 316 * Gets all defined query parameters for the query being constructed indexed by parameter index or name. 317 * 318 * @return array<int, mixed>|array<string, mixed> The currently defined query parameters 319 */ 320 public function getParameters() 321 { 322 return $this->params; 323 } 324 325 /** 326 * Gets a (previously set) query parameter of the query being constructed. 327 * 328 * @param mixed $key The key (index or name) of the bound parameter. 329 * 330 * @return mixed The value of the bound parameter. 331 */ 332 public function getParameter($key) 333 { 334 return $this->params[$key] ?? null; 335 } 336 337 /** 338 * Gets all defined query parameter types for the query being constructed indexed by parameter index or name. 339 * 340 * @return array<int, int|string|Type|null>|array<string, int|string|Type|null> The currently defined 341 * query parameter types 342 */ 343 public function getParameterTypes() 344 { 345 return $this->paramTypes; 346 } 347 348 /** 349 * Gets a (previously set) query parameter type of the query being constructed. 350 * 351 * @param int|string $key The key of the bound parameter type 352 * 353 * @return int|string|Type|null The value of the bound parameter type 354 */ 355 public function getParameterType($key) 356 { 357 return $this->paramTypes[$key] ?? null; 358 } 359 360 /** 361 * Sets the position of the first result to retrieve (the "offset"). 362 * 363 * @param int $firstResult The first result to return. 364 * 365 * @return $this This QueryBuilder instance. 366 */ 367 public function setFirstResult($firstResult) 368 { 369 $this->state = self::STATE_DIRTY; 370 $this->firstResult = $firstResult; 371 372 return $this; 373 } 374 375 /** 376 * Gets the position of the first result the query object was set to retrieve (the "offset"). 377 * 378 * @return int The position of the first result. 379 */ 380 public function getFirstResult() 381 { 382 return $this->firstResult; 383 } 384 385 /** 386 * Sets the maximum number of results to retrieve (the "limit"). 387 * 388 * @param int|null $maxResults The maximum number of results to retrieve or NULL to retrieve all results. 389 * 390 * @return $this This QueryBuilder instance. 391 */ 392 public function setMaxResults($maxResults) 393 { 394 $this->state = self::STATE_DIRTY; 395 $this->maxResults = $maxResults; 396 397 return $this; 398 } 399 400 /** 401 * Gets the maximum number of results the query object was set to retrieve (the "limit"). 402 * Returns NULL if all results will be returned. 403 * 404 * @return int|null The maximum number of results. 405 */ 406 public function getMaxResults() 407 { 408 return $this->maxResults; 409 } 410 411 /** 412 * Either appends to or replaces a single, generic query part. 413 * 414 * The available parts are: 'select', 'from', 'set', 'where', 415 * 'groupBy', 'having' and 'orderBy'. 416 * 417 * @param string $sqlPartName 418 * @param mixed $sqlPart 419 * @param bool $append 420 * 421 * @return $this This QueryBuilder instance. 422 */ 423 public function add($sqlPartName, $sqlPart, $append = false) 424 { 425 $isArray = is_array($sqlPart); 426 $isMultiple = is_array($this->sqlParts[$sqlPartName]); 427 428 if ($isMultiple && ! $isArray) { 429 $sqlPart = [$sqlPart]; 430 } 431 432 $this->state = self::STATE_DIRTY; 433 434 if ($append) { 435 if ( 436 $sqlPartName === 'orderBy' 437 || $sqlPartName === 'groupBy' 438 || $sqlPartName === 'select' 439 || $sqlPartName === 'set' 440 ) { 441 foreach ($sqlPart as $part) { 442 $this->sqlParts[$sqlPartName][] = $part; 443 } 444 } elseif ($isArray && is_array($sqlPart[key($sqlPart)])) { 445 $key = key($sqlPart); 446 $this->sqlParts[$sqlPartName][$key][] = $sqlPart[$key]; 447 } elseif ($isMultiple) { 448 $this->sqlParts[$sqlPartName][] = $sqlPart; 449 } else { 450 $this->sqlParts[$sqlPartName] = $sqlPart; 451 } 452 453 return $this; 454 } 455 456 $this->sqlParts[$sqlPartName] = $sqlPart; 457 458 return $this; 459 } 460 461 /** 462 * Specifies an item that is to be returned in the query result. 463 * Replaces any previously specified selections, if any. 464 * 465 * USING AN ARRAY ARGUMENT IS DEPRECATED. Pass each value as an individual argument. 466 * 467 * <code> 468 * $qb = $conn->createQueryBuilder() 469 * ->select('u.id', 'p.id') 470 * ->from('users', 'u') 471 * ->leftJoin('u', 'phonenumbers', 'p', 'u.id = p.user_id'); 472 * </code> 473 * 474 * @param string|string[]|null $select The selection expression. USING AN ARRAY OR NULL IS DEPRECATED. 475 * Pass each value as an individual argument. 476 * 477 * @return $this This QueryBuilder instance. 478 */ 479 public function select($select = null/*, string ...$selects*/) 480 { 481 $this->type = self::SELECT; 482 483 if (empty($select)) { 484 return $this; 485 } 486 487 if (is_array($select)) { 488 Deprecation::trigger( 489 'doctrine/dbal', 490 'https://github.com/doctrine/dbal/issues/3837', 491 'Passing an array for the first argument to QueryBuilder::select is deprecated, ' . 492 'pass each value as an individual variadic argument instead.' 493 ); 494 } 495 496 $selects = is_array($select) ? $select : func_get_args(); 497 498 return $this->add('select', $selects); 499 } 500 501 /** 502 * Adds DISTINCT to the query. 503 * 504 * <code> 505 * $qb = $conn->createQueryBuilder() 506 * ->select('u.id') 507 * ->distinct() 508 * ->from('users', 'u') 509 * </code> 510 * 511 * @return $this This QueryBuilder instance. 512 */ 513 public function distinct(): self 514 { 515 $this->sqlParts['distinct'] = true; 516 517 return $this; 518 } 519 520 /** 521 * Adds an item that is to be returned in the query result. 522 * 523 * USING AN ARRAY ARGUMENT IS DEPRECATED. Pass each value as an individual argument. 524 * 525 * <code> 526 * $qb = $conn->createQueryBuilder() 527 * ->select('u.id') 528 * ->addSelect('p.id') 529 * ->from('users', 'u') 530 * ->leftJoin('u', 'phonenumbers', 'u.id = p.user_id'); 531 * </code> 532 * 533 * @param string|string[]|null $select The selection expression. USING AN ARRAY OR NULL IS DEPRECATED. 534 * Pass each value as an individual argument. 535 * 536 * @return $this This QueryBuilder instance. 537 */ 538 public function addSelect($select = null/*, string ...$selects*/) 539 { 540 $this->type = self::SELECT; 541 542 if (empty($select)) { 543 return $this; 544 } 545 546 if (is_array($select)) { 547 Deprecation::trigger( 548 'doctrine/dbal', 549 'https://github.com/doctrine/dbal/issues/3837', 550 'Passing an array for the first argument to QueryBuilder::addSelect is deprecated, ' . 551 'pass each value as an individual variadic argument instead.' 552 ); 553 } 554 555 $selects = is_array($select) ? $select : func_get_args(); 556 557 return $this->add('select', $selects, true); 558 } 559 560 /** 561 * Turns the query being built into a bulk delete query that ranges over 562 * a certain table. 563 * 564 * <code> 565 * $qb = $conn->createQueryBuilder() 566 * ->delete('users', 'u') 567 * ->where('u.id = :user_id') 568 * ->setParameter(':user_id', 1); 569 * </code> 570 * 571 * @param string $delete The table whose rows are subject to the deletion. 572 * @param string $alias The table alias used in the constructed query. 573 * 574 * @return $this This QueryBuilder instance. 575 */ 576 public function delete($delete = null, $alias = null) 577 { 578 $this->type = self::DELETE; 579 580 if (! $delete) { 581 return $this; 582 } 583 584 return $this->add('from', [ 585 'table' => $delete, 586 'alias' => $alias, 587 ]); 588 } 589 590 /** 591 * Turns the query being built into a bulk update query that ranges over 592 * a certain table 593 * 594 * <code> 595 * $qb = $conn->createQueryBuilder() 596 * ->update('counters', 'c') 597 * ->set('c.value', 'c.value + 1') 598 * ->where('c.id = ?'); 599 * </code> 600 * 601 * @param string $update The table whose rows are subject to the update. 602 * @param string $alias The table alias used in the constructed query. 603 * 604 * @return $this This QueryBuilder instance. 605 */ 606 public function update($update = null, $alias = null) 607 { 608 $this->type = self::UPDATE; 609 610 if (! $update) { 611 return $this; 612 } 613 614 return $this->add('from', [ 615 'table' => $update, 616 'alias' => $alias, 617 ]); 618 } 619 620 /** 621 * Turns the query being built into an insert query that inserts into 622 * a certain table 623 * 624 * <code> 625 * $qb = $conn->createQueryBuilder() 626 * ->insert('users') 627 * ->values( 628 * array( 629 * 'name' => '?', 630 * 'password' => '?' 631 * ) 632 * ); 633 * </code> 634 * 635 * @param string $insert The table into which the rows should be inserted. 636 * 637 * @return $this This QueryBuilder instance. 638 */ 639 public function insert($insert = null) 640 { 641 $this->type = self::INSERT; 642 643 if (! $insert) { 644 return $this; 645 } 646 647 return $this->add('from', ['table' => $insert]); 648 } 649 650 /** 651 * Creates and adds a query root corresponding to the table identified by the 652 * given alias, forming a cartesian product with any existing query roots. 653 * 654 * <code> 655 * $qb = $conn->createQueryBuilder() 656 * ->select('u.id') 657 * ->from('users', 'u') 658 * </code> 659 * 660 * @param string $from The table. 661 * @param string|null $alias The alias of the table. 662 * 663 * @return $this This QueryBuilder instance. 664 */ 665 public function from($from, $alias = null) 666 { 667 return $this->add('from', [ 668 'table' => $from, 669 'alias' => $alias, 670 ], true); 671 } 672 673 /** 674 * Creates and adds a join to the query. 675 * 676 * <code> 677 * $qb = $conn->createQueryBuilder() 678 * ->select('u.name') 679 * ->from('users', 'u') 680 * ->join('u', 'phonenumbers', 'p', 'p.is_primary = 1'); 681 * </code> 682 * 683 * @param string $fromAlias The alias that points to a from clause. 684 * @param string $join The table name to join. 685 * @param string $alias The alias of the join table. 686 * @param string $condition The condition for the join. 687 * 688 * @return $this This QueryBuilder instance. 689 */ 690 public function join($fromAlias, $join, $alias, $condition = null) 691 { 692 return $this->innerJoin($fromAlias, $join, $alias, $condition); 693 } 694 695 /** 696 * Creates and adds a join to the query. 697 * 698 * <code> 699 * $qb = $conn->createQueryBuilder() 700 * ->select('u.name') 701 * ->from('users', 'u') 702 * ->innerJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1'); 703 * </code> 704 * 705 * @param string $fromAlias The alias that points to a from clause. 706 * @param string $join The table name to join. 707 * @param string $alias The alias of the join table. 708 * @param string $condition The condition for the join. 709 * 710 * @return $this This QueryBuilder instance. 711 */ 712 public function innerJoin($fromAlias, $join, $alias, $condition = null) 713 { 714 return $this->add('join', [ 715 $fromAlias => [ 716 'joinType' => 'inner', 717 'joinTable' => $join, 718 'joinAlias' => $alias, 719 'joinCondition' => $condition, 720 ], 721 ], true); 722 } 723 724 /** 725 * Creates and adds a left join to the query. 726 * 727 * <code> 728 * $qb = $conn->createQueryBuilder() 729 * ->select('u.name') 730 * ->from('users', 'u') 731 * ->leftJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1'); 732 * </code> 733 * 734 * @param string $fromAlias The alias that points to a from clause. 735 * @param string $join The table name to join. 736 * @param string $alias The alias of the join table. 737 * @param string $condition The condition for the join. 738 * 739 * @return $this This QueryBuilder instance. 740 */ 741 public function leftJoin($fromAlias, $join, $alias, $condition = null) 742 { 743 return $this->add('join', [ 744 $fromAlias => [ 745 'joinType' => 'left', 746 'joinTable' => $join, 747 'joinAlias' => $alias, 748 'joinCondition' => $condition, 749 ], 750 ], true); 751 } 752 753 /** 754 * Creates and adds a right join to the query. 755 * 756 * <code> 757 * $qb = $conn->createQueryBuilder() 758 * ->select('u.name') 759 * ->from('users', 'u') 760 * ->rightJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1'); 761 * </code> 762 * 763 * @param string $fromAlias The alias that points to a from clause. 764 * @param string $join The table name to join. 765 * @param string $alias The alias of the join table. 766 * @param string $condition The condition for the join. 767 * 768 * @return $this This QueryBuilder instance. 769 */ 770 public function rightJoin($fromAlias, $join, $alias, $condition = null) 771 { 772 return $this->add('join', [ 773 $fromAlias => [ 774 'joinType' => 'right', 775 'joinTable' => $join, 776 'joinAlias' => $alias, 777 'joinCondition' => $condition, 778 ], 779 ], true); 780 } 781 782 /** 783 * Sets a new value for a column in a bulk update query. 784 * 785 * <code> 786 * $qb = $conn->createQueryBuilder() 787 * ->update('counters', 'c') 788 * ->set('c.value', 'c.value + 1') 789 * ->where('c.id = ?'); 790 * </code> 791 * 792 * @param string $key The column to set. 793 * @param string $value The value, expression, placeholder, etc. 794 * 795 * @return $this This QueryBuilder instance. 796 */ 797 public function set($key, $value) 798 { 799 return $this->add('set', $key . ' = ' . $value, true); 800 } 801 802 /** 803 * Specifies one or more restrictions to the query result. 804 * Replaces any previously specified restrictions, if any. 805 * 806 * <code> 807 * $qb = $conn->createQueryBuilder() 808 * ->select('c.value') 809 * ->from('counters', 'c') 810 * ->where('c.id = ?'); 811 * 812 * // You can optionally programatically build and/or expressions 813 * $qb = $conn->createQueryBuilder(); 814 * 815 * $or = $qb->expr()->orx(); 816 * $or->add($qb->expr()->eq('c.id', 1)); 817 * $or->add($qb->expr()->eq('c.id', 2)); 818 * 819 * $qb->update('counters', 'c') 820 * ->set('c.value', 'c.value + 1') 821 * ->where($or); 822 * </code> 823 * 824 * @param mixed $predicates The restriction predicates. 825 * 826 * @return $this This QueryBuilder instance. 827 */ 828 public function where($predicates) 829 { 830 if (! (func_num_args() === 1 && $predicates instanceof CompositeExpression)) { 831 $predicates = CompositeExpression::and(...func_get_args()); 832 } 833 834 return $this->add('where', $predicates); 835 } 836 837 /** 838 * Adds one or more restrictions to the query results, forming a logical 839 * conjunction with any previously specified restrictions. 840 * 841 * <code> 842 * $qb = $conn->createQueryBuilder() 843 * ->select('u') 844 * ->from('users', 'u') 845 * ->where('u.username LIKE ?') 846 * ->andWhere('u.is_active = 1'); 847 * </code> 848 * 849 * @see where() 850 * 851 * @param mixed $where The query restrictions. 852 * 853 * @return $this This QueryBuilder instance. 854 */ 855 public function andWhere($where) 856 { 857 $args = func_get_args(); 858 $args = array_filter($args); // https://github.com/doctrine/dbal/issues/4282 859 $where = $this->getQueryPart('where'); 860 861 if ($where instanceof CompositeExpression && $where->getType() === CompositeExpression::TYPE_AND) { 862 if (count($args) > 0) { 863 $where = $where->with(...$args); 864 } 865 } else { 866 array_unshift($args, $where); 867 $where = CompositeExpression::and(...$args); 868 } 869 870 return $this->add('where', $where, true); 871 } 872 873 /** 874 * Adds one or more restrictions to the query results, forming a logical 875 * disjunction with any previously specified restrictions. 876 * 877 * <code> 878 * $qb = $em->createQueryBuilder() 879 * ->select('u.name') 880 * ->from('users', 'u') 881 * ->where('u.id = 1') 882 * ->orWhere('u.id = 2'); 883 * </code> 884 * 885 * @see where() 886 * 887 * @param mixed $where The WHERE statement. 888 * 889 * @return $this This QueryBuilder instance. 890 */ 891 public function orWhere($where) 892 { 893 $args = func_get_args(); 894 $args = array_filter($args); // https://github.com/doctrine/dbal/issues/4282 895 $where = $this->getQueryPart('where'); 896 897 if ($where instanceof CompositeExpression && $where->getType() === CompositeExpression::TYPE_OR) { 898 if (count($args) > 0) { 899 $where = $where->with(...$args); 900 } 901 } else { 902 array_unshift($args, $where); 903 $where = CompositeExpression::or(...$args); 904 } 905 906 return $this->add('where', $where, true); 907 } 908 909 /** 910 * Specifies a grouping over the results of the query. 911 * Replaces any previously specified groupings, if any. 912 * 913 * USING AN ARRAY ARGUMENT IS DEPRECATED. Pass each value as an individual argument. 914 * 915 * <code> 916 * $qb = $conn->createQueryBuilder() 917 * ->select('u.name') 918 * ->from('users', 'u') 919 * ->groupBy('u.id'); 920 * </code> 921 * 922 * @param string|string[] $groupBy The grouping expression. USING AN ARRAY IS DEPRECATED. 923 * Pass each value as an individual argument. 924 * 925 * @return $this This QueryBuilder instance. 926 */ 927 public function groupBy($groupBy/*, string ...$groupBys*/) 928 { 929 if (empty($groupBy)) { 930 return $this; 931 } 932 933 if (is_array($groupBy)) { 934 Deprecation::trigger( 935 'doctrine/dbal', 936 'https://github.com/doctrine/dbal/issues/3837', 937 'Passing an array for the first argument to QueryBuilder::groupBy is deprecated, ' . 938 'pass each value as an individual variadic argument instead.' 939 ); 940 } 941 942 $groupBy = is_array($groupBy) ? $groupBy : func_get_args(); 943 944 return $this->add('groupBy', $groupBy, false); 945 } 946 947 /** 948 * Adds a grouping expression to the query. 949 * 950 * USING AN ARRAY ARGUMENT IS DEPRECATED. Pass each value as an individual argument. 951 * 952 * <code> 953 * $qb = $conn->createQueryBuilder() 954 * ->select('u.name') 955 * ->from('users', 'u') 956 * ->groupBy('u.lastLogin') 957 * ->addGroupBy('u.createdAt'); 958 * </code> 959 * 960 * @param string|string[] $groupBy The grouping expression. USING AN ARRAY IS DEPRECATED. 961 * Pass each value as an individual argument. 962 * 963 * @return $this This QueryBuilder instance. 964 */ 965 public function addGroupBy($groupBy/*, string ...$groupBys*/) 966 { 967 if (empty($groupBy)) { 968 return $this; 969 } 970 971 if (is_array($groupBy)) { 972 Deprecation::trigger( 973 'doctrine/dbal', 974 'https://github.com/doctrine/dbal/issues/3837', 975 'Passing an array for the first argument to QueryBuilder::addGroupBy is deprecated, ' . 976 'pass each value as an individual variadic argument instead.' 977 ); 978 } 979 980 $groupBy = is_array($groupBy) ? $groupBy : func_get_args(); 981 982 return $this->add('groupBy', $groupBy, true); 983 } 984 985 /** 986 * Sets a value for a column in an insert query. 987 * 988 * <code> 989 * $qb = $conn->createQueryBuilder() 990 * ->insert('users') 991 * ->values( 992 * array( 993 * 'name' => '?' 994 * ) 995 * ) 996 * ->setValue('password', '?'); 997 * </code> 998 * 999 * @param string $column The column into which the value should be inserted. 1000 * @param string $value The value that should be inserted into the column. 1001 * 1002 * @return $this This QueryBuilder instance. 1003 */ 1004 public function setValue($column, $value) 1005 { 1006 $this->sqlParts['values'][$column] = $value; 1007 1008 return $this; 1009 } 1010 1011 /** 1012 * Specifies values for an insert query indexed by column names. 1013 * Replaces any previous values, if any. 1014 * 1015 * <code> 1016 * $qb = $conn->createQueryBuilder() 1017 * ->insert('users') 1018 * ->values( 1019 * array( 1020 * 'name' => '?', 1021 * 'password' => '?' 1022 * ) 1023 * ); 1024 * </code> 1025 * 1026 * @param mixed[] $values The values to specify for the insert query indexed by column names. 1027 * 1028 * @return $this This QueryBuilder instance. 1029 */ 1030 public function values(array $values) 1031 { 1032 return $this->add('values', $values); 1033 } 1034 1035 /** 1036 * Specifies a restriction over the groups of the query. 1037 * Replaces any previous having restrictions, if any. 1038 * 1039 * @param mixed $having The restriction over the groups. 1040 * 1041 * @return $this This QueryBuilder instance. 1042 */ 1043 public function having($having) 1044 { 1045 if (! (func_num_args() === 1 && $having instanceof CompositeExpression)) { 1046 $having = CompositeExpression::and(...func_get_args()); 1047 } 1048 1049 return $this->add('having', $having); 1050 } 1051 1052 /** 1053 * Adds a restriction over the groups of the query, forming a logical 1054 * conjunction with any existing having restrictions. 1055 * 1056 * @param mixed $having The restriction to append. 1057 * 1058 * @return $this This QueryBuilder instance. 1059 */ 1060 public function andHaving($having) 1061 { 1062 $args = func_get_args(); 1063 $args = array_filter($args); // https://github.com/doctrine/dbal/issues/4282 1064 $having = $this->getQueryPart('having'); 1065 1066 if ($having instanceof CompositeExpression && $having->getType() === CompositeExpression::TYPE_AND) { 1067 $having = $having->with(...$args); 1068 } else { 1069 array_unshift($args, $having); 1070 $having = CompositeExpression::and(...$args); 1071 } 1072 1073 return $this->add('having', $having); 1074 } 1075 1076 /** 1077 * Adds a restriction over the groups of the query, forming a logical 1078 * disjunction with any existing having restrictions. 1079 * 1080 * @param mixed $having The restriction to add. 1081 * 1082 * @return $this This QueryBuilder instance. 1083 */ 1084 public function orHaving($having) 1085 { 1086 $args = func_get_args(); 1087 $args = array_filter($args); // https://github.com/doctrine/dbal/issues/4282 1088 $having = $this->getQueryPart('having'); 1089 1090 if ($having instanceof CompositeExpression && $having->getType() === CompositeExpression::TYPE_OR) { 1091 $having = $having->with(...$args); 1092 } else { 1093 array_unshift($args, $having); 1094 $having = CompositeExpression::or(...$args); 1095 } 1096 1097 return $this->add('having', $having); 1098 } 1099 1100 /** 1101 * Specifies an ordering for the query results. 1102 * Replaces any previously specified orderings, if any. 1103 * 1104 * @param string $sort The ordering expression. 1105 * @param string $order The ordering direction. 1106 * 1107 * @return $this This QueryBuilder instance. 1108 */ 1109 public function orderBy($sort, $order = null) 1110 { 1111 return $this->add('orderBy', $sort . ' ' . (! $order ? 'ASC' : $order), false); 1112 } 1113 1114 /** 1115 * Adds an ordering to the query results. 1116 * 1117 * @param string $sort The ordering expression. 1118 * @param string $order The ordering direction. 1119 * 1120 * @return $this This QueryBuilder instance. 1121 */ 1122 public function addOrderBy($sort, $order = null) 1123 { 1124 return $this->add('orderBy', $sort . ' ' . (! $order ? 'ASC' : $order), true); 1125 } 1126 1127 /** 1128 * Gets a query part by its name. 1129 * 1130 * @param string $queryPartName 1131 * 1132 * @return mixed 1133 */ 1134 public function getQueryPart($queryPartName) 1135 { 1136 return $this->sqlParts[$queryPartName]; 1137 } 1138 1139 /** 1140 * Gets all query parts. 1141 * 1142 * @return mixed[] 1143 */ 1144 public function getQueryParts() 1145 { 1146 return $this->sqlParts; 1147 } 1148 1149 /** 1150 * Resets SQL parts. 1151 * 1152 * @param string[]|null $queryPartNames 1153 * 1154 * @return $this This QueryBuilder instance. 1155 */ 1156 public function resetQueryParts($queryPartNames = null) 1157 { 1158 if ($queryPartNames === null) { 1159 $queryPartNames = array_keys($this->sqlParts); 1160 } 1161 1162 foreach ($queryPartNames as $queryPartName) { 1163 $this->resetQueryPart($queryPartName); 1164 } 1165 1166 return $this; 1167 } 1168 1169 /** 1170 * Resets a single SQL part. 1171 * 1172 * @param string $queryPartName 1173 * 1174 * @return $this This QueryBuilder instance. 1175 */ 1176 public function resetQueryPart($queryPartName) 1177 { 1178 $this->sqlParts[$queryPartName] = self::SQL_PARTS_DEFAULTS[$queryPartName]; 1179 1180 $this->state = self::STATE_DIRTY; 1181 1182 return $this; 1183 } 1184 1185 /** 1186 * @return string 1187 * 1188 * @throws QueryException 1189 */ 1190 private function getSQLForSelect() 1191 { 1192 $query = 'SELECT ' . ($this->sqlParts['distinct'] ? 'DISTINCT ' : '') . 1193 implode(', ', $this->sqlParts['select']); 1194 1195 $query .= ($this->sqlParts['from'] ? ' FROM ' . implode(', ', $this->getFromClauses()) : '') 1196 . ($this->sqlParts['where'] !== null ? ' WHERE ' . ((string) $this->sqlParts['where']) : '') 1197 . ($this->sqlParts['groupBy'] ? ' GROUP BY ' . implode(', ', $this->sqlParts['groupBy']) : '') 1198 . ($this->sqlParts['having'] !== null ? ' HAVING ' . ((string) $this->sqlParts['having']) : '') 1199 . ($this->sqlParts['orderBy'] ? ' ORDER BY ' . implode(', ', $this->sqlParts['orderBy']) : ''); 1200 1201 if ($this->isLimitQuery()) { 1202 return $this->connection->getDatabasePlatform()->modifyLimitQuery( 1203 $query, 1204 $this->maxResults, 1205 $this->firstResult 1206 ); 1207 } 1208 1209 return $query; 1210 } 1211 1212 /** 1213 * @return string[] 1214 */ 1215 private function getFromClauses() 1216 { 1217 $fromClauses = []; 1218 $knownAliases = []; 1219 1220 // Loop through all FROM clauses 1221 foreach ($this->sqlParts['from'] as $from) { 1222 if ($from['alias'] === null) { 1223 $tableSql = $from['table']; 1224 $tableReference = $from['table']; 1225 } else { 1226 $tableSql = $from['table'] . ' ' . $from['alias']; 1227 $tableReference = $from['alias']; 1228 } 1229 1230 $knownAliases[$tableReference] = true; 1231 1232 $fromClauses[$tableReference] = $tableSql . $this->getSQLForJoins($tableReference, $knownAliases); 1233 } 1234 1235 $this->verifyAllAliasesAreKnown($knownAliases); 1236 1237 return $fromClauses; 1238 } 1239 1240 /** 1241 * @param array<string,true> $knownAliases 1242 * 1243 * @throws QueryException 1244 */ 1245 private function verifyAllAliasesAreKnown(array $knownAliases): void 1246 { 1247 foreach ($this->sqlParts['join'] as $fromAlias => $joins) { 1248 if (! isset($knownAliases[$fromAlias])) { 1249 throw QueryException::unknownAlias($fromAlias, array_keys($knownAliases)); 1250 } 1251 } 1252 } 1253 1254 /** 1255 * @return bool 1256 */ 1257 private function isLimitQuery() 1258 { 1259 return $this->maxResults !== null || $this->firstResult !== 0; 1260 } 1261 1262 /** 1263 * Converts this instance into an INSERT string in SQL. 1264 * 1265 * @return string 1266 */ 1267 private function getSQLForInsert() 1268 { 1269 return 'INSERT INTO ' . $this->sqlParts['from']['table'] . 1270 ' (' . implode(', ', array_keys($this->sqlParts['values'])) . ')' . 1271 ' VALUES(' . implode(', ', $this->sqlParts['values']) . ')'; 1272 } 1273 1274 /** 1275 * Converts this instance into an UPDATE string in SQL. 1276 * 1277 * @return string 1278 */ 1279 private function getSQLForUpdate() 1280 { 1281 $table = $this->sqlParts['from']['table'] 1282 . ($this->sqlParts['from']['alias'] ? ' ' . $this->sqlParts['from']['alias'] : ''); 1283 1284 return 'UPDATE ' . $table 1285 . ' SET ' . implode(', ', $this->sqlParts['set']) 1286 . ($this->sqlParts['where'] !== null ? ' WHERE ' . ((string) $this->sqlParts['where']) : ''); 1287 } 1288 1289 /** 1290 * Converts this instance into a DELETE string in SQL. 1291 * 1292 * @return string 1293 */ 1294 private function getSQLForDelete() 1295 { 1296 $table = $this->sqlParts['from']['table'] 1297 . ($this->sqlParts['from']['alias'] ? ' ' . $this->sqlParts['from']['alias'] : ''); 1298 1299 return 'DELETE FROM ' . $table 1300 . ($this->sqlParts['where'] !== null ? ' WHERE ' . ((string) $this->sqlParts['where']) : ''); 1301 } 1302 1303 /** 1304 * Gets a string representation of this QueryBuilder which corresponds to 1305 * the final SQL query being constructed. 1306 * 1307 * @return string The string representation of this QueryBuilder. 1308 */ 1309 public function __toString() 1310 { 1311 return $this->getSQL(); 1312 } 1313 1314 /** 1315 * Creates a new named parameter and bind the value $value to it. 1316 * 1317 * This method provides a shortcut for PDOStatement::bindValue 1318 * when using prepared statements. 1319 * 1320 * The parameter $value specifies the value that you want to bind. If 1321 * $placeholder is not provided bindValue() will automatically create a 1322 * placeholder for you. An automatic placeholder will be of the name 1323 * ':dcValue1', ':dcValue2' etc. 1324 * 1325 * For more information see {@link http://php.net/pdostatement-bindparam} 1326 * 1327 * Example: 1328 * <code> 1329 * $value = 2; 1330 * $q->eq( 'id', $q->bindValue( $value ) ); 1331 * $stmt = $q->executeQuery(); // executed with 'id = 2' 1332 * </code> 1333 * 1334 * @link http://www.zetacomponents.org 1335 * 1336 * @param mixed $value 1337 * @param int|string|Type|null $type 1338 * @param string $placeHolder The name to bind with. The string must start with a colon ':'. 1339 * 1340 * @return string the placeholder name used. 1341 */ 1342 public function createNamedParameter($value, $type = ParameterType::STRING, $placeHolder = null) 1343 { 1344 if ($placeHolder === null) { 1345 $this->boundCounter++; 1346 $placeHolder = ':dcValue' . $this->boundCounter; 1347 } 1348 1349 $this->setParameter(substr($placeHolder, 1), $value, $type); 1350 1351 return $placeHolder; 1352 } 1353 1354 /** 1355 * Creates a new positional parameter and bind the given value to it. 1356 * 1357 * Attention: If you are using positional parameters with the query builder you have 1358 * to be very careful to bind all parameters in the order they appear in the SQL 1359 * statement , otherwise they get bound in the wrong order which can lead to serious 1360 * bugs in your code. 1361 * 1362 * Example: 1363 * <code> 1364 * $qb = $conn->createQueryBuilder(); 1365 * $qb->select('u.*') 1366 * ->from('users', 'u') 1367 * ->where('u.username = ' . $qb->createPositionalParameter('Foo', ParameterType::STRING)) 1368 * ->orWhere('u.username = ' . $qb->createPositionalParameter('Bar', ParameterType::STRING)) 1369 * </code> 1370 * 1371 * @param mixed $value 1372 * @param int|string|Type|null $type 1373 * 1374 * @return string 1375 */ 1376 public function createPositionalParameter($value, $type = ParameterType::STRING) 1377 { 1378 $this->boundCounter++; 1379 $this->setParameter($this->boundCounter, $value, $type); 1380 1381 return '?'; 1382 } 1383 1384 /** 1385 * @param string $fromAlias 1386 * @param array<string,true> $knownAliases 1387 * 1388 * @return string 1389 * 1390 * @throws QueryException 1391 */ 1392 private function getSQLForJoins($fromAlias, array &$knownAliases) 1393 { 1394 $sql = ''; 1395 1396 if (isset($this->sqlParts['join'][$fromAlias])) { 1397 foreach ($this->sqlParts['join'][$fromAlias] as $join) { 1398 if (array_key_exists($join['joinAlias'], $knownAliases)) { 1399 throw QueryException::nonUniqueAlias($join['joinAlias'], array_keys($knownAliases)); 1400 } 1401 1402 $sql .= ' ' . strtoupper($join['joinType']) 1403 . ' JOIN ' . $join['joinTable'] . ' ' . $join['joinAlias']; 1404 if ($join['joinCondition'] !== null) { 1405 $sql .= ' ON ' . $join['joinCondition']; 1406 } 1407 1408 $knownAliases[$join['joinAlias']] = true; 1409 } 1410 1411 foreach ($this->sqlParts['join'][$fromAlias] as $join) { 1412 $sql .= $this->getSQLForJoins($join['joinAlias'], $knownAliases); 1413 } 1414 } 1415 1416 return $sql; 1417 } 1418 1419 /** 1420 * Deep clone of all expression objects in the SQL parts. 1421 * 1422 * @return void 1423 */ 1424 public function __clone() 1425 { 1426 foreach ($this->sqlParts as $part => $elements) { 1427 if (is_array($this->sqlParts[$part])) { 1428 foreach ($this->sqlParts[$part] as $idx => $element) { 1429 if (! is_object($element)) { 1430 continue; 1431 } 1432 1433 $this->sqlParts[$part][$idx] = clone $element; 1434 } 1435 } elseif (is_object($elements)) { 1436 $this->sqlParts[$part] = clone $elements; 1437 } 1438 } 1439 1440 foreach ($this->params as $name => $param) { 1441 if (! is_object($param)) { 1442 continue; 1443 } 1444 1445 $this->params[$name] = clone $param; 1446 } 1447 } 1448} 1449