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:

.. code-block:: php

<?php

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

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:

.. code-block:: php

<?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``.

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

.. code-block:: php

<?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):

.. code-block:: php

<?php

$queryBuilder
    ->insert('users')
;

$queryBuilder
    ->update('users')
;

$queryBuilder
    ->delete('users')
;

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:

.. code-block:: php

    <?php

    $queryBuilder
        ->select('name')
        ->distinct()
        ->from('users')
    ;

WHERE-Clause
~~~~~~~~~~~~

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

.. code-block:: php

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

Table alias
~~~~~~~~~~~

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

.. code-block:: php

    <?php

    $queryBuilder
        ->select('u.id', 'u.name')
        ->from('users', 'u')
        ->where('u.email = ?')
    ;

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:

.. code-block:: php

<?php
$queryBuilder
    ->select('DATE(last_login) as date', 'COUNT(id) AS users')
    ->from('users')
    ->groupBy('DATE(last_login)')
    ->having('users > 10')
;

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.

.. code-block:: php

    <?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()``.

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.

.. code-block:: php

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

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.

.. code-block:: php

    <?php
    $queryBuilder
        ->select('id', 'name')
        ->from('users')
        ->setFirstResult(10)
        ->setMaxResults(20);

VALUES Clause
~~~~~~~~~~

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

.. code-block:: php

    <?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:

.. code-block:: php

    <?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:

.. code-block:: php

    <?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:

.. code-block:: php

    <?php

    $queryBuilder
        ->insert('users')
    ;
    // INSERT INTO users () VALUES ()

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:

.. code-block:: php

    <?php

    $queryBuilder
        ->update('users u')
        ->set('u.logins', 'u.logins + 1')
        ->set('u.last_login', '?')
        ->setParameter(0, $userInputLastLogin)
    ;

UNION-Clause

To combine multiple SELECT queries into one result-set you can pass SQL Part strings or QueryBuilder instances to one of the following methods:

union(string|QueryBuilder $part)
addUnion(string|QueryBuilder $part, UnionType $type = UnionType::DISTINCT)

.. code-block:: php

<?php

$queryBuilder
    ->union('SELECT 1 AS field')
    ->addUnion('SELECT 2 AS field')
    ->addUnion('SELECT 3 AS field')
    ->addUnion('SELECT 3 as field');

$queryBuilder
    ->union('SELECT 1 AS field')
    ->addUnion('SELECT 2 AS field', UnionType::ALL)
    ->addUnion('SELECT 3 AS field', UnionType::ALL)
    ->addUnion('SELECT 3 as field', UnionType::ALL);

$queryBuilder
    ->union('SELECT 1 AS field')
    ->addUnion('SELECT 2 AS field', UnionType::ALL)
    ->addUnion('SELECT 3 AS field', UnionType::ALL)
    ->addUnion('SELECT 3 as field', UnionType::DISTINCT);

$subQueryBuilder1
    ->select('id AS field')
    ->from('a_table');
$subQueryBuilder2
    ->select('id AS field')
    ->from('a_table');
$queryBuilder
    ->union($subQueryBuilder1)
    ->addUnion($subQueryBuilder2, UnionType::DISTINCT);

$subQueryBuilder1
    ->select('id AS field')
    ->from('a_table');
$subQueryBuilder2
    ->select('id AS field')
    ->from('a_table');
$queryBuilder
    ->union($subQueryBuilder1)
    ->addUnion($subQueryBuilder2,UnionType::ALL)
    ->orderBy('field', 'DESC')
    ->setMaxResults(100);

Common Table Expressions


`SELECT` main query
^^^^^^^^^^^^^^^^^^^

To define Common Table Expressions (CTEs) that can be used in select query.

* ``with(string $name, string|QueryBuilder $queryBuilder, ?array $columns = null)``

.. code-block:: php

    <?php

    $cteQueryBuilder1
        ->select('id')
        ->from('table_a')
        ->where('id = :id');

    $cteQueryBuilder2
        ->select('id')
        ->from('table_b');

    $queryBuilder
        ->with('cte_a', $cteQueryBuilder1)
        ->with('cte_b', $cteQueryBuilder2)
        ->select('id')
        ->from('cte_b', 'b')
        ->join('b', 'cte_a', 'a', 'a.id = b.id')
        ->setParameter('id', 1);

Multiple CTEs can be defined by calling the with method multiple times.

Values of parameters used in a CTE should be defined in the main QueryBuilder.

`UNION` main query part
^^^^^^^^^^^^^^^^^^^^^^^

To define Common Table Expressions (CTEs) that can be used in union query the union
api needs to be used instead of using `select()`:

.. code-block:: php

    <?php

    $qb = $connection->createQueryBuilder();

    $baseQueryBuilder = $qb->sub()
        ->select('id')
        ->from('table_a');

    $unionPart1 = $qb->sub()
        ->select('id', $qb->expr()->literal('first') . ' AS value', '1 AS sort')
        ->from('cte_base')
        ->where($qb->expr()->eq('id', ':id1'));

    $unionPart2 = $qb->sub()
        ->select('id', $qb->expr()->literal('second') . ' AS value', '2 AS sort')
        ->from('cte_base')
        ->where($qb->expr()->eq('id', ':id2'));

    $qb->with('cte_base', $baseQueryBuilder)
        ->union($unionPart1)
        ->addUnion($unionPart2)
        ->orderBy('sort')
        ->setParameter('id1', 2)
        ->setParameter('id2', 1);

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:

.. code-block:: php

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

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:

.. code-block:: php

    <?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 = ?

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.

.. code-block:: php

    <?php

    $queryBuilder
        ->select('id', 'name')
        ->from('users')
        ->enableResultCache(new QueryCacheProfile(300, 'some-key'))
    ;

.. note::

    See the :ref:`Caching <caching>` section for more information.