Doctrine Query Language

DQL SELECT clause

Here is an example that selects all users with an age > 20:

1
<?php
$query = $em->createQuery('SELECT u FROM MyProject\Model\User u WHERE u.age > 20');
$users = $query->getResult();
2
3

Lets examine the query:

• u is a so called identification variable or alias that refers to the MyProject\Model\User class. By placing this alias in the SELECT clause we specify that we want all instances of the User class that are matched by this query to appear in the query result.
• The FROM keyword is always followed by a fully-qualified class name which in turn is followed by an identification variable or alias for that class name. This class designates a root of our query from which we can navigate further via joins (explained later) and path expressions.
• The expression u.age in the WHERE clause is a path expression. Path expressions in DQL are easily identified by the use of the '.' operator that is used for constructing paths. The path expression u.age refers to the age field on the User class.

The result of this query would be a list of User objects where all users are older than 20.

Result format

The composition of the expressions in the SELECT clause also influences the nature of the query result. There are three cases:

All objects

1
SELECT u, p, n FROM Users u...

In this case, the result will be an array of User objects because of the FROM clause, with children p and n hydrated because of their inclusion in the SELECT clause.

All scalars

1
SELECT u.name, u.address FROM Users u...

In this case, the result will be an array of arrays. In the example above, each element of the result array would be an array of the scalar name and address values.

You can select scalars from any entity in the query.

Mixed

1
SELECT u, p.quantity FROM Users u...

Here, the result will again be an array of arrays, with each element being an array made up of a User object and the scalar value p.quantity.

Multiple FROM clauses are allowed, which would cause the result array elements to cycle through the classes included in the multiple FROM clauses.

Joins

A SELECT query can contain joins. There are 2 types of JOINs: "Regular" Joins and "Fetch" Joins.

Regular Joins: Used to limit the results and/or compute aggregate values.

Fetch Joins: In addition to the uses of regular joins: Used to fetch related entities and include them in the hydrated result of a query.

There is no special DQL keyword that distinguishes a regular join from a fetch join. A join (be it an inner or outer join) becomes a "fetch join" as soon as fields of the joined entity appear in the SELECT part of the DQL query outside of an aggregate function. Otherwise its a "regular join".

Example:

Regular join of the address:

1
<?php
$query = $em->createQuery("SELECT u FROM User u JOIN u.address a WHERE a.city = 'Berlin'");
$users = $query->getResult();
2
3

Fetch join of the address:

1
<?php
$query = $em->createQuery("SELECT u, a FROM User u JOIN u.address a WHERE a.city = 'Berlin'");
$users = $query->getResult();
2
3

When Doctrine hydrates a query with fetch-join it returns the class in the FROM clause on the root level of the result array. In the previous example an array of User instances is returned and the address of each user is fetched and hydrated into the User#address variable. If you access the address Doctrine does not need to lazy load the association with another query.

Named and Positional Parameters

DQL supports both named and positional parameters, however in contrast to many SQL dialects positional parameters are specified with numbers, for example "?1", "?2" and so on. Named parameters are specified with ":name1", ":name2" and so on.

When referencing the parameters in Query#setParameter($param, $value) both named and positional parameters are used without their prefixes.

DQL SELECT Examples

This section contains a large set of DQL queries and some explanations of what is happening. The actual result also depends on the hydration mode.

Hydrate all User entities:

1
<?php
$query = $em->createQuery('SELECT u FROM MyProject\Model\User u');
$users = $query->getResult(); // array of User objects
2
3

Retrieve the IDs of all CmsUsers:

1
<?php
$query = $em->createQuery('SELECT u.id FROM CmsUser u');
$ids = $query->getResult(); // array of CmsUser ids
2
3

Retrieve the IDs of all users that have written an article:

1
<?php
$query = $em->createQuery('SELECT DISTINCT u.id FROM CmsArticle a JOIN a.user u');
$ids = $query->getResult(); // array of CmsUser ids
2
3

Retrieve all articles and sort them by the name of the articles users instance:

1
<?php
$query = $em->createQuery('SELECT a FROM CmsArticle a JOIN a.user u ORDER BY u.name ASC');
$articles = $query->getResult(); // array of CmsArticle objects
2
3

Retrieve the Username and Name of a CmsUser:

1
<?php
$query = $em->createQuery('SELECT u.username, u.name FROM CmsUser u');
$users = $query->getResult(); // array of CmsUser username and name values
echo $users[0]['username'];
2
3
4

Retrieve a ForumUser and its single associated entity:

1
<?php
$query = $em->createQuery('SELECT u, a FROM ForumUser u JOIN u.avatar a');
$users = $query->getResult(); // array of ForumUser objects with the avatar association loaded
echo get_class($users[0]->getAvatar());
2
3
4

Retrieve a CmsUser and fetch join all the phonenumbers it has:

1
<?php
$query = $em->createQuery('SELECT u, p FROM CmsUser u JOIN u.phonenumbers p');
$users = $query->getResult(); // array of CmsUser objects with the phonenumbers association loaded
$phonenumbers = $users[0]->getPhonenumbers();
2
3
4

Hydrate a result in Ascending:

1
<?php
$query = $em->createQuery('SELECT u FROM ForumUser u ORDER BY u.id ASC');
$users = $query->getResult(); // array of ForumUser objects
2
3

Or in Descending Order:

1
<?php
$query = $em->createQuery('SELECT u FROM ForumUser u ORDER BY u.id DESC');
$users = $query->getResult(); // array of ForumUser objects
2
3

