TYPO3 main API: QueryBuilder

QueryBuilder extends ConcreteQueryBuilder

Object-oriented approach to building SQL queries.

It's a facade to the Doctrine DBAL QueryBuilder that implements PHP7 type hinting and automatic quoting of table and column names.


$query->select('aField', 'anotherField')
      ->from('aTable')
      ->where($query->expr()->eq('aField', 1))
      ->andWhere($query->expr()->gte('anotherField',10'))
      ->execute()

Additional functionality included is support for COUNT() and TRUNCATE() statements.

Properties

$additionalRestrictions : array<string|int, mixed>
$concreteQueryBuilder : ConcreteQueryBuilder
$connection : Connection
$distinct : bool: Whether this is a SELECT DISTINCT query.
$forUpdate : ForUpdate|null
$from : array<string|int, From>: The FROM parts of a SELECT query.
$groupBy : array<string|int, string>: The GROUP BY part of a SELECT query.
$having : string|CompositeExpression|null: The HAVING part of a SELECT query.
$join : array<string, array<string|int, Join>>: The list of joins, indexed by from alias.
$orderBy : array<string|int, string>: The ORDER BY parts of a SELECT query.
$restrictionContainer : QueryRestrictionContainerInterface
$select : array<string|int, string>: The SELECT parts of the query.
$sql : string|null: The complete SQL string for this query.
$type : QueryType: The type of query this is. Can be select, update or delete.
$typo3_unionParts : array<string|int, Union>: The QueryBuilder for the union parts.
$typo3_with : WithCollection: The WITH query parts.
$where : string|CompositeExpression|null: The WHERE part of a SELECT, UPDATE or DELETE query.

Methods

__clone() : mixed: Deep clone of the QueryBuilder
__construct() : mixed: Initializes a new QueryBuilder.
__toString() : string: Gets a string representation of this QueryBuilder which corresponds to the final SQL query being constructed.
addGroupBy() : $this: Adds a grouping expression to the query.
addOrderBy() : $this: Adds an ordering to the query results.
addSelect() : $this: Adds an item that is to be returned in the query result.
addSelectLiteral() : QueryBuilder: Adds an item that is to be returned in the query result. This should only be used for literal SQL expressions as no quoting/escaping of any kind will be performed on the items.
addUnion() : $this: Add parts to be used to build a UNION query.
andHaving() : $this: Adds a restriction over the groups of the query, forming a logical conjunction with any existing having restrictions.
andWhere() : $this: Adds one or more restrictions to the query results, forming a logical conjunction with any previously specified restrictions.
castFieldToTextType() : string: Creates a cast of the $fieldName to a text datatype depending on the database management system.
count() : QueryBuilder: Specifies the item that is to be counted in the query result.
createNamedParameter() : string: Creates a new named parameter and bind the value $value to it.
createPositionalParameter() : string: Creates a new positional parameter and bind the given value to it.
delete() : $this: Turns the query being built into a bulk delete query that ranges over a certain table.
distinct() : $this: Adds or removes DISTINCT to/from the query.
escapeLikeWildcards() : string: Quotes like wildcards for given string value.
executeQuery() : Result: Executes an SQL query (SELECT) and returns a Result.
executeStatement() : int: Executes an SQL statement (INSERT, UPDATE and DELETE) and returns the number of affected rows.
expr() : ExpressionBuilder: Gets an ExpressionBuilder used for object-oriented construction of query expressions.
forUpdate() : QueryBuilder
from() : $this: Creates and adds a query root corresponding to the table identified by the given alias, forming a cartesian product with any existing query roots.
getConcreteQueryBuilder() : QueryBuilder: Gets the concrete implementation of the query builder
getConnection() : Connection: Gets the associated DBAL Connection for this query builder.
getFirstResult() : int: Gets the position of the first result the query object was set to retrieve (the "offset").
getFrom() : array<string|int, From>: Returns from tables from internal query state.
getGroupBy() : array<string|int, string>: Returns selected group-by definitions from internal query state.
getHaving() : CompositeExpression|string|null: Returns having expressions from internal query state.
getJoin() : array<string, array<string|int, Join>>: Returns the list of joins, indexed by `from-alias` from the internal query state.
getMaxResults() : int|null: Gets the maximum number of results the query object was set to retrieve (the "limit").
getOrderBy() : array<string|int, string>: Returns order-by definitions from internal query state.
getParameter() : mixed: Gets a (previously set) query parameter of the query being constructed.
getParameters() : array<int, mixed>|array<string, mixed>: Gets all defined query parameters for the query being constructed indexed by parameter index or name.
getParameterType() : string|ParameterType|Type|ArrayParameterType: Gets a (previously set) query parameter type of the query being constructed.
getParameterTypes() : array<int<0, max>, string|Type|ParameterType|ArrayParameterType>|array<string, string|Type|ParameterType|ArrayParameterType>: Gets all defined query parameter types for the query being constructed indexed by parameter index or name.
getRestrictions() : QueryRestrictionContainerInterface
getSelect() : array<string|int, string>: Returns selected fields from internal query state.
getSQL() : string: Gets the complete SQL string formed by the current specifications of this QueryBuilder.
getWhere() : CompositeExpression|string|null: Returns where expressions from internal query state.
groupBy() : $this: Specifies a grouping over the results of the query.
having() : $this: Specifies a restriction over the groups of the query.
innerJoin() : $this: Creates and adds a join to the query.
insert() : $this: Turns the query being built into an insert query that inserts into a certain table
join() : QueryBuilder: Creates and adds a join to the query.
leftJoin() : $this: Creates and adds a left join to the query.
limitRestrictionsToTables() : void: Limits ALL currently active restrictions of the restriction container to the table aliases given
orderBy() : $this: Specifies an ordering for the query results.
orHaving() : $this: Adds a restriction over the groups of the query, forming a logical disjunction with any existing having restrictions.
orWhere() : $this: Adds one or more restrictions to the query results, forming a logical disjunction with any previously specified restrictions.
prepare() : Statement: Create prepared statement out of QueryBuilder instance.
quote() : string: Quotes a given input parameter.
quoteArrayBasedValueListToIntegerList() : string: Implode array to comma separated list with database int-quoted values to be used as direct value list for database 'in(...)' or 'notIn(...') expressions. Empty array will return 'NULL' as string to avoid database query failure, as 'IN()' is invalid, but 'IN(NULL)' is fine.
quoteArrayBasedValueListToStringList() : string: Implode array to comma separated list with database string-quoted values to be used as direct value list for database 'in(...)' or 'notIn(...') expressions. Empty array will return 'NULL' as string to avoid database query failure, as 'IN()' is invalid, but 'IN(NULL)' is fine.
quoteColumnValuePairs() : array<string|int, mixed>: Quotes an associative array of column-value so the column names can be safely used, even if the name is a reserved name.
quoteIdentifier() : string: Quotes a string so it can be safely used as a table or column name, even if it is a reserved name.
quoteIdentifiers() : array<string|int, mixed>: Quotes an array of column names so it can be safely used, even if the name is a reserved name.
quoteIdentifiersForSelect() : array<string|int, mixed>: Quotes an array of column names so it can be safely used, even if the name is a reserved name.
resetGroupBy() : $this: Resets the grouping for the query.
resetHaving() : $this: Resets the HAVING conditions for the query.
resetOrderBy() : $this: Resets the ordering for the query.
resetRestrictions() : void: Re-apply default restrictions
resetWhere() : $this: Resets the WHERE conditions for the query.
rightJoin() : $this: Creates and adds a right join to the query.
select() : $this: Specifies items that are to be returned in the query result.
selectLiteral() : QueryBuilder: Specifies items that are to be returned in the query result.
set() : QueryBuilder: Sets a new value for a column in a bulk update query.
setFirstResult() : QueryBuilder: Sets the position of the first result to retrieve (the "offset").
setMaxResults() : QueryBuilder: Sets the maximum number of results to retrieve (the "limit").
setParameter() : QueryBuilder: Sets a query parameter for the query being constructed.
setParameters() : QueryBuilder: Sets a collection of query parameters for the query being constructed.
setRestrictions() : void
setValue() : QueryBuilder: Sets a value for a column in an insert query.
typo3_addWith() : self
typo3_addWithRecursive() : self
typo3_with() : self
typo3_withRecursive() : self
union() : $this: Specifies union parts to be used to build a UNION query.
update() : $this: Turns the query being built into a bulk update query that ranges over a certain table
values() : QueryBuilder: Specifies values for an insert query indexed by column names.
where() : $this: Specifies one or more restrictions to the query result.
addAdditionalWhereConditions() : CompositeExpression|string|null: Add the additional query conditions returned by the QueryRestrictionBuilder to the current query and return the original set of conditions so that they can be restored after the query has been built/executed.
getCountExpression() : string
getIdentifierQuoteCharacter() : string
getQueriedTables() : array<non-empty-string, non-empty-string>: Return all tables/aliases used in FROM or JOIN query parts from the query builder.
isEmptyPart() : bool: Determine if a query part used for where or having is empty. Used as array_filter in ConcreteQueryBuilder methods. This is needed to avoid invalid sql syntax by empty parts, which can happen to relaxed custom CompositeExpression handling.
unquoteSingleIdentifier() : string: Unquote a single identifier (no dot expansion). Used to unquote the table names from the expressionBuilder so that the table can be found in the TCA definition.

