Building Queries

(For more resources related to this topic, see here.)

Understanding DQL

DQL is the acronym of Doctrine Query Language. It's a domain-specific language that is very similar to SQL, but is not SQL. Instead of querying the database tables and rows, DQL is designed to query the object model's entities and mapped properties.

DQL is inspired by and similar to HQL, the query language of Hibernate, a popular ORM for Java. For more details you can visit this website:

Learn more about domain-specific languages at:

To better understand what it means, let's run our first DQL query.

Doctrine command-line tools are as genuine as a Swiss Army knife. They include a command called orm:run-dql that runs the DQL query and displays it's result. Use it to retrieve title and all the comments of the post with 1 as an identifier:

php vendor/bin/doctrine.php orm:run-dql "SELECT p.title,
c.bodyFROM Blog\Entity\Post p JOIN p.comments c WHERE"

It looks like a SQL query, but it's definitely not a SQL query. Examine the FROM and the JOIN clauses; they contain the following aspects:

  • A fully qualified entity class name is used in the FROM clause as the root of the query
  • All the Comment entities associated with the selected Post entities are joined, thanks to the presence of the comments property of the Post entity class in the JOIN clause

As you can see, data from the entities associated with the main entity can be requested in an object-oriented way. Properties holding the associations (on the owning or the inverse side) can be used in the JOIN clause.

Despite some limitations (especially in the field of subqueries), DQL is a powerful and flexible language to retrieve object graphs. Internally, Doctrine parses the DQL queries, generates and executes them through Database Abstraction Layer (DBAL) corresponding to the SQL queries, and hydrates the data structures with results.

Until now, we only used Doctrine to retrieve the PHP objects. Doctrine is able to hydrate other types of data structures, especially arrays and basic types. It's also possible to write custom hydrators to populate any data structure.

If you look closely at the return of the previous call of orm:run-dql, you'll see that it's an array, and not an object graph, that has been hydrated.

As with all the topics covered in this book, more information about built-in hydration modes and custom hydrators is available in the Doctrine documentation on the following website:

Using the entity repositories

Entity repositories are classes responsible for accessing and managing entities. Just like entities are related to the database rows, entity repositories are related to the database tables.

All the DQL queries should be written in the entity repository related to the entity type they retrieve. It hides the ORM from other components of the application and makes it easier to re-use, refactor, and optimize the queries.

Doctrine entity repositories are an implementation of the Table Data Gateway design pattern. For more details, visit the following website:

A base repository, available for every entity, provides useful methods for managing the entities in the following manner:

  • find($id): It returns the entity with $id as an identifier or null

    It is used internally by the find() method of the Entity Managers.

  • findAll(): It retrieves an array that contains all the entities in this repository
  • findBy(['property1' => 'value', 'property2' => 1], ['property3' => 'DESC', 'property4' => 'ASC']): It retrieves an array that contains entities matching all the criteria passed in the first parameter and ordered by the second parameter
  • findOneBy(['property1' => 'value', 'property2' => 1]): It is similar to findBy() but retrieves only the first entity or null if none of the entities match the criteria

Entity repositories also provide shortcut methods that allow a single property to filter entities. They follow this pattern: findBy*() and findOneBy*().

For instance, calling findByTitle('My title') is equivalent to calling findBy(['title' => 'My title']).

This feature uses the magical __call() PHP method. For more details visit the following website:

In our blog app, we want to display comments in the detailed post view, but it is not necessary to fetch them from the list of posts. Eager loading through the fetch attribute is not a good choice for the list, and Lazy loading slows down the detailed view.

A solution to this would be to create a custom repository with extra methods for executing our own queries. We will write a custom method that collates comments in the detailed view.

Creating custom entity repositories

Custom entity repositories are classes extending the base entity repository class provided by Doctrine. They are designed to receive custom methods that run the DQL queries.

As usual, we will use the mapping information to tell Doctrine to use a custom repository class. This is the role of the repositoryClass attribute of the @Entity annotation.

Kindly perform the following steps to create a custom entity repository:

  1. Reopen the Post.php file at the src/Blog/Entity/ location and add a repositoryClass attribute to the existing @Entity annotation like the following line of code:


  2. Doctrine command-line tools also provide an entity repository generator. Type the following command to use it:

    php vendor/bin/doctrine.php orm:generate:repositories src/

  3. Open this new empty custom repository, which we just generated in the PostRepository.phpPostRepository.php file, at the src/Blog/Entity/ location. Add the following method for retrieving the posts and comments:

    /** * Finds a post with its comments * * @param int $id * @return Post */ public function findWithComments($id) { return $this ->createQueryBuilder('p') ->addSelect('c') ->leftJoin('p.comments', 'c') ->where(' = :id') ->orderBy('c.publicationDate', 'ASC') ->setParameter('id', $id) ->getQuery() ->getOneOrNullResult() ; }

Our custom repository extends the default entity repository provided by Doctrine. The standard methods, described earlier in the article, are still available.

Getting started with Query Builder

QueryBuilder is an object designed to help build the DQL queries through a PHP API with a fluent interface. It allows us to retrieve the generated DQL queries through the getDql() method (useful for debugging) or directly use the Query object (provided by Doctrine).

