Doctrine features a powerful query builder for the SQL language. This QueryBuilder object has methods to add parts to an SQL statement. If you built the complete state you can execute it using the connection it was generated from. The API is roughly the same as that of the DQL Query Builder.
You can access the QueryBuilder by calling Doctrine\DBAL\Connection#createQueryBuilder
:
<?php
$conn = DriverManager::getConnection([/*..*/]);
$queryBuilder = $conn->createQueryBuilder();
It is important to understand how the query builder works in terms of
preventing SQL injection. Because SQL allows expressions in almost
every clause and position the Doctrine QueryBuilder can only prevent
SQL injections for calls to the methods setFirstResult()
and
setMaxResults()
.
All other methods cannot distinguish between user- and developer input and are therefore subject to the possibility of SQL injection.
To safely work with the QueryBuilder you should NEVER pass user
input to any of the methods of the QueryBuilder and use the placeholder
?
or :name
syntax in combination with
$queryBuilder->setParameter($placeholder, $value)
instead:
<?php
$queryBuilder
->select('id', 'name')
->from('users')
->where('email = ?')
->setParameter(0, $userInputEmail)
;
Note
The numerical parameters in the QueryBuilder API start with the needle
0
.
The \Doctrine\DBAL\Query\QueryBuilder
supports building SELECT
,
INSERT
, UPDATE
, DELETE
and UNION
queries. Which sort of
query you are building depends on the methods you are using.
For SELECT
queries you start with invoking the select()
method
<?php
$queryBuilder
->select('id', 'name')
->from('users');
For INSERT
, UPDATE
and DELETE
queries you can pass the
table name into the insert($tableName)
, update($tableName)
and delete($tableName)
:
<?php
$queryBuilder
->insert('users')
;
$queryBuilder
->update('users')
;
$queryBuilder
->delete('users')
;
For UNION
queries you can pass SQL Part strings or QueryBuilder
instances to union('SELECT 1 as field', $partQueryBuilder)
or
addUnion('SELECT 1 as field', $partQueryBuilder)
:
<?php
$queryBuilder
->union('SELECT 1 AS field', 'SELECT 2 AS field')
->addUnion('SELECT 3 AS field', 'SELECT 3 as field')
;
$subQueryBuilder1
->select('id AS field')
->from('a_table');
$subQueryBuilder2
->select('id AS field')
->from('a_table');
$queryBuilder
->union($subQueryBuilder1)
->addUnionBuilder($subQueryBuilder2)
;
You can convert a query builder to its SQL string representation
by calling $queryBuilder->getSQL()
or casting the object to string.
The SELECT
and UNION`
statement can be specified with a DISTINCT
clause:
<?php
$queryBuilder
->select('name')
->distinct()
->from('users')
;
$queryBuilder
->union('SELECT 1 AS field', 'SELECT 2 AS field')
->distinct()
;
The SELECT
, UPDATE
and DELETE
types of queries allow where
clauses with the following API:
<?php
$queryBuilder
->select('id', 'name')
->from('users')
->where('email = ?')
;
Calling where()
overwrites the previous clause and you can prevent
this by combining expressions with andWhere()
and orWhere()
methods.
You can alternatively use expressions to generate the where clause.
The from()
method takes an optional second parameter with which a table
alias can be specified.
<?php
$queryBuilder
->select('u.id', 'u.name')
->from('users', 'u')
->where('u.email = ?')
;
The SELECT
statement can be specified with GROUP BY
and HAVING
clauses.
Using having()
works exactly like using where()
and there are
corresponding andHaving()
and orHaving()
methods to combine predicates.
For the GROUP BY
you can use the methods groupBy()
which replaces
previous expressions or addGroupBy()
which adds to them:
<?php
$queryBuilder
->select('DATE(last_login) as date', 'COUNT(id) AS users')
->from('users')
->groupBy('DATE(last_login)')
->having('users > 10')
;
For SELECT
clauses you can generate different types of joins: INNER
,
LEFT
and RIGHT
. The RIGHT
join is not portable across all platforms
(Sqlite for example does not support it).
A join always belongs to one part of the from clause. This is why you have to
specify the alias of the FROM
part the join belongs to as the first
argument.
As a second and third argument you can then specify the name and alias of the
join-table and the fourth argument contains the ON
clause.
<?php
$queryBuilder
->select('u.id', 'u.name', 'p.number')
->from('users', 'u')
->innerJoin('u', 'phonenumbers', 'p', 'u.id = p.user_id')
The method signature for join()
, innerJoin()
, leftJoin()
and
rightJoin()
is the same. join()
is a shorthand syntax for
innerJoin()
.
The orderBy($sort, $order = null)
method adds an expression to the ORDER
BY
clause. Be aware that the optional $order
parameter is not safe for
user input and accepts SQL expressions.
<?php
$queryBuilder
->select('id', 'name')
->from('users')
->orderBy('username', 'ASC')
->addOrderBy('last_login', 'ASC NULLS FIRST')
;
Use the addOrderBy
method to add instead of replace the orderBy
clause.
Only a few database vendors have the LIMIT
clause as known from MySQL,
but we support this functionality for all vendors using workarounds.
To use this functionality you have to call the methods setFirstResult($offset)
to set the offset and setMaxResults($limit)
to set the limit of results
returned.
<?php
$queryBuilder
->select('id', 'name')
->from('users')
->setFirstResult(10)
->setMaxResults(20);
For the INSERT
clause setting the values for columns to insert can be
done with the values()
method on the query builder:
<?php
$queryBuilder
->insert('users')
->values(
[
'name' => '?',
'password' => '?',
]
)
->setParameter(0, $username)
->setParameter(1, $password)
;
// INSERT INTO users (name, password) VALUES (?, ?)
Each subsequent call to values()
overwrites any previous set values.
Setting single values instead of all at once is also possible with the
setValue()
method:
<?php
$queryBuilder
->insert('users')
->setValue('name', '?')
->setValue('password', '?')
->setParameter(0, $username)
->setParameter(1, $password)
;
// INSERT INTO users (name, password) VALUES (?, ?)
Of course you can also use both methods in combination:
<?php
$queryBuilder
->insert('users')
->values(
[
'name' => '?',
]
)
->setParameter(0, $username)
;
// INSERT INTO users (name) VALUES (?)
if ($password) {
$queryBuilder
->setValue('password', '?')
->setParameter(1, $password)
;
// INSERT INTO users (name, password) VALUES (?, ?)
}
Not setting any values at all will result in an empty insert statement:
<?php
$queryBuilder
->insert('users')
;
// INSERT INTO users () VALUES ()
For the UPDATE
clause setting columns to new values is necessary
and can be done with the set()
method on the query builder.
Be aware that the second argument allows expressions and is not safe for
user-input:
<?php
$queryBuilder
->update('users', 'u')
->set('u.logins', 'u.logins + 1')
->set('u.last_login', '?')
->setParameter(0, $userInputLastLogin)
;
For more complex WHERE
, HAVING
or other clauses you can use expressions
for building these query parts. You can invoke the expression API, by calling
$queryBuilder->expr()
and then invoking the helper method on it.
Most notably you can use expressions to build nested And-/Or statements:
<?php
$queryBuilder
->select('id', 'name')
->from('users')
->where(
$queryBuilder->expr()->and(
$queryBuilder->expr()->eq('username', '?'),
$queryBuilder->expr()->eq('email', '?')
)
);
The and()
and or()
methods accept an arbitrary amount
of arguments and can be nested in each other.
There is a bunch of methods to create comparisons and other SQL snippets on the Expression object that you can see on the API documentation.
It is often not necessary to know about the exact placeholder names during the building of a query. You can use two helper methods to bind a value to a placeholder and directly use that placeholder in your query as a return value:
<?php
$queryBuilder
->select('id', 'name')
->from('users')
->where('email = ' . $queryBuilder->createNamedParameter($userInputEmail))
;
// SELECT id, name FROM users WHERE email = :dcValue1
$queryBuilder
->select('id', 'name')
->from('users')
->where('email = ' . $queryBuilder->createPositionalParameter($userInputEmail))
;
// SELECT id, name FROM users WHERE email = ?
To use the result cache, it is necessary to call the method
enableResultCache($cacheProfile)
and pass a instance of
Doctrine\DBAL\Cache\QueryCacheProfile
with a cache lifetime
value in seconds. A cache key can optionally be added if needed.
<?php
$queryBuilder
->select('id', 'name')
->from('users')
->enableResultCache(new QueryCacheProfile(300, 'some-key'))
;
Note
See the :ref:`Caching <caching>` section for more information.