Doctrine related entities additional queries

Question

I am a bit confused as to why Doctrine queries work in this certain way. Supposing that we have Articles and Users entities, with Users having a OneToMany relation with Articles.

Then, in the controller we'd get all the articles and send them to a view.

$em = $this->getEntityManager();    
$articles = $em->getRepository('MyBundle:Article')->findAll();

And in the view we'd loop them.

{% for article in articles %}
    {{ article.author.name }}
{% endfor %}

The problem here is that this piece of code does an additional query (for the article's user) for EVERY SINGLE ARTICLE.

I am aware that we can use DQL, but my question is how does Doctrine work here, why isn't it optimized for this kind of thing, which is pretty common? I find this to be a commonly used 'mistake' through applications, that really slows them down. I've just discovered this and now I have to rewrite so many queries into my controllers.

This also defeats the purpose of the ORM, which should actually provide speed in writing the application. This forces us to write DQL/QB queries instead of taking advantage of the ORM. So, when is the ORM a good idea to use if it performs so bad?

Consider scanning the docs: docs.doctrine-project.org/projects/doctrine-orm/en/latest — Cerad
Look up eager vs lazy fetching, and you'll begin to understand. — Daniel
The problem with eager fetch is that it will always fetch related entities even when they are not needed. — George Irimiciuc
@GeorgeIrimiciuc That is the trade-off. Either you always fetch the related entities in anticipation of iterating over them, or you don't and they are retrieved when needed. The ORM can't know how you are going to use related entities on its own, you need to tell it. — solocommand
But why doesn't it create the query in the view, when I am calling the related entities? Why is it creating separate queries? — George Irimiciuc

A.L A.L · Accepted Answer · 2016-01-23T21:45:29

I am aware that we can use DQL, but my question is how does Doctrine work here, why isn't it optimized for this kind of thing, which is pretty common?

Doctrine can't guess what values you will need on the Twig view.

But why doesn't it create the query in the view, when I am calling the related entities? Why is it creating separate queries?

This could not be the solution. See you code:

{% for article in articles %}
    {{ article.author.name }}
{% endfor %}

To know what values you will have to display, Symfony should iterate over the loop in order to guess what value you will need, that would be a lot of work before fetching the data.

You can tell explicitly to Doctrine what table associations should be added to the query:

In the ArticleRepository repository, you have to join the related table:

<?php

namespace Acme\Bundle\AcmeBundle\Entity;

use Doctrine\ORM\EntityRepository;

class ArticleRepository extends EntityRepository
{
    public function getArticlesWithAuthors()
    {
        $qb = $this->createQueryBuilder('article');

        $query = $qb
            ->select('article, author')
            ->innerJoin('article.author', 'author')
            ->orderBy('a.id', 'DESC')
        ;

        return $query->getQuery()->getResult();
    }
}

Then you can call the method:

$articles = $em->getRepository('MyBundle:Article')->getArticlesWithAuthors();

And Doctrine will load the author associated to the article in the same query.

Doctrine related entities additional queries

1 Answers