class QueryBuilder (View source)

This class is responsible for building DQL query strings via an object oriented PHP interface.

Constants

SELECT

DELETE

UPDATE

STATE_DIRTY

STATE_CLEAN

Properties

protected boolean $cacheable Whether to use second level cache, if available.
protected string|null $cacheRegion Second level cache region name.
protected integer|null $cacheMode Second level query cache mode.
protected integer $lifetime

Methods

__construct(EntityManagerInterface $em)

Initializes a new QueryBuilder that uses the given EntityManager.

expr()

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

setCacheable(boolean $cacheable)

Enable/disable second level query (result) caching for this query.

boolean
isCacheable()

No description

setCacheRegion(string $cacheRegion)

No description

The
getCacheRegion()

Obtain the name of the second level query cache region in which query results will be stored

integer
getLifetime()

No description

setLifetime(integer $lifetime)

Sets the life-time for this query into second level cache.

integer
getCacheMode()

No description

setCacheMode(integer $cacheMode)

No description

integer
getType()

Gets the type of the currently built query.

getEntityManager()

Gets the associated EntityManager for this query builder.

integer
getState()

Gets the state of this query builder instance.

string
getDQL()

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

getQuery()

Constructs a Query instance from the current specifications of the builder.

string
getRootAlias() deprecated

Gets the FIRST root alias of the query. This is the first entity alias involved in the construction of the query.

array
getRootAliases()

Gets the root aliases of the query. This is the entity aliases involved in the construction of the query.

array
getAllAliases()

Gets all the aliases that have been used in the query.

array
getRootEntities()

Gets the root entities of the query. This is the entity aliases involved in the construction of the query.

setParameter(string|integer $key, mixed $value, string|null $type = null)

Sets a query parameter for the query being constructed.

setParameters(ArrayCollection|array $parameters)

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

ArrayCollection
getParameters()

Gets all defined query parameters for the query being constructed.

Parameter|null
getParameter(mixed $key)

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

setFirstResult(integer $firstResult)

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

integer
getFirstResult()

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

setMaxResults(integer $maxResults)

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

integer
getMaxResults()

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

add(string $dqlPartName, Base $dqlPart, bool $append = false)

Either appends to or replaces a single, generic query part.

select(mixed $select = null)

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

distinct(bool $flag = true)

Adds a DISTINCT flag to this query.

addSelect(mixed $select = null)

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

delete(string $delete = null, string $alias = null)

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

update(string $update = null, string $alias = null)

Turns the query being built into a bulk update query that ranges over a certain entity type.

from(string $from, string $alias, string $indexBy = null)

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

indexBy(string $alias, string $indexBy)

Updates a query root corresponding to an entity setting its index by. This method is intended to be used with EntityRepository->createQueryBuilder(), which creates the initial FROM clause and do not allow you to update it setting an index by.

join(string $join, string $alias, string|null $conditionType = null, string|null $condition = null, string|null $indexBy = null)

Creates and adds a join over an entity association to the query.

innerJoin(string $join, string $alias, string|null $conditionType = null, string|null $condition = null, string|null $indexBy = null)

Creates and adds a join over an entity association to the query.

leftJoin(string $join, string $alias, string|null $conditionType = null, string|null $condition = null, string|null $indexBy = null)

Creates and adds a left join over an entity association to the query.

set(string $key, string $value)

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

where(mixed $predicates)

Specifies one or more restrictions to the query result.

andWhere()

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

orWhere()

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

groupBy(string $groupBy)

Specifies a grouping over the results of the query.

addGroupBy(string $groupBy)

Adds a grouping expression to the query.

having(mixed $having)

Specifies a restriction over the groups of the query.

andHaving(mixed $having)

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

orHaving(mixed $having)

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

orderBy(string|OrderBy $sort, string $order = null)

Specifies an ordering for the query results.

addOrderBy(string|OrderBy $sort, string $order = null)

Adds an ordering to the query results.

addCriteria(Criteria $criteria)

Adds criteria to the query.

mixed
getDQLPart(string $queryPartName)

Gets a query part by its name.

array
getDQLParts()

Gets all query parts.

resetDQLParts(array|null $parts = null)

Resets DQL parts.

resetDQLPart(string $part)

Resets single DQL part.

string
__toString()

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

void
__clone()

Deep clones all expression objects in the DQL parts.

Details

__construct(EntityManagerInterface $em)

