Doctrine DQL

What is DQL?

Doctrine Query Language is a DSL that looks much like SQL but makes working with Doctrine-based entities easier.

• It lets you use class names and field names instead of table names and column names.
• It uses Doctrine metadata to save you some typing. For example, you don’t have to specify the ON clause of joins since these can be inferred from the metadata.

Selecting using DQL

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

Regular joins vs fetch joins

A DQL SELECT statement can contains two types of joins:

• Regular joins
• Fetch joins

Regular joins

A regular join just limits the result of the query. For example:

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

Here, $users would contain an array of User objects, not any Address objects.

Fetch joins

A fetch join fetches related entities and includes them in the hydrated result of a query.

A regular join becomes a fetch join as soon as you include fields of a joined entity in the SELECT part of your DQL query.

For example:

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

Here, we also select a in the SELECT clause so the regular join becomes a fetch join.

$users will still be an array of User instances but the address of each user is fetched and hydrated into the User#address field. If you access the User#address, Doctrine will already have the data so it doesn’t need to do another read from the database.

Fetch joins are useful when you know upfront that you’ll need to read a lot of associated records so you can load them all upfront instead of making multiple queries for each association.

Sources

• What is DQL? – Doctrine docs