$additionalRestrictions


    protected
        array<string|int, mixed>
    $additionalRestrictions

$concreteQueryBuilder


    protected
        ConcreteQueryBuilder
    $concreteQueryBuilder

$connection read-only


    protected
        Connection
    $connection

$distinct

Whether this is a SELECT DISTINCT query.


    protected
        bool
    $distinct
     = false

$forUpdate


    protected
        ForUpdate|null
    $forUpdate
     = null

$from

The FROM parts of a SELECT query.


    protected
        array<string|int, From>
    $from
     = []

$groupBy

The GROUP BY part of a SELECT query.


    protected
        array<string|int, string>
    $groupBy
     = []

$having

The HAVING part of a SELECT query.


    protected
        string|CompositeExpression|null
    $having
     = null

$join

The list of joins, indexed by from alias.


    protected
        array<string, array<string|int, Join>>
    $join
     = []

$orderBy

The ORDER BY parts of a SELECT query.


    protected
        array<string|int, string>
    $orderBy
     = []

$restrictionContainer


    protected
        QueryRestrictionContainerInterface
    $restrictionContainer

$select

The SELECT parts of the query.


    protected
        array<string|int, string>
    $select
     = []

$sql

The complete SQL string for this query.


    protected
        string|null
    $sql
     = null