Initializes a new QueryBuilder that uses the given EntityManager.

Parameters

EntityManagerInterface $em The EntityManager to use.

Expr expr()

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

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

$qb = $em->createQueryBuilder(); $qb ->select('u') ->from('User', 'u') ->where($qb->expr()->eq('u.id', 1));

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

Return Value

Expr

AbstractQuery setCacheable(boolean $cacheable)

Enable/disable second level query (result) caching for this query.

Parameters

boolean $cacheable

Return Value

AbstractQuery This query instance.

boolean isCacheable()

Return Value

boolean TRUE if the query results are enable for second level cache, FALSE otherwise.

AbstractQuery setCacheRegion(string $cacheRegion)

Parameters

string $cacheRegion

Return Value

AbstractQuery This query instance.

The getCacheRegion()

Obtain the name of the second level query cache region in which query results will be stored

Return Value

The cache region name; NULL indicates the default region.

integer getLifetime()

Return Value

integer

AbstractQuery setLifetime(integer $lifetime)

Sets the life-time for this query into second level cache.

Parameters

integer $lifetime

Return Value

AbstractQuery This query instance.

integer getCacheMode()

Return Value

integer

AbstractQuery setCacheMode(integer $cacheMode)

Parameters

integer $cacheMode

Return Value

AbstractQuery This query instance.

integer getType()

Gets the type of the currently built query.

Return Value

integer

EntityManager getEntityManager()

Gets the associated EntityManager for this query builder.

Return Value

EntityManager

integer getState()

Gets the state of this query builder instance.

Return Value

integer Either QueryBuilder::STATE_DIRTY or QueryBuilder::STATE_CLEAN.

string getDQL()

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

$qb = $em->createQueryBuilder() ->select('u') ->from('User', 'u'); echo $qb->getDql(); // SELECT u FROM User u

Return Value

string The DQL query string.

Query getQuery()

Constructs a Query instance from the current specifications of the builder.

$qb = $em->createQueryBuilder() ->select('u') ->from('User', 'u'); $q = $qb->getQuery(); $results = $q->execute();

Return Value

Query

string getRootAlias() deprecated

deprecated Please use $qb->getRootAliases() instead.

Gets the FIRST root alias of the query. This is the first entity alias involved in the construction of the query.

$qb = $em->createQueryBuilder() ->select('u') ->from('User', 'u');

echo $qb->getRootAlias(); // u

Return Value

string

Exceptions

RuntimeException

array getRootAliases()

Gets the root aliases of the query. This is the entity aliases involved in the construction of the query.

$qb = $em->createQueryBuilder() ->select('u') ->from('User', 'u');

$qb->getRootAliases(); // array('u')

Return Value

array

array getAllAliases()

Gets all the aliases that have been used in the query.

Including all select root aliases and join aliases