Using Aggregate Functions:

1
<?php
$query = $em->createQuery('SELECT COUNT(u.id) FROM Entities\User u');
$count = $query->getSingleScalarResult();

$query = $em->createQuery('SELECT u, count(g.id) FROM Entities\User u JOIN u.groups g GROUP BY u.id');
$result = $query->getResult();
2
3
4
5
6

With WHERE Clause and Positional Parameter:

1
<?php
$query = $em->createQuery('SELECT u FROM ForumUser u WHERE u.id = ?1');
$query->setParameter(1, 321);
$users = $query->getResult(); // array of ForumUser objects
2
3
4

With WHERE Clause and Named Parameter:

1
<?php
$query = $em->createQuery('SELECT u FROM ForumUser u WHERE u.username = :name');
$query->setParameter('name', 'Bob');
$users = $query->getResult(); // array of ForumUser objects
2
3
4

With Nested Conditions in WHERE Clause:

1
<?php
$query = $em->createQuery('SELECT u FROM ForumUser u WHERE (u.username = :name OR u.username = :name2) AND u.id = :id');
$query->setParameters([
    'name' => 'Bob',
    'name2' => 'Alice',
    'id' => 321,
]);
$users = $query->getResult(); // array of ForumUser objects
2
3
4
5
6
7
8

With COUNT DISTINCT:

1
<?php
$query = $em->createQuery('SELECT COUNT(DISTINCT u.name) FROM CmsUser');
$users = $query->getResult(); // array of ForumUser objects
2
3

With Arithmetic Expression in WHERE clause:

1
<?php
$query = $em->createQuery('SELECT u FROM CmsUser u WHERE ((u.id + 5000) * u.id + 3) < 10000000');
$users = $query->getResult(); // array of ForumUser objects
2
3

Retrieve user entities with Arithmetic Expression in ORDER clause, using the HIDDEN keyword:

1
<?php
$query = $em->createQuery('SELECT u, u.posts_count + u.likes_count AS HIDDEN score FROM CmsUser u ORDER BY score');
$users = $query->getResult(); // array of User objects
2
3

Using a LEFT JOIN to hydrate all user-ids and optionally associated article-ids:

1
<?php
$query = $em->createQuery('SELECT u.id, a.id as article_id FROM CmsUser u LEFT JOIN u.articles a');
$results = $query->getResult(); // array of user ids and every article_id for each user
2
3

Restricting a JOIN clause by additional conditions specified by WITH:

1
<?php
$query = $em->createQuery("SELECT u FROM CmsUser u LEFT JOIN u.articles a WITH a.topic LIKE :foo");
$query->setParameter('foo', '%foo%');
$users = $query->getResult();
2
3
4

Using several Fetch JOINs:

1
<?php
$query = $em->createQuery('SELECT u, a, p, c FROM CmsUser u JOIN u.articles a JOIN u.phonenumbers p JOIN a.comments c');
$users = $query->getResult();
2
3

BETWEEN in WHERE clause:

1
<?php
$query = $em->createQuery('SELECT u.name FROM CmsUser u WHERE u.id BETWEEN ?1 AND ?2');
$query->setParameter(1, 123);
$query->setParameter(2, 321);
$usernames = $query->getResult();
2
3
4
5

DQL Functions in WHERE clause:

1
<?php
$query = $em->createQuery("SELECT u.name FROM CmsUser u WHERE TRIM(u.name) = 'someone'");
$usernames = $query->getResult();
2
3

IN() Expression:

1
<?php
$query = $em->createQuery('SELECT u.name FROM CmsUser u WHERE u.id IN(46)');
$usernames = $query->getResult();

$query = $em->createQuery('SELECT u FROM CmsUser u WHERE u.id IN (1, 2)');
$users = $query->getResult();

$query = $em->createQuery('SELECT u FROM CmsUser u WHERE u.id NOT IN (1)');
$users = $query->getResult();
2
3
4
5
6
7
8
9

CONCAT() DQL Function:

1
<?php
$query = $em->createQuery("SELECT u.id FROM CmsUser u WHERE CONCAT(u.name, 's') = ?1");
$query->setParameter(1, 'Jess');
$ids = $query->getResult();

$query = $em->createQuery('SELECT CONCAT(u.id, u.name) FROM CmsUser u WHERE u.id = ?1');
$query->setParameter(1, 321);
$idUsernames = $query->getResult();
2
3
4
5
6
7
8

EXISTS in WHERE clause with correlated Subquery

1
<?php
$query = $em->createQuery('SELECT u.id FROM CmsUser u WHERE EXISTS (SELECT p.phonenumber FROM CmsPhonenumber p WHERE p.user = u.id)');
$ids = $query->getResult();
2
3

Get all users who are members of $group.

1
<?php
$query = $em->createQuery('SELECT u.id FROM CmsUser u WHERE :groupId MEMBER OF u.groups');
$query->setParameter('groupId', $group);
$ids = $query->getResult();
2
3
4

Get all users that have more than 1 phonenumber

1
<?php
$query = $em->createQuery('SELECT u FROM CmsUser u WHERE SIZE(u.phonenumbers) > 1');
$users = $query->getResult();
2
3

Get all users that have no phonenumber

1
<?php
$query = $em->createQuery('SELECT u FROM CmsUser u WHERE u.phonenumbers IS EMPTY');
$users = $query->getResult();
2
3