$type

The type of query this is. Can be select, update or delete.


    protected
        QueryType
    $type
     = \Doctrine\DBAL\Query\QueryType::SELECT

$typo3_unionParts

The QueryBuilder for the union parts.


    protected
        array<string|int, Union>
    $typo3_unionParts
     = []

$typo3_with

The WITH query parts.


    protected
        WithCollection
    $typo3_with

$where

The WHERE part of a SELECT, UPDATE or DELETE query.


    protected
        string|CompositeExpression|null
    $where
     = null

__clone()

Deep clone of the QueryBuilder


    public
                    __clone() : mixed

__construct()

Initializes a new QueryBuilder.


    public
                    __construct(Connection $connection[, QueryRestrictionContainerInterface|null $restrictionContainer = null ][, ConcreteQueryBuilder|null $concreteQueryBuilder = null ][, array<string|int, mixed>|null $additionalRestrictions = null ]) : mixed

Parameters

$connection : Connection: The DBAL Connection.
$restrictionContainer : QueryRestrictionContainerInterface|null = null
$concreteQueryBuilder : ConcreteQueryBuilder|null = null
$additionalRestrictions : array<string|int, mixed>|null = null

__toString()

Gets a string representation of this QueryBuilder which corresponds to the final SQL query being constructed.


    public
                    __toString() : string

Return values

string —

The string representation of this QueryBuilder.

addGroupBy()

Adds a grouping expression to the query.


    public
                    addGroupBy(string ...$groupBy) : $this

Parameters

$groupBy : string: The grouping expression.

Return values

$this —

This QueryBuilder instance.

addOrderBy()

Adds an ordering to the query results.


    public
                    addOrderBy(string $fieldName[, string|null $order = null ]) : $this

Parameters

$fieldName : string: The fieldName to order by. Will be quoted according to database platform automatically.
$order : string|null = null: The ordering direction.

Return values

$this —

This QueryBuilder instance.

addSelect()

Adds an item that is to be returned in the query result.


    public
                    addSelect(string ...$selects) : $this

Parameters

$selects : string

Return values

$this —

This QueryBuilder instance.

addSelectLiteral()

Adds an item that is to be returned in the query result. This should only be used for literal SQL expressions as no quoting/escaping of any kind will be performed on the items.


    public
                    addSelectLiteral(string ...$selects) : QueryBuilder

Parameters

$selects : string: Literal SQL expressions to be selected.

Return values

QueryBuilder

addUnion()

Add parts to be used to build a UNION query.


    public
                    addUnion(string|QueryBuilder|ConcreteQueryBuilder|QueryBuilder $part[, UnionType $type = UnionType::DISTINCT ]) : $this

  $qb = $conn->createQueryBuilder()
    ->union('SELECT 1 AS field1')
    ->addUnion('SELECT 2 AS field1', 'SELECT 3 AS field1')

Parameters

$part : string|QueryBuilder|ConcreteQueryBuilder|QueryBuilder
$type : UnionType = UnionType::DISTINCT

Return values

$this

andHaving()

Adds a restriction over the groups of the query, forming a logical conjunction with any existing having restrictions.


    public
                    andHaving(mixed ...$predicates) : $this

Parameters

$predicates : mixed: The restriction to append.

Return values

$this —

This QueryBuilder instance.

andWhere()

Adds one or more restrictions to the query results, forming a logical conjunction with any previously specified restrictions.


    public
                    andWhere(string|CompositeExpression ...$predicates) : $this

Parameters

$predicates : string|CompositeExpression: The query restrictions.

Return values

$this —

This QueryBuilder instance.

castFieldToTextType()

Creates a cast of the $fieldName to a text datatype depending on the database management system.


    public
                    castFieldToTextType(string $fieldName) : string

Parameters

$fieldName : string: The fieldname will be quoted and casted according to database platform automatically

Return values

string

count()

Specifies the item that is to be counted in the query result.


    public
                    count(string $item) : QueryBuilder

Replaces any previously specified selections, if any.

Parameters

$item : string: Will be quoted according to database platform automatically.

Return values

QueryBuilder —

This QueryBuilder instance.

createNamedParameter()

Creates a new named parameter and bind the value $value to it.


    public
                    createNamedParameter(mixed $value[, string|ParameterType|Type|ArrayParameterType $type = ParameterType::STRING ][, string|null $placeHolder = null ]) : string

This method provides a shortcut for Statement::bindValue() when using prepared statements.

The parameter $value specifies the value that you want to bind. If $placeholder is not provided createNamedParameter() will automatically create a placeholder for you. An automatic placeholder will be of the name ':dcValue1', ':dcValue2' etc.

