ConcreteQueryBuilder

class \TYPO3\CMS\Core\Database\Query\ConcreteQueryBuilder

Extends
QueryBuilder

QueryBuilder class is responsible to dynamically create SQL queries.

Important: Verify that every feature you use will work with your database vendor. SQL Query Builder does not attempt to validate the generated SQL at all.

The query builder does no validation whatsoever if certain features even work with the underlying database vendor. Limit queries and joins are NOT applied to UPDATE and DELETE statements even if some vendors such as MySQL support it.

Table of Contents

Properties

$connection  : Connection
$distinct  : bool
Whether this is a SELECT DISTINCT query.
$forUpdate  : ForUpdate|null
$from  : From[]
The FROM parts of a SELECT query.
$groupBy  : string[]
The GROUP BY part of a SELECT query.
$having  : string|CompositeExpression|null
The HAVING part of a SELECT query.
$join  : array<string, Join[]>
The list of joins, indexed by from alias.
$orderBy  : string[]
The ORDER BY parts of a SELECT query.
$select  : 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  : 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 all expression objects in the SQL parts.
__construct()  : mixed
Initializes a new <tt>QueryBuilder</tt>.
addGroupBy()  : $this
Adds one or more grouping expressions 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.
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.
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.
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.
getSQL()  : string
groupBy()  : $this
Specifies one or more grouping expressions 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
leftJoin()  : $this
Creates and adds a left join to the query.
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.
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.
resetWhere()  : $this
Resets the WHERE conditions for the query.
rightJoin()  : $this
Creates and adds a right join to the query.
select()  : $this
Specifies an item that is to be returned in the query result.
setFirstResult()  : ConcreteQueryBuilder
setMaxResults()  : ConcreteQueryBuilder
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
where()  : $this
Specifies one or more restrictions to the query result.
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.

Properties

$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 From[] $from = []

$groupBy

The GROUP BY part of a SELECT query.

protected 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, Join[]> $join = []

$orderBy

The ORDER BY parts of a SELECT query.

protected string[] $orderBy = []

$select

The SELECT parts of the query.

protected 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 Union[] $typo3_unionParts = []

$where

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

protected string|CompositeExpression|null $where = null

Methods

__clone()

Deep clone of all expression objects in the SQL parts.

public __clone() : mixed

__construct()

Initializes a new <tt>QueryBuilder</tt>.

public __construct(Connection $connection) : mixed

Parameters

$connection : Connection

The DBAL Connection.

addGroupBy()

Adds one or more grouping expressions to the query.

public addGroupBy(string $expression, string ...$expressions) : $this
$qb = $conn->createQueryBuilder() ->select('u.name') ->from('users', 'u') ->groupBy('u.lastLogin') ->addGroupBy('u.createdAt');

Parameters

$expression : string

The grouping expression

$expressions : string

Additional grouping expressions

Return values

$this —

This QueryBuilder instance.

addOrderBy()

Adds an ordering to the query results.

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

Parameters

$sort : string

The ordering expression.

$order : string = 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 $expression, string ...$expressions) : $this
$qb = $conn->createQueryBuilder() ->select('u.id') ->addSelect('p.id') ->from('users', 'u') ->leftJoin('u', 'phonenumbers', 'u.id = p.user_id');

Parameters

$expression : string

The selection expression.

$expressions : string

Additional selection expressions.

Return values

$this —

This QueryBuilder instance.

addUnion()

Add parts to be used to build a UNION query.

public addUnion(string|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|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(string|CompositeExpression $predicate, string|CompositeExpression ...$predicates) : $this

Parameters

$predicate : string|CompositeExpression

The predicate to append.

$predicates : string|CompositeExpression

Additional predicates 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 $predicate, string|CompositeExpression ...$predicates) : $this
$qb = $conn->createQueryBuilder() ->select('u') ->from('users', 'u') ->where('u.username LIKE ?') ->andWhere('u.is_active = 1');

Parameters

$predicate : string|CompositeExpression

The predicate to append.

$predicates : string|CompositeExpression

Additional predicates to append.

Tags

see
where()

Return values

$this —

This QueryBuilder instance.

delete()

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

public delete(string $table) : $this
$qb = $conn->createQueryBuilder() ->delete('users', 'u') ->where('u.id = :user_id') ->setParameter(':user_id', 1);

Parameters

$table : string

The table whose rows are subject to the deletion.

Return values

$this —

This QueryBuilder instance.

distinct()

Adds or removes DISTINCT to/from the query.

public distinct([bool $distinct = true ]) : $this
$qb = $conn->createQueryBuilder() ->select('u.id') ->distinct() ->from('users', 'u')

Parameters

$distinct : bool = true

Return values

$this —

This QueryBuilder instance.

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
$qb = $conn->createQueryBuilder() ->select('u.id') ->from('users', 'u')

Parameters

$table : string

The table.

$alias : string|null = null

The alias of the table.

Return values

$this —

This QueryBuilder instance.

getSQL()

public getSQL() : string

Return values

string

groupBy()

Specifies one or more grouping expressions over the results of the query.

public groupBy(string $expression, string ...$expressions) : $this

Replaces any previously specified groupings, if any.

$qb = $conn->createQueryBuilder() ->select('u.name') ->from('users', 'u') ->groupBy('u.id');

Parameters

$expression : string

The grouping expression

$expressions : string

Additional grouping expressions

Return values

$this —

This QueryBuilder instance.

having()

Specifies a restriction over the groups of the query.

