cult3

Using Doctrine Query Language

Jul 28, 2014

Table of contents:

  1. What is Doctrine Query Language?
  2. What are the benefits of using DQL over SQL?
  3. How to use Doctrine Query Language
  4. When should you use Doctrine Query Language?
  5. Using Parameters
  6. Conclusion

Over the last couple of weeks we’ve looked at Doctrine 2 and how to use it in your PHP projects. Doctrine 2 is an implementation of the Data Mapper pattern and so it allows you to make a clean separation of your application’s business rules from the persistence layer of the database.

In this week’s article I want to take a deep dive into DQL (Doctrine Query Language). I briefly mentioned DQL in How is Doctrine 2 different to Eloquent? but I think it is worth taking a more in-depth look into how DQL works and what are the benefits of using it.

I think DQL really comes into it’s own when you are required to write a complex query or you need to make existing queries more efficient. DQL can be a bit intimidating if you are used to using ORM methods to work with the database, but it is actually pretty easy to use.

What is Doctrine Query Language?

Unless you are really new to building web applications, you are probably already familiar with SQL (Structured Query Language).

SQL is a language for interacting with the data in a relational database. Whenever you use an ORM such as Eloquent or Doctrine, the ORM will be converting back and forth between objects and SQL queries.

When you use SQL you think about the data in your database as a series of tables and relationships.

For example, if I wanted to get all of the users from the database, I would write something like this:

SELECT * FROM users;

If I wanted to get all the posts of a particular user, I would write an SQL query like this:

SELECT * FROM posts WHERE user_id = 1;

SQL is a pretty simply language to use but it can be a really powerful way of managing the data in your database.

Doctrine Query Language is similar to SQL in that it is a language for managing the data in your database. But instead of thinking of your data as tables and relationships, with DQL you should think of your data as objects and properties, just like how you work with the data within your application.

So instead of using tables, you use class names instead:

SELECT p FROM Cribbb\Entities\Post p WHERE p.user_id = 1;

In essence this is not that different from SQL, so if you are already familiar with SQL this shouldn’t be too much of a departure from how you would normally work with a database.

What are the benefits of using DQL over SQL?

I’m going to assume (especially if you already use SQL) that you’re thinking, “what are the benefits of using DQL over SQL?”

Often adding another layer of abstraction can be a benefit if it makes your life easier. But another layer of abstraction is yet another thing to learn and yet another thing for you to diagnose problems with.

I think the big benefit of using DQL is, generally speaking, it is going to be easier to write your database queries in an object oriented way.

For example, if you needed to pull data from multiple tables via a relationship, you don’t have to think of how the data is structured in the database, you only have to think of your entities and how they are related. You are going to be a lot more familiar with your entity objects than you are with your database tables.

This means that you don’t have to think about foreign keys or joining tables together because those kinds of things are just implementation details of how Doctrine is persisting the data to storage.

If you are already very familiar with SQL I can see why DQL seems like an unnecessary abstraction. The real benefits of using DQL is when you need to query the database in a way that can’t be achieved using the default repository methods.

How to use Doctrine Query Language

A DQL statement is simply a string that is passed to the createQuery() method on the EntityManager:

$query = $em->createQuery("SELECT u FROM Cribbb\Entities\User u");

This will return an instance of Doctrine\ORM\Query which you can use to get the results of the query:

$users = $query->getResult();

This will return an array of User objects.

If you wanted to get the posts of a particular user, you could do something like this:

$query = $em->createQuery(
    "SELECT p FROM Cribbb\Entities\Post p JOIN p.user u WHERE u.username = :username"
);
$posts = $query->getResult();

In this example I’m selecting data from two tables by using the relationship on the Post object.

Notice how I’m using the JOIN key word, but I don’t have to write what fields to actually join on because I can just use the object’s existing relationship.

You’ll also notice that I’ve set a parameter to select by the user’s username. I’ll cover parameters later in this article.

So as you can see, using DQL is not that much different from SQL. If you can think of how you want to manage data from the database using SQL, you will find DQL extremely easy to use as it’s really not that much different.

When should you use Doctrine Query Language?

DQL can be a very powerful tool in your toolbox under the right circumstances, but it isn’t really ideal for all situations.

DQL is ideal for when you need to write a complex query that would be difficult to achieve using the default methods of the repository. However, you shouldn’t start writing all the basic functionality of the repository in DQL just because you can.

DQL is really useful when you have a particularly complex or an inefficient query that you need to make as efficient as possible.

DQL is also really powerful when you need to run bulk UPDATE or DELETE queries. This will avoid the problem of having to load thousands of entities into memory when you have to run big sweeping changes on your database.

However you can’t use DQL for INSERT queries. This is because new entities have to be persisted through the Entity Manager to introduce them into the persistence context to ensure consistency of your object model.

Using Parameters

When writing DQL statements, you can set parameters in one of two ways.

Firstly you can use named parameters, like this:

$query = $em->createQuery(
    "SELECT u from Cribbb\Entities\User u WHERE u.username = :name"
);
$query->setParameters(["name" => "philipbrown"]);
$user = $query->getResult();

Or you can use positional parameters, like this:

$query = $em->createQuery(
    "SELECT u from Cribbb\Entities\User u WHERE u.username = ?1"
);
$query->setParameter(1, "philipbrown");
$user = $query->getResult();

I’m not really sure of the benefits of using named parameters over positional parameters or vice versa. Personally I usually use named parameters because I think it make it easier to read when you inevitably have to return to the code at some point in the future.

Conclusion

Doctrine Query Language is a powerful way to query your storage layer in order to work with the objects of your application. By using DQL, you can work with your application’s stored data as objects rather than tables.

DQL is yet another abstraction layer that can be seen as unnecessary if you are already well versed in using SQL. DQL does not provide any efficiency benefits over SQL, but it does allow you to write query in a way that many people might find more intuitive.

You definitely don’t have to use DQL if you want to use Doctrine 2 in your project, but you will probably make your life easier if you do.

This is a series of posts on building an entire Open Source application called Cribbb. All of the tutorials will be free to web, and all of the code is available on GitHub.

Philip Brown

@philipbrown

© Yellow Flag Ltd 2024.