Get all instances of a specific type, for use with inheritance hierarchies:

1
<?php
$query = $em->createQuery('SELECT u FROM Doctrine\Tests\Models\Company\CompanyPerson u WHERE u INSTANCE OF Doctrine\Tests\Models\Company\CompanyEmployee');
$query = $em->createQuery('SELECT u FROM Doctrine\Tests\Models\Company\CompanyPerson u WHERE u INSTANCE OF ?1');
$query = $em->createQuery('SELECT u FROM Doctrine\Tests\Models\Company\CompanyPerson u WHERE u NOT INSTANCE OF ?1');
$query->setParameter(0, $em->getClassMetadata(CompanyEmployee::class));
2
3
4
5

Get all users visible on a given website that have chosen certain gender:

1
<?php
$query = $em->createQuery('SELECT u FROM User u WHERE u.gender IN (SELECT IDENTITY(agl.gender) FROM Site s JOIN s.activeGenderList agl WHERE s.id = ?1)');
2

The IDENTITY() DQL function also works for composite primary keys

1
<?php
$query = $em->createQuery("SELECT IDENTITY(c.location, 'latitude') AS latitude, IDENTITY(c.location, 'longitude') AS longitude FROM Checkpoint c WHERE c.user = ?1");
2

Joins between entities without associations are available, where you can generate an arbitrary join with the following syntax:

1
<?php
$query = $em->createQuery('SELECT u FROM User u JOIN Banlist b WITH u.email = b.email');
2

With an arbitrary join the result differs from the joins using a mapped property. The result of an arbitrary join is an one dimensional array with a mix of the entity from the SELECT and the joined entity fitting to the filtering of the query. In case of the example with User and Banlist, it can look like this:

• User
• Banlist
• Banlist
• User
• Banlist
• User
• Banlist
• Banlist
• Banlist

In this form of join, the Banlist entities found by the filtering in the WITH part are not fetched by an accessor method on User, but are already part of the result. In case the accessor method for Banlists is invoked on a User instance, it loads all the related Banlist objects corresponding to this User. This change of behaviour needs to be considered when the DQL is switched to an arbitrary join.

1
<?php
class CustomerDTO
{
    public function __construct($name, $email, $city, $value = null)
    {
        // Bind values to the object properties.
    }
}
2
3
4
5
6
7
8

1
<?php
$query = $em->createQuery('SELECT NEW CustomerDTO(c.name, e.email, a.city) FROM Customer c JOIN c.email e JOIN c.address a');
$users = $query->getResult(); // array of CustomerDTO
2
3

1
<?php
$query = $em->createQuery('SELECT NEW CustomerDTO(c.name, e.email, a.city, SUM(o.value)) FROM Customer c JOIN c.email e JOIN c.address a JOIN c.orders o GROUP BY c');
$users = $query->getResult(); // array of CustomerDTO
2
3

Using INDEX BY

The INDEX BY construct is nothing that directly translates into SQL but that affects object and array hydration. After each FROM and JOIN clause you specify by which field this class should be indexed in the result. By default a result is incremented by numerical keys starting with 0. However with INDEX BY you can specify any other column to be the key of your result, it really only makes sense with primary or unique fields though:

1
SELECT u.id, u.status, upper(u.name) nameUpper FROM User u INDEX BY u.id
JOIN u.phonenumbers p INDEX BY p.phonenumber
2

Returns an array of the following kind, indexed by both user-id then phonenumber-id:

1
array
  0 =>
    array
      1 =>
        object(stdClass)[299]
          public '__CLASS__' => string 'Doctrine\Tests\Models\CMS\CmsUser' (length=33)
          public 'id' => int 1
          ..
      'nameUpper' => string 'ROMANB' (length=6)
  1 =>
    array
      2 =>
        object(stdClass)[298]
          public '__CLASS__' => string 'Doctrine\Tests\Models\CMS\CmsUser' (length=33)
          public 'id' => int 2
          ...
      'nameUpper' => string 'JWAGE' (length=5)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

You can also index by a to-one association, which will use the id of the associated entity (the join column) as the key in the result set:

1
SELECT p, u FROM Participant INDEX BY p.user JOIN p.user u WHERE p.event = 3

DQL Functions

The following functions are supported in SELECT, WHERE and HAVING clauses:

• IDENTITY(single_association_path_expression [, fieldMapping]) - Retrieve the foreign key column of association of the owning side
• ABS(arithmetic_expression)
• CONCAT(str1, str2)
• CURRENT_DATE() - Return the current date
• CURRENT_TIME() - Returns the current time
• CURRENT_TIMESTAMP() - Returns a timestamp of the current date and time.
• LENGTH(str) - Returns the length of the given string
• LOCATE(needle, haystack [, offset]) - Locate the first occurrence of the substring in the string.
• LOWER(str) - returns the string lowercased.
• MOD(a, b) - Return a MOD b.
• SIZE(collection) - Return the number of elements in the specified collection
• SQRT(q) - Return the square-root of q.
• SUBSTRING(str, start [, length]) - Return substring of given string.
• TRIM([LEADING | TRAILING | BOTH] ['trchar' FROM] str) - Trim the string by the given trim char, defaults to whitespaces.
• UPPER(str) - Return the upper-case of the given string.
• DATE_ADD(date, value, unit) - Add the given time to a given date. (Supported units are SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR)
• DATE_SUB(date, value, unit) - Subtract the given time from a given date. (Supported units are SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR)
• DATE_DIFF(date1, date2) - Calculate the difference in days between date1-date2.

