(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: http://www.hibernate.org/.

Learn more about domain-specific languages at:

http://en.wikipedia.org/wiki/Domain-specific_language

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 BlogEntityPost p JOIN p.comments c WHERE p.id=1"

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:

http://docs.doctrine-project.org/en/latest/reference/dql-doctrine-query-language.html#hydration-modes

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:

http://martinfowler.com/eaaCatalog/tableDataGateway.html

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:

http://php.net/manual/en/language.oop5.overloading.php#object.call

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:

   @Entity(repositoryClass="PostRepository")

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('p.id = :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:

http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/query-builder.html

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 BlogEntityPost p LEFT JOIN p.comments c
WHEREp.id = :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 p0_.id AS id0, p0_.title AS title1, p0_.body
AS body2,p0_.publicationDate AS publicationDate3,
c1_.id AS id4, c1_.bodyAS body5, c1_.publicationDate AS publicationDate6,
c1_.post_id ASpost_id7 FROM Post p0_ LEFT JOIN Comment
c1_ ON p0_.id =c1_.post_id WHERE p0_.id
= ? 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:

http://docs.doctrine-project.org/en/latest/reference/caching.html

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('BlogEntityPost')->find($_GET['id']);

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

$post = $entityManager->getRepository('BlogEntityPost')->
findWithComments($_GET['id']);