class QueryBuilder (View source)

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.

Constants

SELECT

DELETE

UPDATE

STATE_DIRTY

The builder states.

STATE_CLEAN

Methods

__construct(Connection $connection)

Initializes a new QueryBuilder.

expr()

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

integer
getType()

Get the type of the currently built query.

getConnection()

Get the associated DBAL Connection for this query builder.

integer
getState()

Get the state of this query builder instance.

mixed
execute()

Execute this query using the bound parameters and their types.

string
getSQL()

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

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

Sets a query parameter for the query being constructed.

setParameters(array $params, array $types = array())

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

array
getParameters()

Gets all defined query parameters for the query being constructed.

mixed
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 $sqlPartName, string $sqlPart, boolean $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.

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 table.

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

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

from(string $from, string $alias)

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

join(string $fromAlias, string $join, string $alias, string $condition = null)

Creates and adds a join to the query.

innerJoin(string $fromAlias, string $join, string $alias, string $condition = null)

Creates and adds a join to the query.

leftJoin(string $fromAlias, string $join, string $alias, string $condition = null)

Creates and adds a left join to the query.

rightJoin(string $fromAlias, string $join, string $alias, string $condition = null)

Creates and adds a right join to the query.

set(string $key, string $value)

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

where(mixed $predicates)

Specifies one or more restrictions to the query result.

andWhere(mixed $where)

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

orWhere(mixed $where)

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

groupBy(mixed $groupBy)

Specifies a grouping over the results of the query.

addGroupBy(mixed $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 $sort, string $order = null)

Specifies an ordering for the query results.

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

Adds an ordering to the query results.

mixed
getQueryPart(string $queryPartName)

Get a query part by its name.

array
getQueryParts()

Get all query parts.

resetQueryParts(array $queryPartNames = null)

Reset SQL parts

resetQueryPart(string $queryPartName)

Reset single SQL part

string
__toString()

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

string
createNamedParameter(mixed $value, mixed $type = \PDO::PARAM_STR, string $placeHolder = null)

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

string
createPositionalParameter(mixed $value, mixed $type = \PDO::PARAM_STR)

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

Details

__construct(Connection $connection)

Initializes a new QueryBuilder.

Parameters

Connection $connection DBAL Connection

ExpressionBuilder expr()

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

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

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

ExpressionBuilder

integer getType()

Get the type of the currently built query.

Return Value

integer

Connection getConnection()

Get the associated DBAL Connection for this query builder.

Return Value

Connection

integer getState()

Get the state of this query builder instance.

Return Value

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

mixed execute()

Execute this query using the bound parameters and their types.

Uses {see Connection::executeQuery} for select statements and {see Connection::executeUpdate} for insert, update and delete statements.

Return Value

mixed

string getSQL()

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

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

Return Value

string The sql query string.

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

Sets a query parameter for the query being constructed.

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

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder setParameters(array $params, array $types = array())

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

$qb = $conn->createQueryBuilder() ->select('u') ->from('users', 'u') ->where('u.id = :user_id1 OR u.id = :user_id2') ->setParameters(array( ':user_id1' => 1, ':user_id2' => 2 ));

Parameters

array $params The query parameters to set.
array $types The query parameters types to set.

Return Value

QueryBuilder This QueryBuilder instance.

array getParameters()

Gets all defined query parameters for the query being constructed.

Return Value

array The currently defined query parameters.

mixed 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

mixed 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 $sqlPartName, string $sqlPart, boolean $append = false)

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

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

Parameters

string $sqlPartName
string $sqlPart
boolean $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 = $conn->createQueryBuilder() ->select('u.id', 'p.id') ->from('users', 'u') ->leftJoin('u', 'phonenumbers', 'p', 'u.id = p.user_id');

Parameters

mixed $select The selection expressions.

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder addSelect(mixed $select = null)

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

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

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 table.

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

Parameters

string $delete The table whose rows are subject to the deletion.
string $alias The table 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 table

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

Parameters

string $update The table whose rows are subject to the update.
string $alias The table alias used in the constructed query.

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder from(string $from, string $alias)

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

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