Arithmetic operators

You can do math in DQL using numeric values, for example:

1
SELECT person.salary * 1.5 FROM CompanyPerson person WHERE person.salary < 100000

Aggregate Functions

The following aggregate functions are allowed in SELECT and GROUP BY clauses: AVG, COUNT, MIN, MAX, SUM

Other Expressions

DQL offers a wide-range of additional expressions that are known from SQL, here is a list of all the supported constructs:

• ALL/ANY/SOME - Used in a WHERE clause followed by a sub-select this works like the equivalent constructs in SQL.
• BETWEEN a AND b and NOT BETWEEN a AND b can be used to match ranges of arithmetic values.
• IN (x1, x2, ...) and NOT IN (x1, x2, ..) can be used to match a set of given values.
• LIKE .. and NOT LIKE .. match parts of a string or text using % as a wildcard.
• IS NULL and IS NOT NULL to check for null values
• EXISTS and NOT EXISTS in combination with a sub-select

Adding your own functions to the DQL language

By default DQL comes with functions that are part of a large basis of underlying databases. However you will most likely choose a database platform at the beginning of your project and most likely never change it. For this cases you can easily extend the DQL parser with own specialized platform functions.

You can register custom DQL functions in your ORM Configuration:

1
<?php
$config = new \Doctrine\ORM\Configuration();
$config->addCustomStringFunction($name, $class);
$config->addCustomNumericFunction($name, $class);
$config->addCustomDatetimeFunction($name, $class);

$em = new EntityManager($connection, $config);
2
3
4
5
6
7

The functions have to return either a string, numeric or datetime value depending on the registered function type. As an example we will add a MySQL specific FLOOR() functionality. All the given classes have to implement the base class :

1
<?php
namespace MyProject\Query\AST;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\TokenType;

class MysqlFloor extends FunctionNode
{
    public $simpleArithmeticExpression;

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return 'FLOOR(' . $sqlWalker->walkSimpleArithmeticExpression(
            $this->simpleArithmeticExpression
        ) . ')';
    }

    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(TokenType::T_IDENTIFIER);
        $parser->match(TokenType::T_OPEN_PARENTHESIS);

        $this->simpleArithmeticExpression = $parser->SimpleArithmeticExpression();

        $parser->match(TokenType::T_CLOSE_PARENTHESIS);
    }
}
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

We will register the function by calling and can then use it:

1
<?php
$config = $em->getConfiguration();
$config->registerNumericFunction('FLOOR', 'MyProject\Query\MysqlFloor');

$dql = "SELECT FLOOR(person.salary * 1.75) FROM CompanyPerson person";
2
3
4
5

Single Table

Single Table Inheritance is an inheritance mapping strategy where all classes of a hierarchy are mapped to a single database table. In order to distinguish which row represents which type in the hierarchy a so-called discriminator column is used.

First we need to setup an example set of entities to use. In this scenario it is a generic Person and Employee example:

1
<?php
namespace Entities;

#[Entity]
#[InheritanceType('SINGLE_TABLE')]
#[DiscriminatorColumn(name: 'discr', type: 'string')]
#[DiscriminatorMap(['person' => 'Person', 'employee' => 'Employee'])]
class Person
{
    #[Id, Column(type: 'integer')]
    #[GeneratedValue]
    protected int|null $id = null;

    #[Column(type: 'string', length: 50)]
    protected string $name;

    // ...
}

#[Entity]
class Employee extends Person
{
    #[Column(type: 'string', length: 50)]
    private $department;

    // ...
}
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

First notice that the generated SQL to create the tables for these entities looks like the following:

1
CREATE TABLE Person (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    name VARCHAR(50) NOT NULL,
    discr VARCHAR(255) NOT NULL,
    department VARCHAR(50) NOT NULL
)
2
3
4
5
6

Now when persist a new Employee instance it will set the discriminator value for us automatically:

1
<?php
$employee = new \Entities\Employee();
$employee->setName('test');
$employee->setDepartment('testing');
$em->persist($employee);
$em->flush();
2
3
4
5
6

Now lets run a simple query to retrieve the Employee we just created:

1
SELECT e FROM Entities\Employee e WHERE e.name = 'test'

If we check the generated SQL you will notice it has some special conditions added to ensure that we will only get back Employee entities:

1
SELECT p0_.id AS id0, p0_.name AS name1, p0_.department AS department2,
       p0_.discr AS discr3 FROM Person p0_
WHERE (p0_.name = ?) AND p0_.discr IN ('employee')
2
3

Class Table Inheritance

Class Table Inheritance is an inheritance mapping strategy where each class in a hierarchy is mapped to several tables: its own table and the tables of all parent classes. The table of a child class is linked to the table of a parent class through a foreign key constraint. Doctrine ORM implements this strategy through the use of a discriminator column in the topmost table of the hierarchy because this is the easiest way to achieve polymorphic queries with Class Table Inheritance.

The example for class table inheritance is the same as single table, you just need to change the inheritance type from SINGLE_TABLE to JOINED:

1
<?php

#[Entity]
#[InheritanceType('JOINED')]
#[DiscriminatorColumn(name: 'discr', type: 'string')]
#[DiscriminatorMap(['person' => 'Person', 'employee' => 'Employee'])]
class Person
{
    // ...
}
2
3
4
5
6
7
8
9
10

