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