Example: $value = 2; $q->eq( 'id', $q->createNamedParameter( $value ) ); $stmt = $q->executeQuery(); // executed with 'id = 2'

Parameters

$value : mixed
$type : string|ParameterType|Type|ArrayParameterType = ParameterType::STRING
$placeHolder : string|null = null: The name to bind with. The string must start with a colon ':'.

Return values

string —

the placeholder name used.

createPositionalParameter()

Creates a new positional parameter and bind the given value to it.


    public
                    createPositionalParameter(mixed $value[, string|ParameterType|Type|ArrayParameterType $type = ParameterType::STRING ]) : string

Attention: If you are using positional parameters with the query builder you have to be very careful to bind all parameters in the order they appear in the SQL statement , otherwise they get bound in the wrong order which can lead to serious bugs in your code.

Example: $qb = $conn->createQueryBuilder(); $qb->select('u.*') ->from('users', 'u') ->where('u.username = ' . $qb->createPositionalParameter('Foo', ParameterType::STRING)) ->orWhere('u.username = ' . $qb->createPositionalParameter('Bar', ParameterType::STRING))

Parameters

$value : mixed
$type : string|ParameterType|Type|ArrayParameterType = ParameterType::STRING

Return values

string

delete()

Turns the query being built into a bulk delete query that ranges over a certain table.


    public
                    delete(string $table) : $this

Parameters

$table : string: The table whose rows are subject to the deletion. Will be quoted according to database platform automatically.

Return values

$this —

This QueryBuilder instance.

distinct()

Adds or removes DISTINCT to/from the query.


    public
                    distinct([bool $distinct = true ]) : $this

Parameters

$distinct : bool = true

Return values

$this —

This QueryBuilder instance.

escapeLikeWildcards()

Quotes like wildcards for given string value.


    public
                    escapeLikeWildcards(string $value) : string

Parameters

$value : string: The value to be quoted.

Return values

string —

The quoted value.

executeQuery()

Executes an SQL query (SELECT) and returns a Result.


    public
                    executeQuery() : Result

doctrine/dbal decided to split execute() into executeQuery() and executeStatement() for doctrine/dbal:^3.0, like it was done on connection level already, thus these methods are added to this decorator class also as preparation for extension authors, that they are able to write code which is compatible across two core versions and avoid deprecation warning. Additional this will ease backport without the need to switch if execute() is not used anymore.

Return values

Result

executeStatement()

Executes an SQL statement (INSERT, UPDATE and DELETE) and returns the number of affected rows.


    public
                    executeStatement() : int

Return values

int —

The number of affected rows.

expr()

Gets an ExpressionBuilder used for object-oriented construction of query expressions.


    public
                    expr() : ExpressionBuilder

This producer method is intended for convenient inline usage. Example:

For more complex expression construction, consider storing the expression builder object in a local variable.

Return values

ExpressionBuilder

forUpdate()


    public
                    forUpdate([ConflictResolutionMode $conflictResolutionMode = ConflictResolutionMode::ORDINARY ]) : QueryBuilder

Parameters

$conflictResolutionMode : ConflictResolutionMode = ConflictResolutionMode::ORDINARY

Return values

QueryBuilder

from()

Creates and adds a query root corresponding to the table identified by the given alias, forming a cartesian product with any existing query roots.


    public
                    from(string $table[, string|null $alias = null ]) : $this

Parameters

$table : string: The table. Will be quoted according to database platform automatically.
$alias : string|null = null: The alias of the table. Will be quoted according to database platform automatically.

Return values

$this —

This QueryBuilder instance.

getConcreteQueryBuilder()

Gets the concrete implementation of the query builder


    public
                    getConcreteQueryBuilder() : QueryBuilder

Internal

Return values

QueryBuilder

getConnection()

Gets the associated DBAL Connection for this query builder.


    public
                    getConnection() : Connection

Return values

Connection

getFirstResult()

Gets the position of the first result the query object was set to retrieve (the "offset").


    public
                    getFirstResult() : int

Returns NULL if was not applied to this QueryBuilder.

Return values

int —

The position of the first result.

getFrom()

Returns from tables from internal query state.


    public
                    getFrom() : array<string|int, From>

Internal

only, used for Extbase internal handling and core tests. Don't use it.

Return values

array<string|int, From>

getGroupBy()

Returns selected group-by definitions from internal query state.


    public
                    getGroupBy() : array<string|int, string>

Internal

only, used for Extbase internal handling and core tests. Don't use it.

Return values

array<string|int, string>

getHaving()

Returns having expressions from internal query state.


    public
                    getHaving() : CompositeExpression|string|null

Internal

only, used for Extbase internal handling and core tests. Don't use it.

Return values

CompositeExpression|string|null

getJoin()

Returns the list of joins, indexed by `from-alias` from the internal query state.


    public
                    getJoin() : array<string, array<string|int, Join>>

Internal

only, used for Extbase internal handling and core tests. Don't use it.

Return values

array<string, array<string|int, Join>>

getMaxResults()

Gets the maximum number of results the query object was set to retrieve (the "limit").


    public
                    getMaxResults() : int|null

Returns 0 if setMaxResults was not applied to this query builder.

Return values

int|null —

