DQL Query Results & Hydration Modes

This page explains and discusses the different DQL Query result types and hydration modes in Doctrine 2.0.
Note: This is not a final document and is subject to frequent changes.

Hydration Modes

"Identity" hydration modes vs. other hydration modes: Identity hydration modes operate on the identity of Entities, hence it is mandatory to include all ID fields of Entities participating in the query, either in the FROM part or in a fetch join, in the SELECT part of the query. If they're not present, Doctrine will auto-add them.

HYDRATE_IDENTITY_OBJECT
Hydrates an object graph. This is the default hydration mode.

HYDRATE_IDENTITY_ARRAY
Hydrates an array graph that is largely interchangeable with the object graph generated by HYDRATE_IDENTITY_OBJECT for read-only purposes.
Note: This is not 100% sure. An array graph can differ from the corresponding object graph in certain scenarios due to the difference of the identity semantics between arrays and objects.

HYDRATE_SCALAR
Hydrates a flat/rectangular result set of scalar values that can contain duplicate data. No identity semantics apply.

HYDRATE_SINGLE_SCALAR
Hydrates a single scalar value from the result returned by the dbms. No identity semantics apply.

HYDRATE_NONE
Hydrates nothing. Returns the result set as it is returned by the underlying dbms.

There are a few special cases where the hydration mode will be fixed to a specific type:
1) Queries that contain only a single aggregate value, or other scalar value, in the SELECT part of the query always hydrate with HYDRATE_SINGLE_SCALAR.
2) Queries that contain only aggregate values, or other scalar values, in the SELECT part of the query always hydrate with HYDRATE_SCALAR.

Types of DQL JOINs

There are 2 types of JOINs: Regular Joins & 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.

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 query outside of an aggregate function. Otherwise its a "regular join".

Example:

Regular join of the address: select u.* from User u join u.address a where a.city = 'Berlin'
Fetch join of the address: select u.*, a.* from User u join u.address a where a.city = 'Berlin'

Query Result Types

The identity hydration modes can return 2 different result types: Pure and mixed. By default, the result type is pure but as soon as aggregate values, or other scalar values that do not belong to an Entity, appear in the SELECT part of the query, the result becomes mixed. The following are a few examples that demonstrate pure and mixed results with object identity hydration (array identity hydration is very similar, just replace the objects with arrays in the shown results).

// mixed result (HYDRATE_IDENTITY_OBJECT)
select u.id, u.status, upper(u.name) from USERS u
 => array(
        0 => array(
            0 => User(object),
            1 => 'ROMAN'
            ),
        1 => array(
            0 => User(object),
            1 => 'JONATHAN'
            ),
        ...
    )

// pure result (HYDRATE_IDENTITY_OBJECT)
select u.id, u.status from USERS u
 => Collection(
        0 => User(object),
        1 => User(object),
        ...
    )

// mixed result (HYDRATE_IDENTITY_OBJECT)
select u.id, u.name, count(p.phonenumber) numPhonenumbers, upper(u.name) nameUpper from User u join u.phonenumbers p group by u.id, u.name
 => array(
    0 => array(
        0 => User(object),
        'numPhonenumbers' => 2,
        'nameUpper' => 'ROMAN'
        ),
    1 => array(
        0 => User(object),
        'numPhonenumbers' => 4,
        'nameUpper' => 'JONATHAN'
        ),
    ...
    )

// pure result (HYDRATE_IDENTITY_OBJECT)
select u.id, u.name, p.phonenumber from User u join u.phonenumbers p
 => Collection(
        0 => User(object) {
                 phonenumbers = Collection(
                     0 => Phonenumber,
                     1 => Phonenumber,
                     ...
                 ) 
             },
        1 => User(object) {
                 phonenumbers = Collection(
                     0 => Phonenumber,
                     1 => Phonenumber,
                     ...
                 ) 
             },
        ...
    )


// mixed result (HYDRATE_IDENTITY_OBJECT)
select u.id, u.name, upper(u.name) nameUpper, p.phonenumber from User u join u.phonenumbers p
 => array(
        0 => array(
            0 => User(object) {
                     phonenumbers = Collection(
                         0 => Phonenumber,
                         1 => Phonenumber,
                         ...
                     ) 
                 },
            'nameUpper' => 'ROMAN'
        ),
        1 => array(
            0 => User(object) {
                     phonenumbers = Collection(
                         0 => Phonenumber,
                         1 => Phonenumber,
                         ...
                     ) 
                 },
            'nameUpper' => 'JONATHAN'
        ),...
    )

As can be seen: In a mixed result, the root entities of the query result appear always at the index 0 of a result row, as long as no custom indexing is used (INDEXBY). Here is a loop over a mixed result to help visualize the structure:

// select u.id, u.name, upper(u.name) nameUpper, p.phonenumber from User u join u.phonenumbers p
foreach ($mixedResult as $row) {
    $user = $row[0]; // User object
    foreach ($user->phonenumbers as $pnumber) { // phonenumbers is a Collection
        echo $pnumber->phonenumber;
    }
    echo $row['nameUpper']; // the scalar value
}

The special scalar() DQL function

By default, if you specify something like "u.name" in the SELECT, outside of any aggregate function or another function, Doctrine assumes you want the "name" field hydrated into the object. In order to tell Doctrine that you just want the scalar value, you can use the scalar() function like this:

select scalar(u.name) from User u where u.id = 1

This will automatically result in HYDRATE_SINGLE_SCALAR and you'll just get the name of the user with id = 1. You can also use this function in a more complex query, in this case these scalar values simply get aligned along with the other scalar values in the query:

select u.id, u.name, scalar(u.name) from User u
=>
array(
    array(
        0 => User(object), // has id and name populated
        'name' => 'Roman'
    ),
    array(
        0 => User(object),
        'name' => 'Jonathan'
    )
)