DQL

SELECT entities

DQL looks like SQL but names entities instead of tables. Select the alias and Doctrine hydrates objects for you.

The query below fetches every product. The result holds objects, not plain rows.

$query = $manager->createQuery("SELECT p FROM Product p"); $items = $query->getResult();

Parameters

Never glue values into the query string. Add named params and bind each value with one call.

Binding keeps quotes and types safe. The same query runs for any minimum price.

$query = $manager->createQuery("SELECT p FROM Product p WHERE p.price > :min"); $query->setParameter("min", 100); $items = $query->getResult();

Joins

Joins walk relations by field name, not by key column. Fetch the category with the product in one trip.

One query with a join beats two queries in a loop. The filter below matches on the joined name.

$dql = "SELECT p, c FROM Product p JOIN p.category c WHERE c.name = :name"; $query = $manager->createQuery($dql); $query->setParameter("name", "Books"); $items = $query->getResult();

Paginate DQL

Large answers need slices too. Set the offset and the limit right on the query object.

Page through with first result and max results. The DQL text stays the same on every page.

$query = $manager->createQuery("SELECT p FROM Product p ORDER BY p.id ASC"); $query->setFirstResult(0); $query->setMaxResults(20); $items = $query->getResult();

Next: Transactions

Article author: Arthur Isaev