The maximum number of results.

getOrderBy()

Returns order-by definitions from internal query state.


    public
                    getOrderBy() : array<string|int, string>

Internal

only, used for Extbase internal handling and core tests. Don't use it.

Return values

array<string|int, string>

getParameter()

Gets a (previously set) query parameter of the query being constructed.


    public
                    getParameter(string|int $key) : mixed

Parameters

$key : string|int: The key (index or name) of the bound parameter.

Return values

mixed —

The value of the bound parameter.

getParameters()

Gets all defined query parameters for the query being constructed indexed by parameter index or name.


    public
                    getParameters() : array<int, mixed>|array<string, mixed>

Return values

array<int, mixed>|array<string, mixed> —

The currently defined query parameters indexed by parameter index or name.

getParameterType()

Gets a (previously set) query parameter type of the query being constructed.


    public
                    getParameterType(string|int $key) : string|ParameterType|Type|ArrayParameterType

Parameters

$key : string|int: The key (index or name) of the bound parameter type.

Return values

string|ParameterType|Type|ArrayParameterType —

The value of the bound parameter type.

getParameterTypes()

Gets all defined query parameter types for the query being constructed indexed by parameter index or name.


    public
                    getParameterTypes() : array<int<0, max>, string|Type|ParameterType|ArrayParameterType>|array<string, string|Type|ParameterType|ArrayParameterType>

Return values

The currently defined query parameter types indexed by parameter index or name.

getRestrictions()


    public
                    getRestrictions() : QueryRestrictionContainerInterface

Return values

QueryRestrictionContainerInterface

getSelect()

Returns selected fields from internal query state.


    public
                    getSelect() : array<string|int, string>

Internal

only, used for Extbase internal handling and core tests. Don't use it.

Return values

array<string|int, string>

getSQL()

Gets the complete SQL string formed by the current specifications of this QueryBuilder.


    public
                    getSQL() : string

If the statement is a SELECT TYPE query restrictions based on TCA settings will automatically be applied based on the current QuerySettings.

Return values

string —

The SQL query string.

getWhere()

Returns where expressions from internal query state.


    public
                    getWhere() : CompositeExpression|string|null

Internal

only, used for Extbase internal handling and core tests. Don't use it.

Return values

CompositeExpression|string|null

groupBy()

Specifies a grouping over the results of the query.


    public
                    groupBy(string ...$groupBy) : $this

Replaces any previously specified groupings, if any.

Parameters

$groupBy : string: The grouping expression.

Return values

$this —

This QueryBuilder instance.

having()

Specifies a restriction over the groups of the query.


    public
                    having(mixed ...$predicates) : $this

Replaces any previous having restrictions, if any.

Parameters

$predicates : mixed: The restriction over the groups.

Return values

$this —

This QueryBuilder instance.

innerJoin()

Creates and adds a join to the query.


    public
                    innerJoin(string $fromAlias, string $join, string $alias[, string|null $condition = null ]) : $this

Parameters

$fromAlias : string: The alias that points to a from clause.
$join : string: The table name to join.
$alias : string: The alias of the join table.
$condition : string|null = null: The condition for the join.

Return values

$this —

This QueryBuilder instance.

insert()

Turns the query being built into an insert query that inserts into a certain table


    public
                    insert(string $table) : $this

Parameters

$table : string: The table into which the rows should be inserted.

Return values

$this —

This QueryBuilder instance.

join()

Creates and adds a join to the query.


    public
                    join(string $fromAlias, string $join, string $alias[, string|null $condition = null ]) : QueryBuilder

Parameters

$fromAlias : string: The alias that points to a from clause.
$join : string: The table name to join.
$alias : string: The alias of the join table.
$condition : string|null = null: The condition for the join.

Return values

QueryBuilder

leftJoin()

Creates and adds a left join to the query.


    public
                    leftJoin(string $fromAlias, string $join, string $alias[, CompositeExpression|string|null $condition = null ]) : $this

Parameters

$fromAlias : string: The alias that points to a from clause.
$join : string: The table name to join.
$alias : string: The alias of the join table.
$condition : CompositeExpression|string|null = null: The condition for the join.

Return values

$this —

This QueryBuilder instance.

limitRestrictionsToTables()

Limits ALL currently active restrictions of the restriction container to the table aliases given


    public
                    limitRestrictionsToTables(array<string|int, mixed> $tableAliases) : void

Parameters

$tableAliases : array<string|int, mixed>

orderBy()

Specifies an ordering for the query results.


    public
                    orderBy(string $fieldName[, string|null $order = null ]) : $this

Replaces any previously specified orderings, if any.

Parameters

$fieldName : string: The fieldName to order by. Will be quoted according to database platform automatically.
$order : string|null = null: The ordering direction. No automatic quoting/escaping.

Return values

$this —

This QueryBuilder instance.

orHaving()

Adds a restriction over the groups of the query, forming a logical disjunction with any existing having restrictions.


    public
                    orHaving(mixed ...$predicates) : $this

Parameters

$predicates : mixed: The restriction to add.

Return values

$this —

This QueryBuilder instance.

orWhere()

