CODEWITHSUNDEEP
phpsymfonydoctrine-orm
George Irimiciuc
George Irimiciuc

Reputation: 4633

Doctrine related entities additional queries

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?

Upvotes: 0

Views: 379

Answers (1)

A.L
A.L

Reputation: 10483

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.

Upvotes: 2

Related Questions