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

INSERT

STATE_DIRTY

STATE_CLEAN

Methods

__construct(Connection $connection)

Initializes a new QueryBuilder.

expr()

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

int
getType()

Gets the type of the currently built query.

getConnection()

Gets the associated DBAL Connection for this query builder.

int
getState()

Gets the state of this query builder instance.

Statement|int
execute()

Executes this query using the bound parameters and their types.

string
getSQL()

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

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

Sets a query parameter for the query being constructed.

$this
setParameters(array $params, array $types = [])

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

array
getParameters()

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

mixed
getParameter(mixed $key)

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

array
getParameterTypes()

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

mixed
getParameterType(mixed $key)

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

$this
setFirstResult(int $firstResult)

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

int
getFirstResult()

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

$this
setMaxResults(int $maxResults)

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

int
getMaxResults()

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

$this
add(string $sqlPartName, string $sqlPart, bool $append = false)

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

$this
select(mixed $select = null)

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

$this
addSelect(mixed $select = null)

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

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

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

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

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

$this
insert(string $insert = null)

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

$this
from(string $from, string|null $alias = null)

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

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

Creates and adds a join to the query.

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

Creates and adds a join to the query.

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

Creates and adds a left join to the query.

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

Creates and adds a right join to the query.

$this
set(string $key, string $value)

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

$this
where(mixed $predicates)

Specifies one or more restrictions to the query result.

$this
andWhere(mixed $where)

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

$this
orWhere(mixed $where)

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

$this
groupBy(mixed $groupBy)

Specifies a grouping over the results of the query.

$this
addGroupBy(mixed $groupBy)

Adds a grouping expression to the query.

$this
setValue(string $column, string $value)

Sets a value for a column in an insert query.

$this
values(array $values)

Specifies values for an insert query indexed by column names.

$this
having(mixed $having)

Specifies a restriction over the groups of the query.

$this
andHaving(mixed $having)

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

$this
orHaving(mixed $having)

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

$this
orderBy(string $sort, string $order = null)

Specifies an ordering for the query results.

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

Adds an ordering to the query results.

mixed
getQueryPart(string $queryPartName)

Gets a query part by its name.

array
getQueryParts()

Gets all query parts.

$this
resetQueryParts(array|null $queryPartNames = null)

Resets SQL parts.

$this
resetQueryPart(string $queryPartName)

Resets a 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 = ParameterType::STRING, string $placeHolder = null)

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

string
createPositionalParameter(mixed $value, int $type = ParameterType::STRING)

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

void
__clone()

Deep clone of all expression objects in the SQL parts.

Details

__construct(Connection $connection)

Initializes a new QueryBuilder.

Parameters

Connection $connection The 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

int getType()

Gets the type of the currently built query.

Return Value

int

Connection getConnection()

Gets the associated DBAL Connection for this query builder.

Return Value

Connection

int getState()

Gets the state of this query builder instance.

Return Value

int Either QueryBuilder::STATE_DIRTY or QueryBuilder::STATE_CLEAN.

Statement|int execute()

Executes 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

Statement|int

string getSQL()

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

$this setParameter(string|int $key, mixed $value, string|int|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|int $key The parameter position or name.
mixed $value The parameter value.
string|int|null $type One of the {@link \Doctrine\DBAL\ParameterType} constants.

Return Value

$this This QueryBuilder instance.

$this setParameters(array $params, array $types = [])

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

$this This QueryBuilder instance.

array getParameters()

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

Return Value

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

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.

array getParameterTypes()

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

Return Value

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

mixed getParameterType(mixed $key)

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

Parameters

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

Return Value

mixed The value of the bound parameter type.

$this setFirstResult(int $firstResult)

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

Parameters

int $firstResult The first result to return.

Return Value

$this This QueryBuilder instance.

int 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

int The position of the first result.

$this setMaxResults(int $maxResults)

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

Parameters

int $maxResults The maximum number of results to retrieve.

Return Value

$this This QueryBuilder instance.

int 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

int The maximum number of results.

$this add(string $sqlPartName, string $sqlPart, bool $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
bool $append

Return Value

$this This QueryBuilder instance.

$this 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

$this This QueryBuilder instance.

$this 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

$this This QueryBuilder instance.

$this 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

$this This QueryBuilder instance.

$this 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.last_login', 'NOW()') ->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

$this This QueryBuilder instance.

$this insert(string $insert = null)

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

$qb = $conn->createQueryBuilder() ->insert('users') ->values( array( 'name' => '?', 'password' => '?' ) );

Parameters

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

Return Value

$this This QueryBuilder instance.

$this from(string $from, string|null $alias = null)

Creates and adds 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|null $alias The alias of the table.

Return Value

$this This QueryBuilder instance.

$this 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

$this This QueryBuilder instance.

$this 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

$this This QueryBuilder instance.

$this 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

$this This QueryBuilder instance.

$this 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

$this This QueryBuilder instance.

$this 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.last_login', 'NOW()') ->where('u.id = ?');

Parameters

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

Return Value

$this This QueryBuilder instance.

$this 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.last_login', 'NOW()')
    ->where($or);

Parameters

mixed $predicates The restriction predicates.

Return Value

$this This QueryBuilder instance.

$this 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

$this This QueryBuilder instance.

See also

where()

$this 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

$this This QueryBuilder instance.

See also

where()

$this 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

$this This QueryBuilder instance.

$this 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

$this This QueryBuilder instance.

$this setValue(string $column, string $value)

Sets a value for a column in an insert query.

$qb = $conn->createQueryBuilder() ->insert('users') ->values( array( 'name' => '?' ) ) ->setValue('password', '?');

Parameters

string $column The column into which the value should be inserted.
string $value The value that should be inserted into the column.

Return Value

$this This QueryBuilder instance.

$this values(array $values)

Specifies values for an insert query indexed by column names.

Replaces any previous values, if any.

$qb = $conn->createQueryBuilder() ->insert('users') ->values( array( 'name' => '?', 'password' => '?' ) );

Parameters

array $values The values to specify for the insert query indexed by column names.

Return Value

$this This QueryBuilder instance.

$this 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

$this This QueryBuilder instance.

$this 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

$this This QueryBuilder instance.

$this 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

$this This QueryBuilder instance.

$this 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

$this This QueryBuilder instance.

$this 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

$this This QueryBuilder instance.

mixed getQueryPart(string $queryPartName)

Gets a query part by its name.

Parameters

string $queryPartName

Return Value

mixed

array getQueryParts()

Gets all query parts.

Return Value

array

$this resetQueryParts(array|null $queryPartNames = null)

Resets SQL parts.

Parameters

array|null $queryPartNames

Return Value

$this This QueryBuilder instance.

$this resetQueryPart(string $queryPartName)

Resets a single SQL part.

Parameters

string $queryPartName

Return Value

$this This QueryBuilder instance.

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 = ParameterType::STRING, string $placeHolder = null)

Creates 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, int $type = ParameterType::STRING)

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

Parameters

mixed $value
int $type

Return Value

string

void __clone()

Deep clone of all expression objects in the SQL parts.

Return Value

void