Now take a look at the SQL which is generated to create the table, you'll notice some differences:

1
CREATE TABLE Person (
    id INT AUTO_INCREMENT NOT NULL,
    name VARCHAR(50) NOT NULL,
    discr VARCHAR(255) NOT NULL,
    PRIMARY KEY(id)
) ENGINE = InnoDB;
CREATE TABLE Employee (
    id INT NOT NULL,
    department VARCHAR(50) NOT NULL,
    PRIMARY KEY(id)
) ENGINE = InnoDB;
ALTER TABLE Employee ADD FOREIGN KEY (id) REFERENCES Person(id) ON DELETE CASCADE
2
3
4
5
6
7
8
9
10
11
12

• The data is split between two tables
• A foreign key exists between the two tables

Now if were to insert the same Employee as we did in the SINGLE_TABLE example and run the same example query it will generate different SQL joining the Person information automatically for you:

1
SELECT p0_.id AS id0, p0_.name AS name1, e1_.department AS department2,
       p0_.discr AS discr3
FROM Employee e1_ INNER JOIN Person p0_ ON e1_.id = p0_.id
WHERE p0_.name = ?
2
3
4

Query Result Formats (Hydration Modes)

The way in which the SQL result set of a DQL SELECT query is transformed to PHP is determined by the so-called "hydration mode".

1
<?php
use Doctrine\ORM\AbstractQuery;

$query = $em->createQuery('SELECT u FROM User u');
$users = $query->getResult();
// same as:
$users = $query->getResult(AbstractQuery::HYDRATE_OBJECT);
2
3
4
5
6
7

1
<?php
$users = $query->getArrayResult();
// same as:
$users = $query->getResult(AbstractQuery::HYDRATE_ARRAY);
2
3
4

1
<?php
$users = $query->getScalarResult();
// same as:
$users = $query->getResult(AbstractQuery::HYDRATE_SCALAR);
2
3
4

1
<?php
$query = $em->createQuery('SELECT COUNT(u.id) FROM User u');
$numUsers = $query->getSingleScalarResult();
// same as:
$numUsers = $query->getResult(AbstractQuery::HYDRATE_SINGLE_SCALAR);
2
3
4
5

1
<?php
$query = $em->createQuery('SELECT a.id FROM User u');
$ids = $query->getSingleColumnResult();
// same as:
$ids = $query->getResult(AbstractQuery::HYDRATE_SCALAR_COLUMN);
2
3
4
5

1
<?php
namespace MyProject\Hydrators;

use Doctrine\ORM\Internal\Hydration\AbstractHydrator;

class CustomHydrator extends AbstractHydrator
{
    protected function _hydrateAll()
    {
        return $this->_stmt->fetchAllAssociative();
    }
}
2
3
4
5
6
7
8
9
10
11
12

1
<?php
$em->getConfiguration()->addCustomHydrationMode('CustomHydrator', 'MyProject\Hydrators\CustomHydrator');
2

1
<?php
$query = $em->createQuery('SELECT u FROM CmsUser u');
$results = $query->getResult('CustomHydrator');
2
3

Pure and Mixed Results

The nature of a result returned by a DQL SELECT query retrieved through Query#getResult() or Query#getArrayResult() can be of 2 forms: pure and mixed. In the previous simple examples, you already saw a "pure" query result, with only objects. By default, the result type is pure but as soon as scalar values, such as aggregate values or other scalar values that do not belong to an entity, appear in the SELECT part of the DQL query, the result becomes mixed. A mixed result has a different structure than a pure result in order to accommodate for the scalar values.

A pure result usually looks like this:

1
$dql = "SELECT u FROM User u";

array
    [0] => Object
    [1] => Object
    [2] => Object
    ...
2
3
4
5
6
7

A mixed result on the other hand has the following general structure:

1
$dql = "SELECT u, 'some scalar string', count(g.id) AS num FROM User u JOIN u.groups g GROUP BY u.id";

array
    [0]
        [0] => Object
        [1] => "some scalar string"
        ['num'] => 42
        // ... more scalar values, either indexed numerically or with a name
    [1]
        [0] => Object
        [1] => "some scalar string"
        ['num'] => 42
        // ... more scalar values, either indexed numerically or with a name
2
3
4
5
6
7
8
9
10
11
12
13

To better understand mixed results, consider the following DQL query:

1
SELECT u, UPPER(u.name) nameUpper FROM MyProject\Model\User u

This query makes use of the UPPER DQL function that returns a scalar value and because there is now a scalar value in the SELECT clause, we get a mixed result.

Conventions for mixed results are as follows:

• The object fetched in the FROM clause is always positioned with the key '0'.
• Every scalar without a name is numbered in the order given in the query, starting with 1.
• Every aliased scalar is given with its alias-name as the key. The case of the name is kept.
• If several objects are fetched from the FROM clause they alternate every row.

Here is how the result could look like:

1
array
    array
        [0] => User (Object)
        ['nameUpper'] => "ROMAN"
    array
        [0] => User (Object)
        ['nameUpper'] => "JONATHAN"
    ...
2
3
4
5
6
7
8

And here is how you would access it in PHP code:

1
<?php
foreach ($results as $row) {
    echo "Name: " . $row[0]->getName();
    echo "Name UPPER: " . $row['nameUpper'];
}
2
3
4
5

Fetching Multiple FROM Entities

If you fetch multiple entities that are listed in the FROM clause then the hydration will return the rows iterating the different top-level entities.