Adds one or more restrictions to the query results, forming a logical disjunction with any previously specified restrictions.


    public
                    orWhere(string|CompositeExpression ...$predicates) : $this

Parameters

$predicates : string|CompositeExpression: The WHERE statement.

Return values

$this —

This QueryBuilder instance.

prepare()

Create prepared statement out of QueryBuilder instance.


    public
                    prepare() : Statement

doctrine/dbal does not provide support for prepared statement in QueryBuilder, but as TYPO3 uses the API throughout the code via QueryBuilder, so the functionality of prepared statements for multiple executions is added.

You should be aware that this method will throw a named 'UnsupportedPreparedStatementParameterTypeException()' exception, if 'PARAM_INT_ARRAY' or 'PARAM_STR_ARRAY' is set, as this is not supported for prepared statements directly.

NamedPlaceholder are not supported, and if one or more are set a 'NamedParameterNotSupportedForPreparedStatementException' will be thrown.

Return values

Statement

quote()

Quotes a given input parameter.


    public
                    quote(string $input) : string

Parameters

$input : string: The parameter to be quoted.

Return values

string —

Often string, but also int or float or similar depending on $input and platform

quoteArrayBasedValueListToIntegerList()

Implode array to comma separated list with database int-quoted values to be used as direct value list for database 'in(...)' or 'notIn(...') expressions. Empty array will return 'NULL' as string to avoid database query failure, as 'IN()' is invalid, but 'IN(NULL)' is fine.


    public
                    quoteArrayBasedValueListToIntegerList(array<string|int, mixed> $values) : string

This method should be used with care, the preferred way is to use placeholders. It is however useful when dealing with potentially many values, which could reach placeholder limit quickly.

When working with prepared statement from QueryBuilder, use this method to proper quote array with integer values.

The method can not be used in queries that re-bind a prepared statement to change values for subsequent execution due to a PDO limitation.

Return value should only be used as value list for database queries 'in()' and 'notIn()' .

Parameters

$values : array<string|int, mixed>

Return values

string

quoteArrayBasedValueListToStringList()

Implode array to comma separated list with database string-quoted values to be used as direct value list for database 'in(...)' or 'notIn(...') expressions. Empty array will return 'NULL' as string to avoid database query failure, as 'IN()' is invalid, but 'IN(NULL)' is fine.


    public
                    quoteArrayBasedValueListToStringList(array<string|int, mixed> $values) : string

This method should be used with care, the preferred way is to use placeholders. It is however useful when dealing with potentially many values, which could reach placeholder limit quickly.

When working with prepared statement from QueryBuilder, use this method to proper quote array with integer values.

The method can not be used in queries that re-bind a prepared statement to change values for subsequent execution due to a PDO limitation.

Return value should only be used as value list for database queries 'in()' and 'notIn()' .

Parameters

$values : array<string|int, mixed>

Return values

string

quoteColumnValuePairs()

Quotes an associative array of column-value so the column names can be safely used, even if the name is a reserved name.


    public
                    quoteColumnValuePairs(array<string|int, mixed> $input) : array<string|int, mixed>

Delimiting style depends on the underlying database platform that is being used.

Parameters

$input : array<string|int, mixed>

Return values

array<string|int, mixed>

quoteIdentifier()

Quotes a string so it can be safely used as a table or column name, even if it is a reserved name.


    public
                    quoteIdentifier(string $identifier) : string

Delimiting style depends on the underlying database platform that is being used.

Parameters

$identifier : string: The name to be quoted.

Return values

string —

The quoted name.

quoteIdentifiers()

Quotes an array of column names so it can be safely used, even if the name is a reserved name.


    public
                    quoteIdentifiers(array<string|int, mixed> $input) : array<string|int, mixed>

Delimiting style depends on the underlying database platform that is being used.

Parameters

$input : array<string|int, mixed>

Return values

array<string|int, mixed>

quoteIdentifiersForSelect()

Quotes an array of column names so it can be safely used, even if the name is a reserved name.


    public
                    quoteIdentifiersForSelect(array<string|int, mixed> $input) : array<string|int, mixed>

Takes into account the special case of the * placeholder that can only be used in SELECT type statements.

Delimiting style depends on the underlying database platform that is being used.

Parameters

$input : array<string|int, mixed>

Return values

array<string|int, mixed>

resetGroupBy()

Resets the grouping for the query.


    public
                    resetGroupBy() : $this

Return values

$this —

This QueryBuilder instance.

resetHaving()

Resets the HAVING conditions for the query.


    public
                    resetHaving() : $this

Return values

$this —

This QueryBuilder instance.

resetOrderBy()

Resets the ordering for the query.


    public
                    resetOrderBy() : $this

Return values

$this —

This QueryBuilder instance.

resetRestrictions()

Re-apply default restrictions


    public
                    resetRestrictions() : void

resetWhere()

Resets the WHERE conditions for the query.


    public
                    resetWhere() : $this

Return values

$this —

This QueryBuilder instance.

rightJoin()

Creates and adds a right join to the query.


    public
                    rightJoin(string $fromAlias, string $join, string $alias[, string|null $condition = null ]) : $this

Parameters