$qb = $em->createQueryBuilder() ->select('u') ->from('User', 'u') ->join('u.articles','a';

$qb->getAllAliases(); // array('u','a')

Return Value

array

array getRootEntities()

Gets the root entities of the query. This is the entity aliases involved in the construction of the query.

$qb = $em->createQueryBuilder() ->select('u') ->from('User', 'u');

$qb->getRootEntities(); // array('User')

Return Value

array

QueryBuilder setParameter(string|integer $key, mixed $value, string|null $type = null)

Sets a query parameter for the query being constructed.

$qb = $em->createQueryBuilder() ->select('u') ->from('User', 'u') ->where('u.id = :user_id') ->setParameter('user_id', 1);

Parameters

string|integer $key The parameter position or name.
mixed $value The parameter value.
string|null $type PDO::PARAM_* or \Doctrine\DBAL\Types\Type::* constant

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder setParameters(ArrayCollection|array $parameters)

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

$qb = $em->createQueryBuilder() ->select('u') ->from('User', 'u') ->where('u.id = :user_id1 OR u.id = :user_id2') ->setParameters(new ArrayCollection(array( new Parameter('user_id1', 1), new Parameter('user_id2', 2) )));

Parameters

ArrayCollection|array $parameters The query parameters to set.

Return Value

QueryBuilder This QueryBuilder instance.

ArrayCollection getParameters()

Gets all defined query parameters for the query being constructed.

Return Value

ArrayCollection The currently defined query parameters.

Parameter|null getParameter(mixed $key)

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

Parameters

mixed $key The key (index or name) of the bound parameter.

Return Value

Parameter|null The value of the bound parameter.

QueryBuilder setFirstResult(integer $firstResult)

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

Parameters

integer $firstResult The first result to return.

Return Value

QueryBuilder This QueryBuilder instance.

integer getFirstResult()

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

Returns NULL if {@link setFirstResult} was not applied to this QueryBuilder.

Return Value

integer The position of the first result.

QueryBuilder setMaxResults(integer $maxResults)

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

Parameters

integer $maxResults The maximum number of results to retrieve.

Return Value

QueryBuilder This QueryBuilder instance.

integer getMaxResults()

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

Returns NULL if {@link setMaxResults} was not applied to this query builder.

Return Value

integer Maximum number of results.

QueryBuilder add(string $dqlPartName, Base $dqlPart, bool $append = false)

Either appends to or replaces a single, generic query part.

The available parts are: 'select', 'from', 'join', 'set', 'where', 'groupBy', 'having' and 'orderBy'.

Parameters

string $dqlPartName
Base $dqlPart
bool $append

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder select(mixed $select = null)

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

Replaces any previously specified selections, if any.

$qb = $em->createQueryBuilder() ->select('u', 'p') ->from('User', 'u') ->leftJoin('u.Phonenumbers', 'p');

Parameters

mixed $select The selection expressions.

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder distinct(bool $flag = true)

Adds a DISTINCT flag to this query.

$qb = $em->createQueryBuilder() ->select('u') ->distinct() ->from('User', 'u');

Parameters

bool $flag

Return Value

QueryBuilder

QueryBuilder addSelect(mixed $select = null)

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

$qb = $em->createQueryBuilder() ->select('u') ->addSelect('p') ->from('User', 'u') ->leftJoin('u.Phonenumbers', 'p');

Parameters

mixed $select The selection expression.

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder delete(string $delete = null, string $alias = null)

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

$qb = $em->createQueryBuilder() ->delete('User', 'u') ->where('u.id = :user_id') ->setParameter('user_id', 1);

Parameters

string $delete The class/type whose instances are subject to the deletion.
string $alias The class/type alias used in the constructed query.

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder update(string $update = null, string $alias = null)

Turns the query being built into a bulk update query that ranges over a certain entity type.

$qb = $em->createQueryBuilder() ->update('User', 'u') ->set('u.password', md5('password')) ->where('u.id = ?');

Parameters

string $update The class/type whose instances are subject to the update.
string $alias The class/type alias used in the constructed query.

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder from(string $from, string $alias, string $indexBy = null)

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

$qb = $em->createQueryBuilder() ->select('u') ->from('User', 'u');

Parameters

string $from The class name.
string $alias The alias of the class.
string $indexBy The index for the from.

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder indexBy(string $alias, string $indexBy)

Updates a query root corresponding to an entity setting its index by. This method is intended to be used with EntityRepository->createQueryBuilder(), which creates the initial FROM clause and do not allow you to update it setting an index by.

$qb = $userRepository->createQueryBuilder('u') ->indexBy('u', 'u.id');

// Is equivalent to...

$qb = $em->createQueryBuilder()
    ->select('u')
    ->from('User', 'u', 'u.id');

Parameters

string $alias The root alias of the class.
string $indexBy The index for the from.

Return Value

QueryBuilder This QueryBuilder instance.

Exceptions

QueryException

QueryBuilder join(string $join, string $alias, string|null $conditionType = null, string|null $condition = null, string|null $indexBy = null)

Creates and adds a join over an entity association to the query.

The entities in the joined association will be fetched as part of the query result if the alias used for the joined association is placed in the select expressions.

$qb = $em->createQueryBuilder() ->select('u') ->from('User', 'u') ->join('u.Phonenumbers', 'p', Expr\Join::WITH, 'p.is_primary = 1');

Parameters

string $join The relationship to join.
string $alias The alias of the join.
string|null $conditionType The condition type constant. Either ON or WITH.
string|null $condition The condition for the join.
string|null $indexBy The index for the join.

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder innerJoin(string $join, string $alias, string|null $conditionType = null, string|null $condition = null, string|null $indexBy = null)

Creates and adds a join over an entity association to the query.

The entities in the joined association will be fetched as part of the query result if the alias used for the joined association is placed in the select expressions.

[php]
$qb = $em->createQueryBuilder()
    ->select('u')
    ->from('User', 'u')
    ->innerJoin('u.Phonenumbers', 'p', Expr\Join::WITH, 'p.is_primary = 1');

Parameters

string $join The relationship to join.
string $alias The alias of the join.
string|null $conditionType The condition type constant. Either ON or WITH.
string|null $condition The condition for the join.
string|null $indexBy The index for the join.

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder leftJoin(string $join, string $alias, string|null $conditionType = null, string|null $condition = null, string|null $indexBy = null)

Creates and adds a left join over an entity association to the query.

The entities in the joined association will be fetched as part of the query result if the alias used for the joined association is placed in the select expressions.

$qb = $em->createQueryBuilder() ->select('u') ->from('User', 'u') ->leftJoin('u.Phonenumbers', 'p', Expr\Join::WITH, 'p.is_primary = 1');

Parameters

string $join The relationship to join.
string $alias The alias of the join.
string|null $conditionType The condition type constant. Either ON or WITH.
string|null $condition The condition for the join.
string|null $indexBy The index for the join.

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder set(string $key, string $value)

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

$qb = $em->createQueryBuilder() ->update('User', 'u') ->set('u.password', md5('password')) ->where('u.id = ?');

Parameters

string $key The key/field to set.
string $value The value, expression, placeholder, etc.

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder where(mixed $predicates)

Specifies one or more restrictions to the query result.

Replaces any previously specified restrictions, if any.

$qb = $em->createQueryBuilder() ->select('u') ->from('User', 'u') ->where('u.id = ?');

// You can optionally programatically build and/or expressions
$qb = $em->createQueryBuilder();

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

$qb->update('User', 'u')
    ->set('u.password', md5('password'))
    ->where($or);

Parameters

mixed $predicates The restriction predicates.

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder andWhere()

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

$qb = $em->createQueryBuilder() ->select('u') ->from('User', 'u') ->where('u.username LIKE ?') ->andWhere('u.is_active = 1');

Return Value

QueryBuilder This QueryBuilder instance.

See also

where()

QueryBuilder orWhere()

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

$qb = $em->createQueryBuilder() ->select('u') ->from('User', 'u') ->where('u.id = 1') ->orWhere('u.id = 2');

Return Value

QueryBuilder

See also

where()

QueryBuilder groupBy(string $groupBy)

Specifies a grouping over the results of the query.

Replaces any previously specified groupings, if any.

$qb = $em->createQueryBuilder() ->select('u') ->from('User', 'u') ->groupBy('u.id');

Parameters

string $groupBy The grouping expression.

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder addGroupBy(string $groupBy)

Adds a grouping expression to the query.

$qb = $em->createQueryBuilder() ->select('u') ->from('User', 'u') ->groupBy('u.lastLogin') ->addGroupBy('u.createdAt');

Parameters

string $groupBy The grouping expression.

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder having(mixed $having)

Specifies a restriction over the groups of the query.

Replaces any previous having restrictions, if any.

Parameters

mixed $having The restriction over the groups.

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder andHaving(mixed $having)

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

Parameters

mixed $having The restriction to append.

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder orHaving(mixed $having)

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

Parameters

mixed $having The restriction to add.

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder orderBy(string|OrderBy $sort, string $order = null)

Specifies an ordering for the query results.

Replaces any previously specified orderings, if any.

Parameters

string|OrderBy $sort The ordering expression.
string $order The ordering direction.

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder addOrderBy(string|OrderBy $sort, string $order = null)

Adds an ordering to the query results.

Parameters

string|OrderBy $sort The ordering expression.
string $order The ordering direction.

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder addCriteria(Criteria $criteria)

Adds criteria to the query.

Adds where expressions with AND operator. Adds orderings. Overrides firstResult and maxResults if they're set.

Parameters

Criteria $criteria

Return Value

QueryBuilder

Exceptions

QueryException

mixed getDQLPart(string $queryPartName)

Gets a query part by its name.

Parameters

string $queryPartName

Return Value

mixed $queryPart

array getDQLParts()

Gets all query parts.

Return Value

array $dqlParts

QueryBuilder resetDQLParts(array|null $parts = null)

Resets DQL parts.

Parameters

array|null $parts

Return Value

QueryBuilder

QueryBuilder resetDQLPart(string $part)

Resets single DQL part.

Parameters

string $part

Return Value

QueryBuilder

string __toString()

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

Return Value

string The string representation of this QueryBuilder.

void __clone()

Deep clones all expression objects in the DQL parts.

Return Value

void