SQL Query Builder

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:

1 <?php

$conn = DriverManager::getConnection([/*..*/]);
$queryBuilder = $conn->createQueryBuilder();
2
3
4

Security: Safely preventing SQL Injection

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:

1 <?php

$queryBuilder
    ->select('id', 'name')
    ->from('users')
    ->where('email = ?')
    ->setParameter(0, $userInputEmail)
;
2
3
4
5
6
7
8

The numerical parameters in the QueryBuilder API start with the needle 0.

Building a Query

The \Doctrine\DBAL\Query\QueryBuilder supports building SELECT, INSERT, UPDATE and DELETE 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

1 <?php

$queryBuilder
    ->select('id', 'name')
    ->from('users');
2
3
4
5

For INSERT, UPDATE and DELETE queries you can pass the table name into the insert($tableName), update($tableName) and delete($tableName):

1 <?php

$queryBuilder
    ->insert('users')
;

$queryBuilder
    ->update('users')
;

$queryBuilder
    ->delete('users')
;
2
3
4
5
6
7
8
9
10
11
12
13

You can convert a query builder to its SQL string representation by calling $queryBuilder->getSQL() or casting the object to string.

DISTINCT-Clause

The SELECT statement can be specified with a DISTINCT clause:

1 <?php

$queryBuilder
    ->select('name')
    ->distinct()
    ->from('users')
;
2
3
4
5
6
7

WHERE-Clause

The SELECT, UPDATE and DELETE types of queries allow where clauses with the following API:

1 <?php

$queryBuilder
    ->select('id', 'name')
    ->from('users')
    ->where('email = ?')
;
2
3
4
5
6
7

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.

Table alias

The from() method takes an optional second parameter with which a table alias can be specified.

1 <?php

$queryBuilder
    ->select('u.id', 'u.name')
    ->from('users', 'u')
    ->where('u.email = ?')
;
2
3
4
5
6
7

GROUP BY and HAVING Clause

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:

1 <?php
$queryBuilder
    ->select('DATE(last_login) as date', 'COUNT(id) AS users')
    ->from('users')
    ->groupBy('DATE(last_login)')
    ->having('users > 10')
;
2
3
4
5
6
7

Join Clauses

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.

1 <?php
$queryBuilder
    ->select('u.id', 'u.name', 'p.number')
    ->from('users', 'u')
    ->innerJoin('u', 'phonenumbers', 'p', 'u.id = p.user_id')
2
3
4
5

The method signature for join(), innerJoin(), leftJoin() and rightJoin() is the same. join() is a shorthand syntax for innerJoin().

Order-By Clause

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.

1 <?php
$queryBuilder
    ->select('id', 'name')
    ->from('users')
    ->orderBy('username', 'ASC')
    ->addOrderBy('last_login', 'ASC NULLS FIRST')
;
2
3
4
5
6
7

Use the addOrderBy method to add instead of replace the orderBy clause.

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

1 <?php
$queryBuilder
    ->select('id', 'name')
    ->from('users')
    ->setFirstResult(10)
    ->setMaxResults(20);
2
3
4
5
6

VALUES Clause

For the INSERT clause setting the values for columns to insert can be done with the values() method on the query builder:

1 <?php

$queryBuilder
    ->insert('users')
    ->values(
        [
            'name' => '?',
            'password' => '?',
        ]
    )
    ->setParameter(0, $username)
    ->setParameter(1, $password)
;
// INSERT INTO users (name, password) VALUES (?, ?)
2
3
4
5
6
7
8
9
10
11
12
13
14

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:

1 <?php

$queryBuilder
    ->insert('users')
    ->setValue('name', '?')
    ->setValue('password', '?')
    ->setParameter(0, $username)
    ->setParameter(1, $password)
;
// INSERT INTO users (name, password) VALUES (?, ?)
2
3
4
5
6
7
8
9
10

Of course you can also use both methods in combination:

1 <?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 (?, ?)
}
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Not setting any values at all will result in an empty insert statement:

1 <?php

$queryBuilder
    ->insert('users')
;
// INSERT INTO users () VALUES ()
2
3
4
5
6

Set Clause

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:

1 <?php

$queryBuilder
    ->update('users', 'u')
    ->set('u.logins', 'u.logins + 1')
    ->set('u.last_login', '?')
    ->setParameter(0, $userInputLastLogin)
;
2
3
4
5
6
7
8

Building Expressions

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:

1 <?php

$queryBuilder
    ->select('id', 'name')
    ->from('users')
    ->where(
        $queryBuilder->expr()->and(
            $queryBuilder->expr()->eq('username', '?'),
            $queryBuilder->expr()->eq('email', '?')
        )
    );
2
3
4
5
6
7
8
9
10
11

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.

Binding Parameters to Placeholders

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:

1 <?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 = ?
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Caching

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.

1 <?php

$queryBuilder
    ->select('id', 'name')
    ->from('users')
    ->enableResultCache(new QueryCacheProfile(300, 'some-key'))
;
2
3
4
5
6
7

See the Caching section for more information.

1	<?php $conn = DriverManager::getConnection([/../]); $queryBuilder = $conn->createQueryBuilder();
2
3
4

1	<?php $queryBuilder ->select('id', 'name') ->from('users') ->where('email = ?') ->setParameter(0, $userInputEmail) ;
2
3
4
5
6
7
8

1	<?php $queryBuilder ->insert('users') ; $queryBuilder ->update('users') ; $queryBuilder ->delete('users') ;
2
3
4
5
6
7
8
9
10
11
12
13

1	<?php $queryBuilder ->select('name') ->distinct() ->from('users') ;
2
3
4
5
6
7

1	<?php $queryBuilder ->select('u.id', 'u.name') ->from('users', 'u') ->where('u.email = ?') ;
2
3
4
5
6
7

1	<?php $queryBuilder ->select('DATE(last_login) as date', 'COUNT(id) AS users') ->from('users') ->groupBy('DATE(last_login)') ->having('users > 10') ;
2
3
4
5
6
7

1	<?php $queryBuilder ->insert('users') ->values( [ 'name' => '?', 'password' => '?', ] ) ->setParameter(0, $username) ->setParameter(1, $password) ; // INSERT INTO users (name, password) VALUES (?, ?)
2
3
4
5
6
7
8
9
10
11
12
13
14

1	<?php $queryBuilder ->update('users', 'u') ->set('u.logins', 'u.logins + 1') ->set('u.last_login', '?') ->setParameter(0, $userInputLastLogin) ;
2
3
4
5
6
7
8

Maintained

Unmaintained