$fromAlias : string: The alias that points to a from clause.
$join : string: The table name to join.
$alias : string: The alias of the join table.
$condition : string|null = null: The condition for the join.

Return values

$this —

This QueryBuilder instance.

select()

Specifies items that are to be returned in the query result.


    public
                    select(string ...$selects) : $this

Replaces any previously specified selections, if any.

Parameters

$selects : string

Return values

$this —

This QueryBuilder instance.

selectLiteral()

Specifies items that are to be returned in the query result.


    public
                    selectLiteral(string ...$selects) : QueryBuilder

Replaces any previously specified selections, if any. This should only be used for literal SQL expressions as no quoting/escaping of any kind will be performed on the items.

Parameters

$selects : string: Literal SQL expressions to be selected. Warning: No quoting will be done!

Return values

QueryBuilder

set()

Sets a new value for a column in a bulk update query.


    public
                    set(string $key, mixed $value[, bool $createNamedParameter = true ][, ParameterType|ArrayParameterType $type = Connection::PARAM_STR ]) : QueryBuilder

Parameters

$key : string: The column to set.
$value : mixed: The value, expression, placeholder, etc.
$createNamedParameter : bool = true: Automatically create a named parameter for the value
$type : ParameterType|ArrayParameterType = Connection::PARAM_STR

Return values

QueryBuilder

setFirstResult()

Sets the position of the first result to retrieve (the "offset").


    public
                    setFirstResult(int $firstResult) : QueryBuilder

Parameters

$firstResult : int: The first result to return.

Return values

QueryBuilder —

This QueryBuilder instance.

setMaxResults()

Sets the maximum number of results to retrieve (the "limit").


    public
                    setMaxResults([int|null $maxResults = null ]) : QueryBuilder

Parameters

$maxResults : int|null = null: The maximum number of results to retrieve or NULL to retrieve all results.

Return values

QueryBuilder —

This QueryBuilder instance.

setParameter()

Sets a query parameter for the query being constructed.


    public
                    setParameter(int<0, max>|string $key, mixed $value[, string|ParameterType|Type|ArrayParameterType $type = ParameterType::STRING ]) : QueryBuilder

Parameters

$key : int<0, max>|string: Parameter position or name
$value : mixed
$type : string|ParameterType|Type|ArrayParameterType = ParameterType::STRING

Return values

QueryBuilder

setParameters()

Sets a collection of query parameters for the query being constructed.


    public
                    setParameters(array<int, mixed>|array<string, mixed> $params[, array<int<0, max>, string|Type|ParameterType|ArrayParameterType>|array<string, string|Type|ParameterType|ArrayParameterType> $types = [] ]) : QueryBuilder

Parameters

$params : array<int, mixed>|array<string, mixed>: The query parameters to set.
$types : array<int<0, max>, string|Type|ParameterType|ArrayParameterType>|array<string, string|Type|ParameterType|ArrayParameterType> = []: The query parameters types to set.

Return values

QueryBuilder —

This QueryBuilder instance.

setRestrictions()


    public
                    setRestrictions(QueryRestrictionContainerInterface $restrictionContainer) : void

Parameters

$restrictionContainer : QueryRestrictionContainerInterface

setValue()

Sets a value for a column in an insert query.


    public
                    setValue(string $column, mixed $value[, bool $createNamedParameter = true ]) : QueryBuilder

Parameters

$column : string: The column into which the value should be inserted.
$value : mixed: The value that should be inserted into the column.
$createNamedParameter : bool = true: Automatically create a named parameter for the value

Return values

QueryBuilder

typo3_addWith()


    public
                    typo3_addWith(string $name, string|QueryBuilder|ConcreteQueryBuilder|QueryBuilder $expression[, array<string|int, string> $fields = [] ][, array<string|int, string> $dependsOn = [] ]) : self

Parameters

$name : string
$expression : string|QueryBuilder|ConcreteQueryBuilder|QueryBuilder
$fields : array<string|int, string> = []
$dependsOn : array<string|int, string> = []

Internal

not part of public API, experimental and may change at any given time.

Return values

self

typo3_addWithRecursive()


    public
                    typo3_addWithRecursive(string $name, bool $uniqueRows, string|QueryBuilder|ConcreteQueryBuilder|QueryBuilder $expression, string|QueryBuilder|ConcreteQueryBuilder|QueryBuilder $initialExpression[, array<string|int, string> $fields = [] ][, array<string|int, string> $dependsOn = [] ]) : self

Parameters

$name : string
$uniqueRows : bool
$expression : string|QueryBuilder|ConcreteQueryBuilder|QueryBuilder
$initialExpression : string|QueryBuilder|ConcreteQueryBuilder|QueryBuilder
$fields : array<string|int, string> = []
$dependsOn : array<string|int, string> = []

Internal

not part of public API, experimental and may change at any given time.

Return values

self

typo3_with()


    public
                    typo3_with(string $name, string|QueryBuilder|ConcreteQueryBuilder|QueryBuilder $expression[, array<string|int, string> $fields = [] ][, array<string|int, string> $dependsOn = [] ]) : self

Parameters

$name : string
$expression : string|QueryBuilder|ConcreteQueryBuilder|QueryBuilder
$fields : array<string|int, string> = []
$dependsOn : array<string|int, string> = []

