Building Queries

Packt Publishing

This book is designed for PHP developers and architects who want to modernize their skills through better understanding of Persistence and ORM. You’ll learn through explanations and code samples, all tied to the full development of a web application.

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/.

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:

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*().

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:

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")

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/

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:

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).

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:

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.

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_.bodyAS 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 Commentc1_ 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:

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:

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

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.

Edit the index.php file in web/ to use our new method. Here, you will find the following code:

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:

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

Summary

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.

Alerts & Offers

Series & Level

We understand your time is important. Uniquely amongst the major publishers, we seek to develop and publish the broadest range of learning and information products on each technology. Every Packt product delivers a specific learning pathway, broadly defined by the Series type. This structured approach enables you to select the pathway which best suits your knowledge level, learning style and task objectives.

Learning

As a new user, these step-by-step tutorial guides will give you all the practical skills necessary to become competent and efficient.

Beginner's Guide

Friendly, informal tutorials that provide a practical introduction using examples, activities, and challenges.

Essentials

Fast paced, concentrated introductions showing the quickest way to put the tool to work in the real world.

Cookbook

A collection of practical self-contained recipes that all users of the technology will find useful for building more powerful and reliable systems.

Blueprints

Guides you through the most common types of project you'll encounter, giving you end-to-end guidance on how to build your specific solution quickly and reliably.

Mastering

Take your skills to the next level with advanced tutorials that will give you confidence to master the tool's most powerful features.

Starting

Accessible to readers adopting the topic, these titles get you into the tool or technology so that you can become an effective user.

Progressing

Building on core skills you already have, these titles share solutions and expertise so you become a highly productive power user.