1
$dql = "SELECT u, g FROM User u, Group g";

array
    [0] => Object (User)
    [1] => Object (Group)
    [2] => Object (User)
    [3] => Object (Group)
2
3
4
5
6
7

Iterating Large Result Sets

There are situations when a query you want to execute returns a very large result-set that needs to be processed. All the previously described hydration modes completely load a result-set into memory which might not be feasible with large result sets. See the Batch Processing section on details how to iterate large result sets.

Functions

The following methods exist on the AbstractQuery which both Query and NativeQuery extend from.

1
<?php
$query = $em->createQuery('SELECT u FROM MyProject\Model\User u WHERE u.id = ?1');
$query->setParameter(1, 12);

$query->setResultCacheDriver(new ApcCache());

$query->useResultCache(true)
      ->setResultCacheLifeTime(3600);

$result = $query->getResult(); // cache miss

$query->expireResultCache(true);
$result = $query->getResult(); // forced expire, cache miss

$query->setResultCacheId('my_query_result');
$result = $query->getResult(); // saved in given result cache id.

// or call useResultCache() with all parameters:
$query->useResultCache(true, 3600, 'my_query_result');
$result = $query->getResult(); // cache hit!

// Introspection
$queryCacheProfile = $query->getQueryCacheProfile();
$cacheDriver = $query->getResultCacheDriver();
$lifetime = $query->getLifetime();
$key = $query->getCacheKey();
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

1
<?php
$query = $em->createQuery("SELECT u FROM MyProject\User u");
$query->setFetchMode("MyProject\User", "address", \Doctrine\ORM\Mapping\ClassMetadata::FETCH_EAGER);
$query->execute();
2
3
4

1
SELECT * FROM users;
SELECT * FROM address WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
2

Document syntax:

• non-terminals begin with an upper case character
• terminals begin with a lower case character
• parentheses (...) are used for grouping
• square brackets [...] are used for defining an optional part, e.g. zero or one time
• curly brackets {...} are used for repetition, e.g. zero or more times
• double quotation marks "..." define a terminal string
• a vertical bar | represents an alternative

Terminals