Internal

not part of public API, experimental and may change at any given time.

Return values

self

typo3_withRecursive()


    public
                    typo3_withRecursive(string $name, bool $uniqueRows, string|QueryBuilder|ConcreteQueryBuilder|QueryBuilder $expression, string|QueryBuilder|ConcreteQueryBuilder|QueryBuilder $initialExpression[, array<string|int, string> $fields = [] ][, array<string|int, string> $dependsOn = [] ]) : self

Parameters

$name : string
$uniqueRows : bool
$expression : string|QueryBuilder|ConcreteQueryBuilder|QueryBuilder
$initialExpression : string|QueryBuilder|ConcreteQueryBuilder|QueryBuilder
$fields : array<string|int, string> = []
$dependsOn : array<string|int, string> = []

Internal

not part of public API, experimental and may change at any given time.

Return values

self

union()

Specifies union parts to be used to build a UNION query.


    public
                    union(string|QueryBuilder|ConcreteQueryBuilder|QueryBuilder $part) : $this

Replaces any previously specified parts.

  $qb = $conn->createQueryBuilder()
    ->union('SELECT 1 AS field1', 'SELECT 2 AS field1');

Parameters

$part : string|QueryBuilder|ConcreteQueryBuilder|QueryBuilder

Return values

$this

update()

Turns the query being built into a bulk update query that ranges over a certain table


    public
                    update(string $table) : $this

Parameters

$table : string: The table whose rows are subject to the update.

Return values

$this —

This QueryBuilder instance.

values()

Specifies values for an insert query indexed by column names.


    public
                    values(array<string|int, mixed> $values[, bool $createNamedParameters = true ]) : QueryBuilder

Replaces any previous values, if any.

Parameters

$values : array<string|int, mixed>: The values to specify for the insert query indexed by column names.
$createNamedParameters : bool = true: Automatically create named parameters for all values

Return values

QueryBuilder

where()

Specifies one or more restrictions to the query result.


    public
                    where(string|CompositeExpression ...$predicates) : $this

Replaces any previously specified restrictions, if any.

Parameters

$predicates : string|CompositeExpression

Return values

$this —

This QueryBuilder instance.

addAdditionalWhereConditions()

Add the additional query conditions returned by the QueryRestrictionBuilder to the current query and return the original set of conditions so that they can be restored after the query has been built/executed.


    protected
                    addAdditionalWhereConditions() : CompositeExpression|string|null

Return values

CompositeExpression|string|null

getCountExpression()


    protected
                    getCountExpression(string $column) : string

Parameters

$column : string

Return values

string

getIdentifierQuoteCharacter()


    protected
                    getIdentifierQuoteCharacter() : string

Internal

This method reflects needed quoted char determination for unquoteSingleIdentifier(). Until doctrine/dbal ^4 the corresponding information has been used from the database platform class which is not available anymore.

Return values

string

getQueriedTables()

Return all tables/aliases used in FROM or JOIN query parts from the query builder.


    protected
                    getQueriedTables() : array<non-empty-string, non-empty-string>

The table names are automatically unquoted. This is a helper for to build the list of queried tables for the AbstractRestrictionContainer.

Return values

array<non-empty-string, non-empty-string>

isEmptyPart()

Determine if a query part used for where or having is empty. Used as array_filter in ConcreteQueryBuilder methods. This is needed to avoid invalid sql syntax by empty parts, which can happen to relaxed custom CompositeExpression handling.


    protected
            static        isEmptyPart(CompositeExpression|string|null $value) : bool

For example used to avoid : (uid = 1) and () and (pid = 2).

Parameters

$value : CompositeExpression|string|null

Return values

bool

unquoteSingleIdentifier()

Unquote a single identifier (no dot expansion). Used to unquote the table names from the expressionBuilder so that the table can be found in the TCA definition.


    protected
                    unquoteSingleIdentifier(string $identifier) : string

Parameters

$identifier : string: The identifier / table name

Return values

string —

The unquoted table name / identifier

QueryBuilder extends ConcreteQueryBuilder

Table of Contents

Properties

Methods

Properties

$additionalRestrictions

$concreteQueryBuilder

$connection read-only

$distinct

$forUpdate

$from

$groupBy

$having

$join

$orderBy

$restrictionContainer

$select

$sql

$type

$typo3_unionParts

$typo3_with

$where

Methods

__clone()

Tags

__construct()

Parameters

__toString()

Return values

addGroupBy()

Parameters

Return values

addOrderBy()

Parameters

Return values

addSelect()

Parameters

Return values

addSelectLiteral()

Parameters

Return values

addUnion()

Parameters

Return values

andHaving()

Parameters

Return values

andWhere()

Parameters

Tags

Return values

castFieldToTextType()

Parameters

Tags

Return values

count()

Parameters

Return values

createNamedParameter()

Parameters

Tags

Return values

createPositionalParameter()

Parameters

Return values

delete()

Parameters

Return values

distinct()

Parameters

Return values

escapeLikeWildcards()

Parameters

Return values

executeQuery()

Return values

executeStatement()

Return values

expr()

Return values