public having(string|CompositeExpression $predicate, string|CompositeExpression ...$predicates) : $this

Replaces any previous having restrictions, if any.

Parameters

$predicate : string|CompositeExpression

The HAVING clause predicate.

$predicates : string|CompositeExpression

Additional HAVING clause predicates.

Return values

$this —

This QueryBuilder instance.

innerJoin()

Creates and adds a join to the query.

public innerJoin(string $fromAlias, string $join, string $alias[, string $condition = null ]) : $this
$qb = $conn->createQueryBuilder() ->select('u.name') ->from('users', 'u') ->innerJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1');

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

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
$qb = $conn->createQueryBuilder() ->insert('users') ->values( array( 'name' => '?', 'password' => '?' ) );

Parameters

$table : string

The table into which the rows should be inserted.

Return values

$this —

This QueryBuilder instance.

leftJoin()

Creates and adds a left join to the query.

public leftJoin(string $fromAlias, string $join, string $alias[, string $condition = null ]) : $this
$qb = $conn->createQueryBuilder() ->select('u.name') ->from('users', 'u') ->leftJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1');

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

The condition for the join.

Return values

$this —

This QueryBuilder instance.

orderBy()

Specifies an ordering for the query results.

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

Replaces any previously specified orderings, if any.

Parameters

$sort : string

The ordering expression.

$order : string = null

The ordering direction.

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(string|CompositeExpression $predicate, string|CompositeExpression ...$predicates) : $this

Parameters

$predicate : string|CompositeExpression

The predicate to append.

$predicates : string|CompositeExpression

Additional predicates to append.

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 $predicate, string|CompositeExpression ...$predicates) : $this
$qb = $em->createQueryBuilder() ->select('u.name') ->from('users', 'u') ->where('u.id = 1') ->orWhere('u.id = 2');

Parameters

$predicate : string|CompositeExpression

The predicate to append.

$predicates : string|CompositeExpression

Additional predicates to append.

Tags

see
where()

Return values

$this —

This QueryBuilder instance.

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.

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 $condition = null ]) : $this
$qb = $conn->createQueryBuilder() ->select('u.name') ->from('users', 'u') ->rightJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1');

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

The condition for the join.

Return values

$this —

This QueryBuilder instance.

select()

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

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

Replaces any previously specified selections, if any.

$qb = $conn->createQueryBuilder() ->select('u.id', 'p.id') ->from('users', 'u') ->leftJoin('u', 'phonenumbers', 'p', 'u.id = p.user_id');

Parameters

$expressions : string

The selection expressions.

Return values

$this —

This QueryBuilder instance.

typo3_addWith()

public typo3_addWith(string $name, string|QueryBuilder $expression[, string[] $fields = [] ][, string[] $dependsOn = [] ]) : self

Parameters

$name : string
$expression : string|QueryBuilder
$fields : string[] = []
$dependsOn : string[] = []

Return values

self

typo3_addWithRecursive()

public typo3_addWithRecursive(string $name, bool $uniqueRows, string|QueryBuilder $initialExpression, string|QueryBuilder $recursiveExpression[, string[] $fields = [] ][, string[] $dependsOn = [] ]) : self

Parameters

$name : string
$uniqueRows : bool
$initialExpression : string|QueryBuilder
$recursiveExpression : string|QueryBuilder
$fields : string[] = []
$dependsOn : string[] = []

Return values

self

typo3_with()

public typo3_with(string $name, string|QueryBuilder $expression[, string[] $fields = [] ][, string[] $dependsOn = [] ]) : self

Parameters

$name : string
$expression : string|QueryBuilder
$fields : string[] = []
$dependsOn : string[] = []

Return values

self

typo3_withRecursive()

public typo3_withRecursive(string $name, bool $uniqueRows, string|QueryBuilder $initialExpression, string|QueryBuilder $recursiveExpression[, string[] $fields = [] ][, string[] $dependsOn = [] ]) : self

Parameters

$name : string
$uniqueRows : bool
$initialExpression : string|QueryBuilder
$recursiveExpression : string|QueryBuilder
$fields : string[] = []
$dependsOn : string[] = []

Return values

self

union()

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

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

Replaces any previously specified parts.

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

Parameters

$part : string|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
$qb = $conn->createQueryBuilder() ->update('counters', 'c') ->set('c.value', 'c.value + 1') ->where('c.id = ?');

Parameters

$table : string

The table whose rows are subject to the update.

Return values

$this —

This QueryBuilder instance.

where()

Specifies one or more restrictions to the query result.

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

Replaces any previously specified restrictions, if any.

$qb = $conn->createQueryBuilder() ->select('c.value') ->from('counters', 'c') ->where('c.id = ?');
// You can optionally programmatically build and/or expressions
$qb = $conn->createQueryBuilder();

$or = $qb->expr()->orx();
$or->add($qb->expr()->eq('c.id', 1));
$or->add($qb->expr()->eq('c.id', 2));

$qb->update('counters', 'c')
    ->set('c.value', 'c.value + 1')
    ->where($or);

Parameters

$predicate : string|CompositeExpression

The WHERE clause predicate.

$predicates : string|CompositeExpression

Additional WHERE clause predicates.

Return values

$this —

This QueryBuilder instance.

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

Tags

see
ConcreteQueryBuilder::createPredicate()
see
ConcreteQueryBuilder::appendToPredicate()
see
CompositeExpression::isEmptyPart()

Return values

bool
On this page