typo3/sysext/core/Classes/Database/Query/QueryBuilder.php

   1 <?php
   2 declare(strict_types=1);
   3 namespace TYPO3\CMS\Core\Database\Query;
   4
   5 /*
   6  * This file is part of the TYPO3 CMS project.
   7  *
   8  * It is free software; you can redistribute it and/or modify it under
   9  * the terms of the GNU General Public License, either version 2
  10  * of the License, or any later version.
  11  *
  12  * For the full copyright and license information, please read the
  13  * LICENSE.txt file that was distributed with this source code.
  14  *
  15  * The TYPO3 project - inspiring people to share!
  16  */
  17
  18 use Doctrine\DBAL\Platforms\SQLServerPlatform;
  19 use Doctrine\DBAL\Query\Expression\CompositeExpression;
  20 use TYPO3\CMS\Core\Database\Connection;
  21 use TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder;
  22 use TYPO3\CMS\Core\Database\Query\Restriction\DefaultRestrictionContainer;
  23 use TYPO3\CMS\Core\Database\Query\Restriction\QueryRestrictionContainerInterface;
  24 use TYPO3\CMS\Core\Utility\GeneralUtility;
  25
  26 /**
  27  * Object oriented approach to building SQL queries.
  28  *
  29  * It's a facade to the Doctrine DBAL QueryBuilder that implements PHP7 type hinting and automatic
  30  * quoting of table and column names.
  31  *
  32  * <code>
  33  * $query->select('aField', 'anotherField')
  34  *       ->from('aTable')
  35  *       ->where($query->expr()->eq('aField', 1))
  36  *       ->andWhere($query->expr()->gte('anotherField',10'))
  37  *       ->execute()
  38  * </code>
  39  *
  40  * Additional functionality included is support for COUNT() and TRUNCATE() statements.
  41  */
  42 class QueryBuilder
  43 {
  44     /**
  45      * The DBAL Connection.
  46      *
  47      * @var Connection
  48      */
  49     protected $connection;
  50
  51     /**
  52      * @var \Doctrine\DBAL\Query\QueryBuilder
  53      */
  54     protected $concreteQueryBuilder;
  55
  56     /**
  57      * @var QueryRestrictionContainerInterface
  58      */
  59     protected $restrictionContainer;
  60
  61     /**
  62      * Initializes a new QueryBuilder.
  63      *
  64      * @param Connection $connection The DBAL Connection.
  65      * @param QueryRestrictionContainerInterface $restrictionContainer
  66      * @param \Doctrine\DBAL\Query\QueryBuilder $concreteQueryBuilder
  67      */
  68     public function __construct(
  69         Connection $connection,
  70         QueryRestrictionContainerInterface $restrictionContainer = null,
  71         \Doctrine\DBAL\Query\QueryBuilder $concreteQueryBuilder = null
  72     ) {
  73         $this->connection = $connection;
  74         $this->restrictionContainer = $restrictionContainer ?: GeneralUtility::makeInstance(DefaultRestrictionContainer::class);
  75         $this->concreteQueryBuilder = $concreteQueryBuilder ?: GeneralUtility::makeInstance(\Doctrine\DBAL\Query\QueryBuilder::class, $connection);
  76     }
  77
  78     /**
  79      * @return QueryRestrictionContainerInterface
  80      */
  81     public function getRestrictions()
  82     {
  83         return $this->restrictionContainer;
  84     }
  85
  86     /**
  87      * @param QueryRestrictionContainerInterface $restrictionContainer
  88      */
  89     public function setRestrictions(QueryRestrictionContainerInterface $restrictionContainer)
  90     {
  91         $this->restrictionContainer = $restrictionContainer;
  92     }
  93
  94     /**
  95      * Re-apply default restrictions
  96      */
  97     public function resetRestrictions()
  98     {
  99         $this->restrictionContainer = GeneralUtility::makeInstance(DefaultRestrictionContainer::class);
 100     }
 101
 102     /**
 103      * Gets an ExpressionBuilder used for object-oriented construction of query expressions.
 104      * This producer method is intended for convenient inline usage. Example:
 105      *
 106      * For more complex expression construction, consider storing the expression
 107      * builder object in a local variable.
 108      *
 109      * @return ExpressionBuilder
 110      */
 111     public function expr(): ExpressionBuilder
 112     {
 113         return $this->connection->getExpressionBuilder();
 114     }
 115
 116     /**
 117      * Gets the type of the currently built query.
 118      *
 119      * @return int
 120      * @internal
 121      */
 122     public function getType(): int
 123     {
 124         return $this->concreteQueryBuilder->getType();
 125     }
 126
 127     /**
 128      * Gets the associated DBAL Connection for this query builder.
 129      *
 130      * @return Connection
 131      */
 132     public function getConnection(): Connection
 133     {
 134         return $this->connection;
 135     }
 136
 137     /**
 138      * Gets the state of this query builder instance.
 139      *
 140      * @return int Either QueryBuilder::STATE_DIRTY or QueryBuilder::STATE_CLEAN.
 141      * @internal
 142      */
 143     public function getState(): int
 144     {
 145         return $this->concreteQueryBuilder->getState();
 146     }
 147
 148     /**
 149      * Gets the concrete implementation of the query builder
 150      *
 151      * @return \Doctrine\DBAL\Query\QueryBuilder
 152      * @internal
 153      */
 154     public function getConcreteQueryBuilder(): \Doctrine\DBAL\Query\QueryBuilder
 155     {
 156         return $this->concreteQueryBuilder;
 157     }
 158
 159     /**
 160      * Executes this query using the bound parameters and their types.
 161      *
 162      * @return \Doctrine\DBAL\Driver\Statement|int
 163      */
 164     public function execute()
 165     {
 166         if ($this->getType() !== \Doctrine\DBAL\Query\QueryBuilder::SELECT) {
 167             return $this->concreteQueryBuilder->execute();
 168         }
 169
 170         // Set additional query restrictions
 171         $originalWhereConditions = $this->addAdditionalWhereConditions();
 172
 173         $result = $this->concreteQueryBuilder->execute();
 174
 175         // Restore the original query conditions in case the user keeps
 176         // on modifying the state.
 177         $this->concreteQueryBuilder->add('where', $originalWhereConditions, false);
 178
 179         return $result;
 180     }
 181
 182     /**
 183      * Gets the complete SQL string formed by the current specifications of this QueryBuilder.
 184      *
 185      * If the statement is a SELECT TYPE query restrictions based on TCA settings will
 186      * automatically be applied based on the current QuerySettings.
 187      *
 188      * @return string The SQL query string.
 189      */
 190     public function getSQL(): string
 191     {
 192         if ($this->getType() !== \Doctrine\DBAL\Query\QueryBuilder::SELECT) {
 193             return $this->concreteQueryBuilder->getSQL();
 194         }
 195
 196         // Set additional query restrictions
 197         $originalWhereConditions = $this->addAdditionalWhereConditions();
 198
 199         $sql = $this->concreteQueryBuilder->getSQL();
 200
 201         // Restore the original query conditions in case the user keeps
 202         // on modifying the state.
 203         $this->concreteQueryBuilder->add('where', $originalWhereConditions, false);
 204
 205         return $sql;
 206     }
 207
 208     /**
 209      * Sets a query parameter for the query being constructed.
 210      *
 211      * @param string|int $key The parameter position or name.
 212      * @param mixed $value The parameter value.
 213      * @param int|null $type One of the Connection::PARAM_* constants.
 214      *
 215      * @return QueryBuilder This QueryBuilder instance.
 216      */
 217     public function setParameter($key, $value, int $type = null): QueryBuilder
 218     {
 219         $this->concreteQueryBuilder->setParameter($key, $value, $type);
 220
 221         return $this;
 222     }
 223
 224     /**
 225      * Sets a collection of query parameters for the query being constructed.
 226      *
 227      * @param array $params The query parameters to set.
 228      * @param array $types The query parameters types to set.
 229      *
 230      * @return QueryBuilder This QueryBuilder instance.
 231      */
 232     public function setParameters(array $params, array $types = []): QueryBuilder
 233     {
 234         $this->concreteQueryBuilder->setParameters($params, $types);
 235
 236         return $this;
 237     }
 238
 239     /**
 240      * Gets all defined query parameters for the query being constructed indexed by parameter index or name.
 241      *
 242      * @return array The currently defined query parameters indexed by parameter index or name.
 243      */
 244     public function getParameters(): array
 245     {
 246         return $this->concreteQueryBuilder->getParameters();
 247     }
 248
 249     /**
 250      * Gets a (previously set) query parameter of the query being constructed.
 251      *
 252      * @param string|int $key The key (index or name) of the bound parameter.
 253      *
 254      * @return mixed The value of the bound parameter.
 255      */
 256     public function getParameter($key)
 257     {
 258         return $this->concreteQueryBuilder->getParameter($key);
 259     }
 260
 261     /**
 262      * Gets all defined query parameter types for the query being constructed indexed by parameter index or name.
 263      *
 264      * @return array The currently defined query parameter types indexed by parameter index or name.
 265      */
 266     public function getParameterTypes(): array
 267     {
 268         return $this->concreteQueryBuilder->getParameterTypes();
 269     }
 270
 271     /**
 272      * Gets a (previously set) query parameter type of the query being constructed.
 273      *
 274      * @param string|int $key The key (index or name) of the bound parameter type.
 275      *
 276      * @return mixed The value of the bound parameter type.
 277      */
 278     public function getParameterType($key)
 279     {
 280         return $this->concreteQueryBuilder->getParameterType($key);
 281     }
 282
 283     /**
 284      * Sets the position of the first result to retrieve (the "offset").
 285      *
 286      * @param int $firstResult The first result to return.
 287      *
 288      * @return QueryBuilder This QueryBuilder instance.
 289      */
 290     public function setFirstResult(int $firstResult): QueryBuilder
 291     {
 292         $this->concreteQueryBuilder->setFirstResult($firstResult);
 293
 294         return $this;
 295     }
 296
 297     /**
 298      * Gets the position of the first result the query object was set to retrieve (the "offset").
 299      * Returns NULL if {@link setFirstResult} was not applied to this QueryBuilder.
 300      *
 301      * @return int The position of the first result.
 302      */
 303     public function getFirstResult(): int
 304     {
 305         return (int)$this->concreteQueryBuilder->getFirstResult();
 306     }
 307
 308     /**
 309      * Sets the maximum number of results to retrieve (the "limit").
 310      *
 311      * @param int $maxResults The maximum number of results to retrieve.
 312      *
 313      * @return QueryBuilder This QueryBuilder instance.
 314      */
 315     public function setMaxResults(int $maxResults): QueryBuilder
 316     {
 317         $this->concreteQueryBuilder->setMaxResults($maxResults);
 318
 319         return $this;
 320     }
 321
 322     /**
 323      * Gets the maximum number of results the query object was set to retrieve (the "limit").
 324      * Returns 0 if setMaxResults was not applied to this query builder.
 325      *
 326      * @return int The maximum number of results.
 327      */
 328     public function getMaxResults(): int
 329     {
 330         return (int)$this->concreteQueryBuilder->getMaxResults();
 331     }
 332
 333     /**
 334      * Either appends to or replaces a single, generic query part.
 335      *
 336      * The available parts are: 'select', 'from', 'set', 'where',
 337      * 'groupBy', 'having' and 'orderBy'.
 338      *
 339      * @param string $sqlPartName
 340      * @param string $sqlPart
 341      * @param bool $append
 342      *
 343      * @return QueryBuilder This QueryBuilder instance.
 344      */
 345     public function add(string $sqlPartName, string $sqlPart, bool $append = false): QueryBuilder
 346     {
 347         $this->concreteQueryBuilder->add($sqlPartName, $sqlPart, $append);
 348
 349         return $this;
 350     }
 351
 352     /**
 353      * Specifies the item that is to be counted in the query result.
 354      * Replaces any previously specified selections, if any.
 355      *
 356      * @param string $item Will be quoted according to database platform automatically.
 357      * @return QueryBuilder This QueryBuilder instance.
 358      */
 359     public function count(string $item): QueryBuilder
 360     {
 361         $countExpr = $this->getConnection()->getDatabasePlatform()->getCountExpression(
 362             $item === '*' ? $item : $this->quoteIdentifier($item)
 363         );
 364         $this->concreteQueryBuilder->select($countExpr);
 365
 366         return $this;
 367     }
 368
 369     /**
 370      * Specifies items that are to be returned in the query result.
 371      * Replaces any previously specified selections, if any.
 372      *
 373      * @param string[] $selects
 374      * @return QueryBuilder This QueryBuilder instance.
 375      */
 376     public function select(string ...$selects): QueryBuilder
 377     {
 378         $this->concreteQueryBuilder->select(...$this->quoteIdentifiersForSelect($selects));
 379
 380         return $this;
 381     }
 382
 383     /**
 384      * Adds an item that is to be returned in the query result.
 385      *
 386      * @param string[] $selects The selection expression.
 387      *
 388      * @return QueryBuilder This QueryBuilder instance.
 389      */
 390     public function addSelect(string ...$selects): QueryBuilder
 391     {
 392         $this->concreteQueryBuilder->addSelect(...$this->quoteIdentifiersForSelect($selects));
 393
 394         return $this;
 395     }
 396
 397     /**
 398      * Specifies items that are to be returned in the query result.
 399      * Replaces any previously specified selections, if any.
 400      * This should only be used for literal SQL expressions as no
 401      * quoting/escaping of any kind will be performed on the items.
 402      *
 403      * @param string[] $selects Literal SQL expressions to be selected. Warning: No quoting will be done!
 404      * @return QueryBuilder This QueryBuilder instance.
 405      */
 406     public function selectLiteral(string ...$selects): QueryBuilder
 407     {
 408         $this->concreteQueryBuilder->select(...$selects);
 409
 410         return $this;
 411     }
 412
 413     /**
 414      * Adds an item that is to be returned in the query result. This should
 415      * only be used for literal SQL expressions as no quoting/escaping of
 416      * any kind will be performed on the items.
 417      *
 418      * @param string[] $selects Literal SQL expressions to be selected.
 419      * @return QueryBuilder This QueryBuilder instance.
 420      */
 421     public function addSelectLiteral(string ...$selects): QueryBuilder
 422     {
 423         $this->concreteQueryBuilder->addSelect(...$selects);
 424
 425         return $this;
 426     }
 427
 428     /**
 429      * Turns the query being built into a bulk delete query that ranges over
 430      * a certain table.
 431      *
 432      * @param string $delete The table whose rows are subject to the deletion.
 433      *                       Will be quoted according to database platform automatically.
 434      * @param string $alias The table alias used in the constructed query.
 435      *                      Will be quoted according to database platform automatically.
 436      *
 437      * @return QueryBuilder This QueryBuilder instance.
 438      */
 439     public function delete(string $delete, string $alias = null): QueryBuilder
 440     {
 441         $this->concreteQueryBuilder->delete(
 442             $this->quoteIdentifier($delete),
 443             empty($alias) ? $alias : $this->quoteIdentifier($alias)
 444         );
 445
 446         return $this;
 447     }
 448
 449     /**
 450      * Turns the query being built into a bulk update query that ranges over
 451      * a certain table
 452      *
 453      * @param string $update The table whose rows are subject to the update.
 454      * @param string $alias The table alias used in the constructed query.
 455      *
 456      * @return QueryBuilder This QueryBuilder instance.
 457      */
 458     public function update(string $update, string $alias = null): QueryBuilder
 459     {
 460         $this->concreteQueryBuilder->update(
 461             $this->quoteIdentifier($update),
 462             empty($alias) ? $alias : $this->quoteIdentifier($alias)
 463         );
 464
 465         return $this;
 466     }
 467
 468     /**
 469      * Turns the query being built into an insert query that inserts into
 470      * a certain table
 471      *
 472      * @param string $insert The table into which the rows should be inserted.
 473      *
 474      * @return QueryBuilder This QueryBuilder instance.
 475      */
 476     public function insert(string $insert): QueryBuilder
 477     {
 478         $this->concreteQueryBuilder->insert($this->quoteIdentifier($insert));
 479
 480         return $this;
 481     }
 482
 483     /**
 484      * Creates and adds a query root corresponding to the table identified by the
 485      * given alias, forming a cartesian product with any existing query roots.
 486      *
 487      * @param string $from The table. Will be quoted according to database platform automatically.
 488      * @param string $alias The alias of the table. Will be quoted according to database platform automatically.
 489      *
 490      * @return QueryBuilder This QueryBuilder instance.
 491      */
 492     public function from(string $from, string $alias = null): QueryBuilder
 493     {
 494         $this->concreteQueryBuilder->from(
 495             $this->quoteIdentifier($from),
 496             empty($alias) ? $alias : $this->quoteIdentifier($alias)
 497         );
 498
 499         return $this;
 500     }
 501
 502     /**
 503      * Creates and adds a join to the query.
 504      *
 505      * @param string $fromAlias The alias that points to a from clause.
 506      * @param string $join The table name to join.
 507      * @param string $alias The alias of the join table.
 508      * @param string $condition The condition for the join.
 509      *
 510      * @return QueryBuilder This QueryBuilder instance.
 511      */
 512     public function join(string $fromAlias, string $join, string $alias, string $condition = null): QueryBuilder
 513     {
 514         $this->concreteQueryBuilder->innerJoin(
 515             $this->quoteIdentifier($fromAlias),
 516             $this->quoteIdentifier($join),
 517             $this->quoteIdentifier($alias),
 518             $condition
 519         );
 520
 521         return $this;
 522     }
 523
 524     /**
 525      * Creates and adds a join to the query.
 526      *
 527      * @param string $fromAlias The alias that points to a from clause.
 528      * @param string $join The table name to join.
 529      * @param string $alias The alias of the join table.
 530      * @param string $condition The condition for the join.
 531      *
 532      * @return QueryBuilder This QueryBuilder instance.
 533      */
 534     public function innerJoin(string $fromAlias, string $join, string $alias, string $condition = null): QueryBuilder
 535     {
 536         $this->concreteQueryBuilder->innerJoin(
 537             $this->quoteIdentifier($fromAlias),
 538             $this->quoteIdentifier($join),
 539             $this->quoteIdentifier($alias),
 540             $condition
 541         );
 542
 543         return $this;
 544     }
 545
 546     /**
 547      * Creates and adds a left join to the query.
 548      *
 549      * @param string $fromAlias The alias that points to a from clause.
 550      * @param string $join The table name to join.
 551      * @param string $alias The alias of the join table.
 552      * @param string $condition The condition for the join.
 553      *
 554      * @return QueryBuilder This QueryBuilder instance.
 555      */
 556     public function leftJoin(string $fromAlias, string $join, string $alias, string $condition = null): QueryBuilder
 557     {
 558         $this->concreteQueryBuilder->leftJoin(
 559             $this->quoteIdentifier($fromAlias),
 560             $this->quoteIdentifier($join),
 561             $this->quoteIdentifier($alias),
 562             $condition
 563         );
 564
 565         return $this;
 566     }
 567
 568     /**
 569      * Creates and adds a right join to the query.
 570      *
 571      * @param string $fromAlias The alias that points to a from clause.
 572      * @param string $join The table name to join.
 573      * @param string $alias The alias of the join table.
 574      * @param string $condition The condition for the join.
 575      *
 576      * @return QueryBuilder This QueryBuilder instance.
 577      */
 578     public function rightJoin(string $fromAlias, string $join, string $alias, string $condition = null): QueryBuilder
 579     {
 580         $this->concreteQueryBuilder->rightJoin(
 581             $this->quoteIdentifier($fromAlias),
 582             $this->quoteIdentifier($join),
 583             $this->quoteIdentifier($alias),
 584             $condition
 585         );
 586
 587         return $this;
 588     }
 589
 590     /**
 591      * Sets a new value for a column in a bulk update query.
 592      *
 593      * @param string $key The column to set.
 594      * @param string $value The value, expression, placeholder, etc.
 595      * @param bool $createNamedParameter Automatically create a named parameter for the value
 596      *
 597      * @return QueryBuilder This QueryBuilder instance.
 598      */
 599     public function set(string $key, $value, bool $createNamedParameter = true): QueryBuilder
 600     {
 601         $this->concreteQueryBuilder->set(
 602             $this->quoteIdentifier($key),
 603             $createNamedParameter ? $this->createNamedParameter($value) : $value
 604         );
 605
 606         return $this;
 607     }
 608
 609     /**
 610      * Specifies one or more restrictions to the query result.
 611      * Replaces any previously specified restrictions, if any.
 612      *
 613      * @param mixed,... $predicates
 614      * @return QueryBuilder This QueryBuilder instance.
 615      */
 616     public function where(...$predicates): QueryBuilder
 617     {
 618         $this->concreteQueryBuilder->where(...$predicates);
 619
 620         return $this;
 621     }
 622
 623     /**
 624      * Adds one or more restrictions to the query results, forming a logical
 625      * conjunction with any previously specified restrictions.
 626      *
 627      * @param mixed,... $where The query restrictions.
 628      *
 629      * @return QueryBuilder This QueryBuilder instance.
 630      *
 631      * @see where()
 632      */
 633     public function andWhere(...$where): QueryBuilder
 634     {
 635         $this->concreteQueryBuilder->andWhere(...$where);
 636
 637         return $this;
 638     }
 639
 640     /**
 641      * Adds one or more restrictions to the query results, forming a logical
 642      * disjunction with any previously specified restrictions.
 643      *
 644      * @param mixed,... $where The WHERE statement.
 645      *
 646      * @return QueryBuilder This QueryBuilder instance.
 647      *
 648      * @see where()
 649      */
 650     public function orWhere(...$where): QueryBuilder
 651     {
 652         $this->concreteQueryBuilder->orWhere(...$where);
 653
 654         return $this;
 655     }
 656
 657     /**
 658      * Specifies a grouping over the results of the query.
 659      * Replaces any previously specified groupings, if any.
 660      *
 661      * @param mixed,... $groupBy The grouping expression.
 662      *
 663      * @return QueryBuilder This QueryBuilder instance.
 664      */
 665     public function groupBy(...$groupBy): QueryBuilder
 666     {
 667         $this->concreteQueryBuilder->groupBy(...$this->quoteIdentifiers($groupBy));
 668
 669         return $this;
 670     }
 671
 672     /**
 673      * Adds a grouping expression to the query.
 674      *
 675      * @param mixed,... $groupBy The grouping expression.
 676      *
 677      * @return QueryBuilder This QueryBuilder instance.
 678      */
 679     public function addGroupBy(...$groupBy): QueryBuilder
 680     {
 681         $this->concreteQueryBuilder->addGroupBy(...$this->quoteIdentifiers($groupBy));
 682
 683         return $this;
 684     }
 685
 686     /**
 687      * Sets a value for a column in an insert query.
 688      *
 689      * @param string $column The column into which the value should be inserted.
 690      * @param string $value The value that should be inserted into the column.
 691      * @param bool $createNamedParameter Automatically create a named parameter for the value
 692      *
 693      * @return QueryBuilder This QueryBuilder instance.
 694      */
 695     public function setValue(string $column, $value, bool $createNamedParameter = true): QueryBuilder
 696     {
 697         $this->concreteQueryBuilder->setValue(
 698             $this->quoteIdentifier($column),
 699             $createNamedParameter ? $this->createNamedParameter($value) : $value
 700         );
 701
 702         return $this;
 703     }
 704
 705     /**
 706      * Specifies values for an insert query indexed by column names.
 707      * Replaces any previous values, if any.
 708      *
 709      * @param array $values The values to specify for the insert query indexed by column names.
 710      * @param bool $createNamedParameters Automatically create named parameters for all values
 711      *
 712      * @return QueryBuilder This QueryBuilder instance.
 713      */
 714     public function values(array $values, bool $createNamedParameters = true): QueryBuilder
 715     {
 716         if ($createNamedParameters === true) {
 717             foreach ($values as &$value) {
 718                 $value = $this->createNamedParameter($value);
 719             }
 720         }
 721
 722         $this->concreteQueryBuilder->values($this->quoteColumnValuePairs($values));
 723
 724         return $this;
 725     }
 726
 727     /**
 728      * Specifies a restriction over the groups of the query.
 729      * Replaces any previous having restrictions, if any.
 730      *
 731      * @param mixed,... $having The restriction over the groups.
 732      *
 733      * @return QueryBuilder This QueryBuilder instance.
 734      */
 735     public function having(...$having): QueryBuilder
 736     {
 737         $this->concreteQueryBuilder->having(...$having);
 738         return $this;
 739     }
 740
 741     /**
 742      * Adds a restriction over the groups of the query, forming a logical
 743      * conjunction with any existing having restrictions.
 744      *
 745      * @param mixed,... $having The restriction to append.
 746      *
 747      * @return QueryBuilder This QueryBuilder instance.
 748      */
 749     public function andHaving(...$having): QueryBuilder
 750     {
 751         $this->concreteQueryBuilder->andHaving(...$having);
 752
 753         return $this;
 754     }
 755
 756     /**
 757      * Adds a restriction over the groups of the query, forming a logical
 758      * disjunction with any existing having restrictions.
 759      *
 760      * @param mixed,... $having The restriction to add.
 761      *
 762      * @return QueryBuilder This QueryBuilder instance.
 763      */
 764     public function orHaving(...$having): QueryBuilder
 765     {
 766         $this->concreteQueryBuilder->orHaving(...$having);
 767
 768         return $this;
 769     }
 770
 771     /**
 772      * Specifies an ordering for the query results.
 773      * Replaces any previously specified orderings, if any.
 774      *
 775      * @param string $fieldName The fieldName to order by. Will be quoted according to database platform automatically.
 776      * @param string $order The ordering direction. No automatic quoting/escaping.
 777      *
 778      * @return QueryBuilder This QueryBuilder instance.
 779      */
 780     public function orderBy(string $fieldName, string $order = null): QueryBuilder
 781     {
 782         $this->concreteQueryBuilder->orderBy($this->connection->quoteIdentifier($fieldName), $order);
 783
 784         return $this;
 785     }
 786
 787     /**
 788      * Adds an ordering to the query results.
 789      *
 790      * @param string $fieldName The fieldName to order by. Will be quoted according to database platform automatically.
 791      * @param string $order The ordering direction.
 792      *
 793      * @return QueryBuilder This QueryBuilder instance.
 794      */
 795     public function addOrderBy(string $fieldName, string $order = null): QueryBuilder
 796     {
 797         $this->concreteQueryBuilder->addOrderBy($this->connection->quoteIdentifier($fieldName), $order);
 798
 799         return $this;
 800     }
 801
 802     /**
 803      * Gets a query part by its name.
 804      *
 805      * @param string $queryPartName
 806      *
 807      * @return mixed
 808      */
 809     public function getQueryPart(string $queryPartName)
 810     {
 811         return $this->concreteQueryBuilder->getQueryPart($queryPartName);
 812     }
 813
 814     /**
 815      * Gets all query parts.
 816      *
 817      * @return array
 818      */
 819     public function getQueryParts(): array
 820     {
 821         return $this->concreteQueryBuilder->getQueryParts();
 822     }
 823
 824     /**
 825      * Resets SQL parts.
 826      *
 827      * @param array|null $queryPartNames
 828      *
 829      * @return QueryBuilder This QueryBuilder instance.
 830      */
 831     public function resetQueryParts(array $queryPartNames = null): QueryBuilder
 832     {
 833         $this->concreteQueryBuilder->resetQueryParts($queryPartNames);
 834
 835         return $this;
 836     }
 837
 838     /**
 839      * Resets a single SQL part.
 840      *
 841      * @param string $queryPartName
 842      *
 843      * @return QueryBuilder This QueryBuilder instance.
 844      */
 845     public function resetQueryPart($queryPartName): QueryBuilder
 846     {
 847         $this->concreteQueryBuilder->resetQueryPart($queryPartName);
 848
 849         return $this;
 850     }
 851
 852     /**
 853      * Gets a string representation of this QueryBuilder which corresponds to
 854      * the final SQL query being constructed.
 855      *
 856      * @return string The string representation of this QueryBuilder.
 857      */
 858     public function __toString(): string
 859     {
 860         return $this->getSQL();
 861     }
 862
 863     /**
 864      * Creates a new named parameter and bind the value $value to it.
 865      *
 866      * This method provides a shortcut for PDOStatement::bindValue
 867      * when using prepared statements.
 868      *
 869      * The parameter $value specifies the value that you want to bind. If
 870      * $placeholder is not provided bindValue() will automatically create a
 871      * placeholder for you. An automatic placeholder will be of the name
 872      * ':dcValue1', ':dcValue2' etc.
 873      *
 874      * @param mixed $value
 875      * @param int $type
 876      * @param string $placeHolder The name to bind with. The string must start with a colon ':'.
 877      *
 878      * @return string the placeholder name used.
 879      */
 880     public function createNamedParameter($value, int $type = \PDO::PARAM_STR, string $placeHolder = null): string
 881     {
 882         return $this->concreteQueryBuilder->createNamedParameter($value, $type, $placeHolder);
 883     }
 884
 885     /**
 886      * Creates a new positional parameter and bind the given value to it.
 887      *
 888      * Attention: If you are using positional parameters with the query builder you have
 889      * to be very careful to bind all parameters in the order they appear in the SQL
 890      * statement , otherwise they get bound in the wrong order which can lead to serious
 891      * bugs in your code.
 892      *
 893      * @param mixed $value
 894      * @param int $type
 895      *
 896      * @return string
 897      */
 898     public function createPositionalParameter($value, int $type = \PDO::PARAM_STR): string
 899     {
 900         return $this->concreteQueryBuilder->createPositionalParameter($value, $type);
 901     }
 902
 903     /**
 904      * Quotes like wildcards for given string value.
 905      *
 906      * @param string $value The value to be quoted.
 907      *
 908      * @return string The quoted value.
 909      */
 910     public function escapeLikeWildcards(string $value): string
 911     {
 912         return addcslashes($value, '_%');
 913     }
 914
 915     /**
 916      * Quotes a given input parameter.
 917      *
 918      * @param mixed $input The parameter to be quoted.
 919      * @param int|null $type The type of the parameter.
 920      *
 921      * @return mixed Often string, but also int or float or similar depending on $input and platform
 922      */
 923     public function quote($input, int $type = null)
 924     {
 925         return $this->getConnection()->quote($input, $type);
 926     }
 927
 928     /**
 929      * Quotes a string so it can be safely used as a table or column name, even if
 930      * it is a reserved name.
 931      *
 932      * Delimiting style depends on the underlying database platform that is being used.
 933      *
 934      * @param string $identifier The name to be quoted.
 935      *
 936      * @return string The quoted name.
 937      */
 938     public function quoteIdentifier(string $identifier): string
 939     {
 940         return $this->getConnection()->quoteIdentifier($identifier);
 941     }
 942
 943     /**
 944      * Quotes an array of column names so it can be safely used, even if the name is a reserved name.
 945      *
 946      * Delimiting style depends on the underlying database platform that is being used.
 947      *
 948      * @param array $input
 949      *
 950      * @return array
 951      */
 952     public function quoteIdentifiers(array $input): array
 953     {
 954         return $this->getConnection()->quoteIdentifiers($input);
 955     }
 956
 957     /**
 958      * Quotes an array of column names so it can be safely used, even if the name is a reserved name.
 959      * Takes into account the special case of the * placeholder that can only be used in SELECT type
 960      * statements.
 961      *
 962      * Delimiting style depends on the underlying database platform that is being used.
 963      *
 964      * @param array $input
 965      *
 966      * @return array
 967      * @throws \InvalidArgumentException
 968      */
 969     public function quoteIdentifiersForSelect(array $input): array
 970     {
 971         foreach ($input as &$select) {
 972             list($fieldName, $alias, $suffix) = GeneralUtility::trimExplode(' AS ', str_ireplace(' as ', ' AS ', $select), 3);
 973             if (!empty($suffix)) {
 974                 throw new \InvalidArgumentException(
 975                     'QueryBuilder::quoteIdentifiersForSelect() could not parse the input "' . $input . '"',
 976                     1461170686
 977                 );
 978             }
 979
 980             // The SQL * operator must not be quoted. As it can only occur either by itself
 981             // or preceded by a tablename (tablename.*) check if the last character of a select
 982             // expression is the * and quote only prepended table name. In all other cases the
 983             // full expression is being quoted.
 984             if (substr($fieldName, -2) === '.*') {
 985                 $select = $this->quoteIdentifier(substr($fieldName, 0, -2)) . '.*';
 986             } elseif ($fieldName !== '*') {
 987                 $select = $this->quoteIdentifier($fieldName);
 988             }
 989
 990             // Quote the alias for the current fieldName, if given
 991             if (!empty($alias)) {
 992                 $select .= ' AS ' . $this->quoteIdentifier($alias);
 993             }
 994         }
 995         return $input;
 996     }
 997
 998     /**
 999      * Quotes an associative array of column-value so the column names can be safely used, even
1000      * if the name is a reserved name.
1001      *
1002      * Delimiting style depends on the underlying database platform that is being used.
1003      *
1004      * @param array $input
1005      *
1006      * @return array
1007      */
1008     public function quoteColumnValuePairs(array $input): array
1009     {
1010         return $this->getConnection()->quoteColumnValuePairs($input);
1011     }
1012
1013     /**
1014      * Unquote a single identifier (no dot expansion). Used to unquote the table names
1015      * from the expressionBuilder so that the table can be found in the TCA definition.
1016      *
1017      * @param string $identifier The identifier / table name
1018      * @return string The unquoted table name / identifier
1019      */
1020     protected function unquoteSingleIdentifier(string $identifier): string
1021     {
1022         $identifier = trim($identifier);
1023         $platform = $this->getConnection()->getDatabasePlatform();
1024         if ($platform instanceof SQLServerPlatform) {
1025             // mssql quotes identifiers with [ and ], not a single character
1026             $identifier = ltrim($identifier, '[');
1027             $identifier = rtrim($identifier, ']');
1028         } else {
1029             $quoteChar = $platform->getIdentifierQuoteCharacter();
1030             $identifier = trim($identifier, $quoteChar);
1031             $identifier = str_replace($quoteChar . $quoteChar, $quoteChar, $identifier);
1032         }
1033         return $identifier;
1034     }
1035
1036     /**
1037      * Return all tables/aliases used in FROM or JOIN query parts from the query builder.
1038      *
1039      * The table names are automatically unquoted. This is a helper for to build the list
1040      * of queried tables for the AbstractRestrictionContainer.
1041      *
1042      * @return string[]
1043      */
1044     protected function getQueriedTables(): array
1045     {
1046         $queriedTables = [];
1047
1048         // Loop through all FROM tables
1049         foreach ($this->getQueryPart('from') as $from) {
1050             $tableName = $this->unquoteSingleIdentifier($from['table']);
1051             $tableAlias = isset($from['alias']) ? $this->unquoteSingleIdentifier($from['alias']) : $tableName;
1052             $queriedTables[$tableAlias] = $tableName;
1053         }
1054
1055         // Loop through all JOIN tables
1056         foreach ($this->getQueryPart('join') as $fromTable => $joins) {
1057             foreach ($joins as $join) {
1058                 $tableName = $this->unquoteSingleIdentifier($join['joinTable']);
1059                 $tableAlias = isset($join['joinAlias']) ? $this->unquoteSingleIdentifier($join['joinAlias']) : $tableName;
1060                 $queriedTables[$tableAlias] = $tableName;
1061             }
1062         }
1063
1064         return $queriedTables;
1065     }
1066
1067     /**
1068      * Add the additional query conditions returned by the QueryRestrictionBuilder
1069      * to the current query and return the original set of conditions so that they
1070      * can be restored after the query has been built/executed.
1071      *
1072      * @return \Doctrine\DBAL\Query\Expression\CompositeExpression|mixed
1073      */
1074     protected function addAdditionalWhereConditions()
1075     {
1076         $originalWhereConditions = $this->concreteQueryBuilder->getQueryPart('where');
1077         $expression = $this->restrictionContainer->buildExpression($this->getQueriedTables(), $this->expr());
1078         // This check would be obsolete, as the composite expression would not add empty expressions anyway
1079         // But we keep it here to only clone the previous state, in case we really will change it.
1080         // Once we remove this state preserving functionality, we can remove the count check here
1081         // and just add the expression to the query builder.
1082         if ($expression->count() > 0) {
1083             if ($originalWhereConditions instanceof CompositeExpression) {
1084                 // Save the original query conditions so we can restore
1085                 // them after the query has been built.
1086                 $originalWhereConditions = clone $originalWhereConditions;
1087             }
1088             $this->concreteQueryBuilder->andWhere($expression);
1089         }
1090
1091         // @todo add hook to be able to add additional restrictions
1092
1093         return $originalWhereConditions;
1094     }
1095
1096     /**
1097      * Deep clone of the QueryBuilder
1098      * @see \Doctrine\DBAL\Query\QueryBuilder::__clone()
1099      */
1100     public function __clone()
1101     {
1102         $this->concreteQueryBuilder = clone $this->concreteQueryBuilder;
1103         $this->restrictionContainer = clone $this->restrictionContainer;
1104     }
1105 }