Symfony/Doctrine: How to reduce the num of SELECT-queries? (Multi-level associated entities; twig + jsonSerialize())

Question

Due to the answer of my last question I was able to reduce the SELECT-queries on the first level. Unfortunately the associated Entities are linked deeper, e.g.:

Item -> Group -> Subscriber -> User -> username

Repository method:

// ItemRepository
public function findAll() {
    return $this->createQueryBuilder('item')
                ->addSelect('groups')->join('item.groups', 'groups')
                ->getQuery()->getResult()
}

twig template:

{% for item in items %}
    {# Level: 0 #}
    Name: {{ item.name }}<br/>
    Groups:<br/>
    <ul>
        {# Level: 1 #}
        {% for group in item.groups %}
           <li>{{ group.name }}<br/>
               <ol>
               {# Level: 2 #}
               {% for subscriber in group.subscribers %}
                   {# Level: 3 #}
                   <li>{{ subscriber.user.username }}</li>
               {% endfor %}
               </ol>
           </li>
        {% endfor %}
    </ul>
{% endfor %}

Note: I'm using jsonSerialize to prepare the JSON-data, which includes multi-level iterating as well.

use JsonSerializable;
// ...

class Item implements JsonSerializable {

    // ...

    public function jsonSerialize() {
        $subscribers = array();
        $groups      = $this->getGroups();
        foreach ($groups as $group) {
            foreach ($group->getSubscribers() as $subscriber) {
                $subscribers[$subscriber->getId()] = array(
                    'userId'   => $subscriber->getUser()->getId();
                    'username' => $subscriber->getUser()->getUsername();
                );
            }
        }

        return array(
            'id'          => $this->getId(),
            'subscribers' => $subscribers
            // ...
        );
    }
}

Is there a way to join the deeper associated data as well to reduce the number of SELECT-queries once more (for twig and jsonSerialize())?

Congratulations! You post the 10 000 question! – callmemath Jul 29 '15 at 17:58 — callmemath, Jul 29 '15 at 17:58

score 3 · Accepted Answer · answered Jul 29 '15 at 18:27

I suggest you to change fetch mode in the specific query, as described here in the doc.

So you can describe your query as follow:

$qb =  $this->createQueryBuilder('item')
                ->addSelect('groups')->join('item.groups', 'groups'); // Not necessary anymore

        $query = $qb->getQuery();
        // Describe here all the entity and the association name that you want to fetch eager
        $query->setFetchMode("YourBundle\\Entity\\Item", "groups", ClassMetadata::FETCH_EAGER);
        $query->setFetchMode("YourBundle\\Entity\\Groups", "subscriber", ClassMetadata::FETCH_EAGER);
        $query->setFetchMode("YourBundle\\Entity\\Subscriber", "user", ClassMetadata::FETCH_EAGER);
        ...

return $qb->->getResult();

NB:

Changing the fetch mode during a query is only possible for one-to-one and many-to-one relations.

Hope this help

Thanks a lot! Exactly what I was looking for, because I want to avoid `fetch='EAGER'` in my Entity. Note again: `it's not working for many-to-many`! — Mr. B., Jul 30 '15 at 07:55

Symfony/Doctrine: How to reduce the num of SELECT-queries? (Multi-level associated entities; twig + jsonSerialize())

1 Answers1