• identifier (name, email, ...) must match [a-z_][a-z0-9_]*
• fully_qualified_name (DoctrineTestsModelsCMSCmsUser) matches PHP's fully qualified class names
• string ('foo', 'bar''s house', '%ninja%', ...)
• char ('/', '\', ' ', ...)
• integer (-1, 0, 1, 34, ...)
• float (-0.23, 0.007, 1.245342E+8, ...)
• boolean (false, true)

Query Language

1
QueryLanguage ::= SelectStatement | UpdateStatement | DeleteStatement

Statements

1
SelectStatement ::= SelectClause FromClause [WhereClause] [GroupByClause] [HavingClause] [OrderByClause]
UpdateStatement ::= UpdateClause [WhereClause]
DeleteStatement ::= DeleteClause [WhereClause]
2
3

Identifiers

1
/* Alias Identification usage (the "u" of "u.name") */
IdentificationVariable ::= identifier

/* Alias Identification declaration (the "u" of "FROM User u") */
AliasIdentificationVariable :: = identifier

/* identifier that must be a class name (the "User" of "FROM User u"), possibly as a fully qualified class name */
AbstractSchemaName ::= fully_qualified_name | identifier

/* Alias ResultVariable declaration (the "total" of "COUNT(*) AS total") */
AliasResultVariable = identifier

/* ResultVariable identifier usage of mapped field aliases (the "total" of "COUNT(*) AS total") */
ResultVariable = identifier

/* identifier that must be a field (the "name" of "u.name") */
/* This is responsible to know if the field exists in Object, no matter if it's a relation or a simple field */
FieldIdentificationVariable ::= identifier

/* identifier that must be a collection-valued association field (to-many) (the "Phonenumbers" of "u.Phonenumbers") */
CollectionValuedAssociationField ::= FieldIdentificationVariable

/* identifier that must be a single-valued association field (to-one) (the "Group" of "u.Group") */
SingleValuedAssociationField ::= FieldIdentificationVariable

/* identifier that must be an embedded class state field */
EmbeddedClassStateField ::= FieldIdentificationVariable

/* identifier that must be a simple state field (name, email, ...) (the "name" of "u.name") */
/* The difference between this and FieldIdentificationVariable is only semantical, because it points to a single field (not mapping to a relation) */
SimpleStateField ::= FieldIdentificationVariable
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

Path Expressions

1
/* "u.Group" or "u.Phonenumbers" declarations */
JoinAssociationPathExpression             ::= IdentificationVariable "." (CollectionValuedAssociationField | SingleValuedAssociationField)

/* "u.Group" or "u.Phonenumbers" usages */
AssociationPathExpression                 ::= CollectionValuedPathExpression | SingleValuedAssociationPathExpression

/* "u.name" or "u.Group" */
SingleValuedPathExpression                ::= StateFieldPathExpression | SingleValuedAssociationPathExpression

/* "u.name" or "u.Group.name" */
StateFieldPathExpression                  ::= IdentificationVariable "." StateField

/* "u.Group" */
SingleValuedAssociationPathExpression     ::= IdentificationVariable "." SingleValuedAssociationField

/* "u.Group.Permissions" */
CollectionValuedPathExpression            ::= IdentificationVariable "." CollectionValuedAssociationField

/* "name" */
StateField                                ::= {EmbeddedClassStateField "."}* SimpleStateField
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Clauses

1
SelectClause        ::= "SELECT" ["DISTINCT"] SelectExpression {"," SelectExpression}*
SimpleSelectClause  ::= "SELECT" ["DISTINCT"] SimpleSelectExpression
UpdateClause        ::= "UPDATE" AbstractSchemaName ["AS"] AliasIdentificationVariable "SET" UpdateItem {"," UpdateItem}*
DeleteClause        ::= "DELETE" ["FROM"] AbstractSchemaName ["AS"] AliasIdentificationVariable
FromClause          ::= "FROM" IdentificationVariableDeclaration {"," IdentificationVariableDeclaration}*
SubselectFromClause ::= "FROM" SubselectIdentificationVariableDeclaration {"," SubselectIdentificationVariableDeclaration}*
WhereClause         ::= "WHERE" ConditionalExpression
HavingClause        ::= "HAVING" ConditionalExpression
GroupByClause       ::= "GROUP" "BY" GroupByItem {"," GroupByItem}*
OrderByClause       ::= "ORDER" "BY" OrderByItem {"," OrderByItem}*
Subselect           ::= SimpleSelectClause SubselectFromClause [WhereClause] [GroupByClause] [HavingClause] [OrderByClause]
2
3
4
5
6
7
8
9
10
11

Items

1
UpdateItem  ::= SingleValuedPathExpression "=" NewValue
OrderByItem ::= (SimpleArithmeticExpression | SingleValuedPathExpression | ScalarExpression | ResultVariable | FunctionDeclaration) ["ASC" | "DESC"]
GroupByItem ::= IdentificationVariable | ResultVariable | SingleValuedPathExpression
NewValue    ::= SimpleArithmeticExpression | "NULL"
2
3
4

From, Join and Index by

1
IdentificationVariableDeclaration          ::= RangeVariableDeclaration [IndexBy] {Join}*
SubselectIdentificationVariableDeclaration ::= IdentificationVariableDeclaration
RangeVariableDeclaration                   ::= AbstractSchemaName ["AS"] AliasIdentificationVariable
JoinAssociationDeclaration                 ::= JoinAssociationPathExpression ["AS"] AliasIdentificationVariable [IndexBy]
Join                                       ::= ["LEFT" ["OUTER"] | "INNER"] "JOIN" (JoinAssociationDeclaration | RangeVariableDeclaration) ["WITH" ConditionalExpression]
IndexBy                                    ::= "INDEX" "BY" SingleValuedPathExpression
2
3
4
5
6

Select Expressions

1
SelectExpression        ::= (IdentificationVariable | ScalarExpression | AggregateExpression | FunctionDeclaration | "(" Subselect ")" | CaseExpression | NewObjectExpression) [["AS"] ["HIDDEN"] AliasResultVariable]
SimpleSelectExpression  ::= (StateFieldPathExpression | IdentificationVariable | FunctionDeclaration | AggregateExpression | "(" Subselect ")" | ScalarExpression) [["AS"] AliasResultVariable]
NewObjectExpression     ::= "NEW" AbstractSchemaName "(" NewObjectArg {"," NewObjectArg}* ")"
NewObjectArg            ::= ScalarExpression | "(" Subselect ")"
2
3
4

Conditional Expressions

1
ConditionalExpression       ::= ConditionalTerm {"OR" ConditionalTerm}*
ConditionalTerm             ::= ConditionalFactor {"AND" ConditionalFactor}*
ConditionalFactor           ::= ["NOT"] ConditionalPrimary
ConditionalPrimary          ::= SimpleConditionalExpression | "(" ConditionalExpression ")"
SimpleConditionalExpression ::= ComparisonExpression | BetweenExpression | LikeExpression |
                                InExpression | NullComparisonExpression | ExistsExpression |
                                EmptyCollectionComparisonExpression | CollectionMemberExpression |
                                InstanceOfExpression
2
3
4
5
6
7
8

Collection Expressions

1
EmptyCollectionComparisonExpression ::= CollectionValuedPathExpression "IS" ["NOT"] "EMPTY"
CollectionMemberExpression          ::= EntityExpression ["NOT"] "MEMBER" ["OF"] CollectionValuedPathExpression
2

Literal Values

1
Literal     ::= string | char | integer | float | boolean
InParameter ::= ArithmeticExpression | InputParameter
2

Input Parameter

1
InputParameter      ::= PositionalParameter | NamedParameter
PositionalParameter ::= "?" integer
NamedParameter      ::= ":" string
2
3

Arithmetic Expressions

1
ArithmeticExpression       ::= SimpleArithmeticExpression | "(" Subselect ")"
SimpleArithmeticExpression ::= ArithmeticTerm {("+" | "-") ArithmeticTerm}*
ArithmeticTerm             ::= ArithmeticFactor {("*" | "/") ArithmeticFactor}*
ArithmeticFactor           ::= [("+" | "-")] ArithmeticPrimary
ArithmeticPrimary          ::= SingleValuedPathExpression | Literal | "(" SimpleArithmeticExpression ")"
                               | FunctionsReturningNumerics | AggregateExpression | FunctionsReturningStrings
                               | FunctionsReturningDatetime | IdentificationVariable | ResultVariable
                               | InputParameter | CaseExpression
2
3
4
5
6
7
8

Scalar and Type Expressions

1
ScalarExpression       ::= SimpleArithmeticExpression | StringPrimary | DateTimePrimary | StateFieldPathExpression | BooleanPrimary | CaseExpression | InstanceOfExpression
StringExpression       ::= StringPrimary | ResultVariable | "(" Subselect ")"
StringPrimary          ::= StateFieldPathExpression | string | InputParameter | FunctionsReturningStrings | AggregateExpression | CaseExpression
BooleanExpression      ::= BooleanPrimary | "(" Subselect ")"
BooleanPrimary         ::= StateFieldPathExpression | boolean | InputParameter
EntityExpression       ::= SingleValuedAssociationPathExpression | SimpleEntityExpression
SimpleEntityExpression ::= IdentificationVariable | InputParameter
DatetimeExpression     ::= DatetimePrimary | "(" Subselect ")"
DatetimePrimary        ::= StateFieldPathExpression | InputParameter | FunctionsReturningDatetime | AggregateExpression
2
3
4
5
6
7
8
9

Aggregate Expressions

1
AggregateExpression ::= ("AVG" | "MAX" | "MIN" | "SUM" | "COUNT") "(" ["DISTINCT"] SimpleArithmeticExpression ")"

Case Expressions

1
CaseExpression        ::= GeneralCaseExpression | SimpleCaseExpression | CoalesceExpression | NullifExpression
GeneralCaseExpression ::= "CASE" WhenClause {WhenClause}* "ELSE" ScalarExpression "END"
WhenClause            ::= "WHEN" ConditionalExpression "THEN" ScalarExpression
SimpleCaseExpression  ::= "CASE" CaseOperand SimpleWhenClause {SimpleWhenClause}* "ELSE" ScalarExpression "END"
CaseOperand           ::= StateFieldPathExpression | TypeDiscriminator
SimpleWhenClause      ::= "WHEN" ScalarExpression "THEN" ScalarExpression
CoalesceExpression    ::= "COALESCE" "(" ScalarExpression {"," ScalarExpression}* ")"
NullifExpression      ::= "NULLIF" "(" ScalarExpression "," ScalarExpression ")"
2
3
4
5
6
7
8

Other Expressions

QUANTIFIED/BETWEEN/COMPARISON/LIKE/NULL/EXISTS

1
QuantifiedExpression     ::= ("ALL" | "ANY" | "SOME") "(" Subselect ")"
BetweenExpression        ::= ArithmeticExpression ["NOT"] "BETWEEN" ArithmeticExpression "AND" ArithmeticExpression
ComparisonExpression     ::= ArithmeticExpression ComparisonOperator ( QuantifiedExpression | ArithmeticExpression )
InExpression             ::= ArithmeticExpression ["NOT"] "IN" "(" (InParameter {"," InParameter}* | Subselect) ")"
InstanceOfExpression     ::= IdentificationVariable ["NOT"] "INSTANCE" ["OF"] (InstanceOfParameter | "(" InstanceOfParameter {"," InstanceOfParameter}* ")")
InstanceOfParameter      ::= AbstractSchemaName | InputParameter
LikeExpression           ::= StringExpression ["NOT"] "LIKE" StringPrimary ["ESCAPE" char]
NullComparisonExpression ::= (InputParameter | NullIfExpression | CoalesceExpression | AggregateExpression | FunctionDeclaration | IdentificationVariable | SingleValuedPathExpression | ResultVariable) "IS" ["NOT"] "NULL"
ExistsExpression         ::= ["NOT"] "EXISTS" "(" Subselect ")"
ComparisonOperator       ::= "=" | "<" | "<=" | "<>" | ">" | ">=" | "!="
2
3
4
5
6
7
8
9
10

Functions

1
FunctionDeclaration ::= FunctionsReturningStrings | FunctionsReturningNumerics | FunctionsReturningDateTime

FunctionsReturningNumerics ::=
        "LENGTH" "(" StringPrimary ")" |
        "LOCATE" "(" StringPrimary "," StringPrimary ["," SimpleArithmeticExpression]")" |
        "ABS" "(" SimpleArithmeticExpression ")" |
        "SQRT" "(" SimpleArithmeticExpression ")" |
        "MOD" "(" SimpleArithmeticExpression "," SimpleArithmeticExpression ")" |
        "SIZE" "(" CollectionValuedPathExpression ")" |
        "DATE_DIFF" "(" ArithmeticPrimary "," ArithmeticPrimary ")" |
        "BIT_AND" "(" ArithmeticPrimary "," ArithmeticPrimary ")" |
        "BIT_OR" "(" ArithmeticPrimary "," ArithmeticPrimary ")"

FunctionsReturningDateTime ::=
        "CURRENT_DATE" |
        "CURRENT_TIME" |
        "CURRENT_TIMESTAMP" |
        "DATE_ADD" "(" ArithmeticPrimary "," ArithmeticPrimary "," StringPrimary ")" |
        "DATE_SUB" "(" ArithmeticPrimary "," ArithmeticPrimary "," StringPrimary ")"

FunctionsReturningStrings ::=
        "CONCAT" "(" StringPrimary "," StringPrimary ")" |
        "SUBSTRING" "(" StringPrimary "," SimpleArithmeticExpression "," SimpleArithmeticExpression ")" |
        "TRIM" "(" [["LEADING" | "TRAILING" | "BOTH"] [char] "FROM"] StringPrimary ")" |
        "LOWER" "(" StringPrimary ")" |
        "UPPER" "(" StringPrimary ")" |
        "IDENTITY" "(" SingleValuedAssociationPathExpression {"," string} ")"
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27