To increase performance, QueryBuilder caches the generated DQL queries and manages an internal state.

The full API and states of the DQL query are documented on the following website:

We will give an in-depth explanation of the findWithComments() method that we created in the PostRepository class.

Firstly, a QueryBuilder instance is created with the createQueryBuilder() method inherited from the base entity repository. The QueryBuilder instance takes a string as a parameter. This string will be used as an alias of the main entity class. By default, all the fields of the main entity class are selected and no other clauses except SELECT and FROM are populated.

The leftJoin() call creates a JOIN clause that retrieves comments associated with the posts. Its first argument is the property to join and its second is the alias; these will be used in the query for the joined entity class (here, the letter c will be used as an alias for the Comment class).

Unless the SQL JOIN clause is used, the DQL query automatically fetches the entities associated with the main entity. There is no need for keywords like ON or USING. Doctrine automatically knows whether a join table or a foreign-key column must be used.

The addSelect() call appends comment data to the SELECT clause. The alias of the entity class is used to retrieve all the fields (this is similar to the * operator in SQL). As in the first DQL query of this article, specific fields can be retrieved with the notation alias.propertyName.

You guessed it, the call to the where() method sets the WHERE part of the query.

Under the hood, Doctrine uses prepared SQL statements. They are more efficient than the standard SQL queries.

The id parameter will be populated by the value set by the call to setParameter().

Thanks again to prepared statements and this setParameter() method, SQL Injection attacks are automatically avoided.

SQL Injection Attacks are a way to execute malicious SQL queries using user inputs that have not escaped. Let's take the following example of a bad DQL query to check if a user has a specific role:

