Work with Doctrine in Symfony framework. DQL (Lesson 11. Doctrine. Continuation)

In the previous note, we learned how to write simple queries to the database using Doctrine. But often we have to write something more kinky, with these joins of yours =). Here we will try to understand how it's done.

Doctrine has its own query system, which is very similar to SQL in many ways, but there are still differences, and this system is called DQL (Doctrine Query Language, not to be confused with Data Query Language)

As I already mentioned in the introductory note on Doctrine, don't confuse DQL with regular SQL queries, as DQL is queries to the model, not the database.

To work with queries in Symfony framework, you need an instance of the Entity Manager. Obtain it using

$em = $this->getDoctrine()->getManager();

(inside a controller's descendant). By the way, you can also obtain it using

$em = $this->getDoctrine()->getEntityManager();

but this method is deprecated.

Example of a query:

$em = $this->getDoctrine()->getManager();
$query = $em->createQuery(
'SELECT p FROM AcmeStoreBundle:Product p'
);
$products = $query->getResult();

This particular example is useless in practice, as you can achieve a similar selection without DQL, but it will help us understand the structure of the queries. So, the structure of the query differs from a regular SQL query in that we can make a selection of the model itself. So the first "p" means a selection of all "fields" (but these are fields of the model), and the second one is just like in SQL - it defines "p".

Parameters can be added to the query using the ":" symbol:

$em = $this->getDoctrine()->getManager();
$query = $em->createQuery(
'SELECT p FROM AcmeStoreBundle:Product p WHERE p.price > :price ORDER BY p.price ASC'
)->setParameter('price', '19.99');
$products = $query->getResult();

Assigning parameters as shown above automatically protects against SQL injections.

In the examples, we ended up with a two-dimensional array, to get a one-dimensional array, you need to call

$product = $query->getSingleResult();

Very often, we need to use JOINs. (an SQL operand that joins tables, but the goal of this note is to show how it's done in Doctrine).

If the model is generated correctly (for this, you had to create connections inside the database), the query will be as follows:

SELECT p, c FROM AcmeStoreBundle:Product p
JOIN p.category c

Notice that "p.category" in the model is a binding field, it returns not a value but a model. In this example, we obtained a selection from both tables.

As an alternative to the Entity Manager, you can use the query builder. Example:

$repository = $this->getDoctrine()
   ->getRepository('AcmeStoreBundle:Product');
$query = $repository->createQueryBuilder('p')
   ->where('p.price > :price')
   ->setParameter('price', '19.99')
   ->orderBy('p.price', 'ASC')
   ->getQuery();
$products = $query->getResult();

That concludes the introduction to Doctrine in Symfony framework. Some questions remain unanswered, such as manually constructing the model. You can read about it in the official documentation, for example: http://symfony.com/doc/current/book/doctrine.html.

P.S. When updating/adding records to tables, there is sometimes a need to set default values. You can define them in the model classes (remember, src/path_to_bundle/Entity?), assigning values to the corresponding variables in the description.