Parameters

string $from The table
string $alias The alias of the table

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder join(string $fromAlias, string $join, string $alias, string $condition = null)

Creates and adds a join to the query.

$qb = $conn->createQueryBuilder() ->select('u.name') ->from('users', 'u') ->join('u', 'phonenumbers', 'p', 'p.is_primary = 1');

Parameters

string $fromAlias The alias that points to a from clause
string $join The table name to join
string $alias The alias of the join table
string $condition The condition for the join

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder innerJoin(string $fromAlias, string $join, string $alias, string $condition = null)

Creates and adds a join to the query.

$qb = $conn->createQueryBuilder() ->select('u.name') ->from('users', 'u') ->innerJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1');

Parameters

string $fromAlias The alias that points to a from clause
string $join The table name to join
string $alias The alias of the join table
string $condition The condition for the join

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder leftJoin(string $fromAlias, string $join, string $alias, string $condition = null)

Creates and adds a left join to the query.

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

Parameters

string $fromAlias The alias that points to a from clause
string $join The table name to join
string $alias The alias of the join table
string $condition The condition for the join

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder rightJoin(string $fromAlias, string $join, string $alias, string $condition = null)

Creates and adds a right join to the query.

$qb = $conn->createQueryBuilder() ->select('u.name') ->from('users', 'u') ->rightJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1');

Parameters

string $fromAlias The alias that points to a from clause
string $join The table name to join
string $alias The alias of the join table
string $condition The condition for the join

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder set(string $key, string $value)

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

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

Parameters

string $key The column 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 = $conn->createQueryBuilder() ->select('u.name') ->from('users', 'u') ->where('u.id = ?');

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

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

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

Parameters

mixed $predicates The restriction predicates.

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder andWhere(mixed $where)

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

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

Parameters

mixed $where The query restrictions.

Return Value

QueryBuilder This QueryBuilder instance.

See also

where()

QueryBuilder orWhere(mixed $where)

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

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

Parameters

mixed $where The WHERE statement

Return Value

QueryBuilder $qb

See also

where()

QueryBuilder groupBy(mixed $groupBy)

Specifies a grouping over the results of the query.

Replaces any previously specified groupings, if any.

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

Parameters

mixed $groupBy The grouping expression.

Return Value

QueryBuilder This QueryBuilder instance.

QueryBuilder addGroupBy(mixed $groupBy)

Adds a grouping expression to the query.

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

Parameters

mixed $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 $sort, string $order = null)

Specifies an ordering for the query results.

Replaces any previously specified orderings, if any.

Parameters

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

Return Value

QueryBuilder This QueryBuilder instance.

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

Adds an ordering to the query results.

Parameters

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

Return Value

QueryBuilder This QueryBuilder instance.

mixed getQueryPart(string $queryPartName)

Get a query part by its name.

Parameters

string $queryPartName

Return Value

mixed $queryPart

array getQueryParts()

Get all query parts.

Return Value

array $sqlParts

QueryBuilder resetQueryParts(array $queryPartNames = null)

Reset SQL parts

Parameters

array $queryPartNames

Return Value

QueryBuilder

QueryBuilder resetQueryPart(string $queryPartName)

Reset single SQL part

Parameters

string $queryPartName

Return Value

QueryBuilder

string __toString()

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

Return Value

string The string representation of this QueryBuilder.

string createNamedParameter(mixed $value, mixed $type = \PDO::PARAM_STR, string $placeHolder = null)

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

This method provides a shortcut for PDOStatement::bindValue when using prepared statements.

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

For more information see {@link http://php.net/pdostatement-bindparam}

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

Parameters

mixed $value
mixed $type
string $placeHolder the name to bind with. The string must start with a colon ':'.

Return Value

string the placeholder name used.

string createPositionalParameter(mixed $value, mixed $type = \PDO::PARAM_STR)

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

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', PDO::PARAM_STR)) ->orWhere('u.username = ' . $qb->createPositionalParameter('Bar', PDO::PARAM_STR))

Parameters

mixed $value
mixed $type

Return Value

string