$query = $entityManager->createQuery('SELECT ur FROMUserRole ur
WHERE ur.username = "' . $username . '" ANDur.role = "' . $role . '"');
$hasRole = count($query->getResult());

This DQL query will be translated into SQL by Doctrine. If someone types the following username:

" OR "a"="a

the SQL code contained in the string will be injected and the query will always return some results. The attacker has now gained access to a private area.

The proper way should be to use the following code:

$query = $entityManager->createQuery("SELECT ur FROMUserRole WHERE
username = :username and role = :role"); $query->setParameters([ 'username' => $username, 'role' => $role ]); $hasRole = count($query->getResult());

Thanks to prepared statements, special characters (like quotes) contained in the username are not dangerous, and this snippet will work as expected.

The orderBy() call generates an ORDER BY clause that orders results as per the publication date of the comments, older first.

Most SQL instructions also have an object-oriented equivalent in DQL. The most common join types can be made using DQL; they generally have the same name.

The getQuery() call tells the Query Builder to generate the DQL query (if needed, it will get the query from its cache if possible), to instantiate a Doctrine Query object, and to populate it with the generated DQL query.

This generated DQL query will be as follows:

SELECT p, c FROM Blog\Entity\Post p LEFT JOIN p.comments c = :id ORDER BY c.publicationDate ASC

The Query object exposes another useful method for the purpose of debugging: getSql(). As its name implies, getSql() returns the SQL query corresponding to the DQL query, which Doctrine will run on DBMS. For our DQL query, the underlying SQL query is as follows:

SELECT AS id0, p0_.title AS title1, p0_.body
AS body2,p0_.publicationDate AS publicationDate3, AS id4, c1_.bodyAS body5, c1_.publicationDate AS publicationDate6,
c1_.post_id ASpost_id7 FROM Post p0_ LEFT JOIN Comment
c1_ ON =c1_.post_id WHERE
= ? ORDER BY c1_.publicationDate ASC

The getOneOrNullResult() method executes it, retrieves the first result, and returns it as a Post entity instance (this method returns null if no result is found).

Like the QueryBuilder object, the Query object manages an internal state to generate the underlying SQL query only when necessary.

Performance is something to be very careful about while using Doctrine. When set in production mode, ORM is able to cache the generated queries (DQL through the QueryBuilder objects, SQL through the Query objects) and results of the queries.

ORM must be configured to use one of the blazing, fast, supported systems (APC, Memcache, XCache, or Redis) as shown on the following website:

We still need to update the view layer to take care of our new findWithComments() method.

Open the view-post.php file at the web/location, where you will find the following code snippet:

$post = $entityManager->getRepository('Blog\Entity\Post')->find($_GET['id']);

Replace the preceding line of code with the following code snippet:

$post = $entityManager->getRepository('Blog\Entity\Post')->

Filtering by tag

To discover a more advanced use of the QueryBuilder and DQL, we will create a list of posts having one or more tags.

Tag filtering is good for Search Engine Optimization and allows the readers to easily find the content they are interested in. We will build a system that is able to list posts that have several tags in common; for example, all the posts tagged with Doctrine and Symfony.

To filter our posts using tags kindly perform the following steps:

  1. Add another method to our custom PostRepository class (src/Blog/Entity/PostRepository.php) using the following code:

    /** * Finds posts having tags * * @param string[] $tagNames * @return Post[] */ public function findHavingTags(array $tagNames) { return $queryBuilder = $this ->createQueryBuilder('p') ->addSelect('t') ->join('p.tags', 't') ->where(' IN (:tagNames)') ->groupBy('') ->having('COUNT( >= :numberOfTags') ->setParameter('tagNames', $tagNames) ->setParameter('numberOfTags',count($tagNames)) ->getQuery() ->getResult() ; }

    This method is a bit more complex. It takes in a parameter as an array of tag names and returns an array of posts that has all these tags.

    The query deserves some explanation, which is as follows:

    • The main entity class (automatically set by the inherited createQueryBuilder() method) is Post and its alias is the letter p.
    • We join the associated tags through a JOIN clause; the Tag class is aliased by t.
    • Thanks to where() being called, we retrieve only the posts tagged by one of the tags passed in the parameter. We use an awesome feature of Doctrine that allows us to directly use an array as a query parameter.
    • Results of where() are grouped by id with the call to groupBy().
    • We use the aggregate function COUNT() in the HAVING clause to filter the posts that are tagged by some tags of the $tagNames array, but not all of them.
  2. Edit the index.php file in web/ to use our new method. Here, you will find the following code:

    /** @var $posts \Blog\Entity\Post[] Retrieve the list ofall blog posts */ $posts = $entityManager->getRepository('Blog\Entity\Post')->findAll();

    And replace the preceding code with the next code snippet:

    $repository = $entityManager->getRepository('Blog\Entity\Post'); /** @var $posts \Blog\Entity\Post[] Retrieve the list ofall blog posts */ $posts = isset($_GET['tags']) ? $repository->
    findHavingTags($_GET['tags']) : $repository->findAll();

    Now, when a GET parameter called tags exists in the URL, it is used to filter posts. Better, if several comma-separated tags are passed in, only posts with all these tags will be displayed.

  3. Type http://localhost:8000/index.php?tags=tag4,tag5 in your favorite browser. Thanks to the fixtures we have created, posts 5 and 10 should be listed.
  4. In the same file, find the following code:

    <p> <?=nl2br(htmlspecialchars($post->getBody()))?> </p>

    And add the list of tags as follows:

    <ul> <?php foreach ($post->getTags() as $tag): ?> <li> <a href="index.php?tags=<?=urlencode($tag)?>"><
    ?=htmlspecialchars($tag)?></a> </li> <?php endforeach ?> </ul>

A smart list of tags with links to the tag page is displayed. You can copy this code and then paste it in the view-post.php file in the web/ location; or better, don't repeat yourself: create a small helper function to display the tags.

Counting comments

We still need to make some cosmetic changes. Posts with a lot of comments interest many readers. It would be better if the number of comments for each post was available directly from the list page. Doctrine can populate an array containing the result of the call to an aggregate function as the first row and hydrated entities as the second.

Add the following method, for retrieving posts with the associated comments, to the PostRepository class:

/** * Finds posts with comment count * * @return array */ public function findWithCommentCount() { return $this ->createQueryBuilder('p') ->leftJoin('p.comments', 'c') ->addSelect('COUNT(') ->groupBy('') ->getQuery() ->getResult() ; }

Thanks to the GROUP BY clause and the call to addSelect(), this method will return a two-dimensional array instead of an array of the Post entities. Arrays in the returned array contain two values, which are as follows:

  • Our Post entity at the first index
  • The result of the COUNT() function of DQL (the number of comments) at the second index

In the index.php file at the web/ location, find the following code:

$posts = $repository->findHavingTags(explode(',',$_GET['tags'])); } else { $posts = $repository->findAll(); }

And replace the preceding code with the following code to use our new method:

$results = $repository->findHavingTags(explode(',',$_GET['tags'])); } else { $results = $repository->findWithCommentCount(); }

To match the new structure returned by findWithCommentCount(), find the following code:

<?php foreach ($posts as $post): ?>

And replace the preceding code with the next code snippet:

<?php foreach ($results as $result): $post = $result[0]; $commentCount = $result[1]; ?>

As seen previously, the use of a custom hydrator is a better practice while handling such cases.

You should also take a look at Custom AST Walker as shown on the following website:

Find the following code snippet:

<?php if (empty($posts)): ?>

And replace the preceding code with the next code snippet:

<?php if (empty($results)): ?>

It's time to display the number of comments. Insert the following code after the tag list:

<?php if ($commentCount == 0): ?> Be the first to comment this post. <?php elseif ($commentCount == 1): ?> One comment <?php else: ?> <?= $commentCount ?> comments <?php endif ?>

As the index.php file at the web/location also uses the findHavingTags() method to display the list of tagged articles, we need to update this method too. This is done using the following code:

// … ->addSelect('t') ->addSelect('COUNT(') ->leftJoin('p.comments', 'c') // …


In this article, we have learned about DQL, its differences from SQL, and its Query Builder. We also learned about the concept of entity repositories and how to create custom ones.

Even if there is a lot more to learn from these topics and from Doctrine in general, our knowledge should be sufficient to start developing complete and complex applications using Doctrine as a persistent system.

Resources for Article:

Further resources on this subject:

You've been reading an excerpt of:

Persistence in PHP with Doctrine ORM

Explore Title