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.

Internal

not part of public core API. Uses as intermediate decorator wrapper to keep track of state, which is considered internal and therefore by Doctrine DBAL but TYPO3 requires internal access.

Table of Contents

Properties

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

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

$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[, array<string|int, string> $fields = [] ][, array<string|int, string> $dependsOn = [] ]) : self
Parameters
$name : string
$expression : string|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 $initialExpression, string|QueryBuilder $recursiveExpression[, array<string|int, string> $fields = [] ][, array<string|int, string> $dependsOn = [] ]) : self
Parameters
$name : string
$uniqueRows : bool
$initialExpression : string|QueryBuilder
$recursiveExpression : string|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 $expression[, array<string|int, string> $fields = [] ][, array<string|int, string> $dependsOn = [] ]) : self
Parameters
$name : string
$expression : string|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 $initialExpression, string|QueryBuilder $recursiveExpression[, array<string|int, string> $fields = [] ][, array<string|int, string> $dependsOn = [] ]) : self
Parameters
$name : string
$uniqueRows : bool
$initialExpression : string|QueryBuilder
$recursiveExpression : string|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|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

        
    


            

                    

        

    

        

            
Search results