Doctrine fetch EAGER in many-to-many association

Question

Using Symfony 3.2 and Doctrine 2.5, I have trouble to understand how fetch "EAGER" should work in many-to-many and many-to-one relationship.

Lets say we have two entities, User and Site, and a many-to-many association:

class User
{
    /**
     * @ORM\ManyToMany(targetEntity="Site", inversedBy="users", fetch="EAGER")
     * @ORM\JoinTable(name="user_site")
     */
    private $sites;
}

class Site
{
    /**
     * @ORM\ManyToMany(targetEntity="User", mappedBy="sites")
     */
    private $users;
}

In my controller, i simply call

 $users = $this->getDoctrine()->getRepository('CoreBundle:User')->findAll();

I want to see only 1 query on the User table with a join on the Site table, but i get N+1 queries. I have also did some tests with many-to-one associations and get the same result.

I know that i can do the join by myself using DQL, but i want to understand how fetch "EAGER" does work.

What is the expected behavior with fetch "EAGER" on many-to-many and many-to-one associations ?

See this https://stackoverflow.com/a/45526015/7135824 – ste Aug 11 '17 at 21:34 — ste, Aug 11 '17 at 21:34

score 3 · Answer 1 · answered Nov 04 '21 at 18:31

many-to-many

The expected behavior with fetch eager on many-to-many associations is exactly what you described.

If we access 3 Post objects in our code which have a many-to-many association to Tags there are 4 queries happening, no matter if we use fetch="EAGER" or fetch="LAZY":

SELECT t0.id AS id_1, t0.title AS title_2 FROM post t0
SELECT t0.id AS id_1, t0.title AS title_2 FROM tag t0 INNER JOIN post_tag ON t0.id = post_tag.tag_id WHERE post_tag.post_id = 1
SELECT t0.id AS id_1, t0.title AS title_2 FROM tag t0 INNER JOIN post_tag ON t0.id = post_tag.tag_id WHERE post_tag.post_id = 2
SELECT t0.id AS id_1, t0.title AS title_2 FROM tag t0 INNER JOIN post_tag ON t0.id = post_tag.tag_id WHERE post_tag.post_id = 3

one-to-many

The expected behavior with fetch eager on one-to-many associations is different. By using fetch="EAGER" we can reduce the number of queries.

If we access 3 Post objects in our code which have a one-to-many association to Comments there is 1 query happening when using fetch="EAGER":

SELECT
    t0.id AS id_1,
    t0.title AS title_2,
    t3.id AS id_4,
    t3.title AS title_5,
    t3.post_id AS post_id_6
FROM post t0
LEFT JOIN comment t3 ON t3.post_id = t0.id

But 4 queries if we use fetch="LAZY":

SELECT t0.id AS id_1, t0.title AS title_2 FROM post t0
SELECT t0.id AS id_1, t0.title AS title_2, t0.post_id AS post_id_3 FROM comment t0 WHERE t0.post_id = 1
SELECT t0.id AS id_1, t0.title AS title_2, t0.post_id AS post_id_3 FROM comment t0 WHERE t0.post_id = 2
SELECT t0.id AS id_1, t0.title AS title_2, t0.post_id AS post_id_3 FROM comment t0 WHERE t0.post_id = 3

My conclusion

Never use fetch="EAGER" on many-to-many associations because it only can slow down your performance by loading more data from the DB than needed (but can never improve it). Optionally use fetch="EAGER" on one-to-many associations if it improves your performance.

If we need to reduce queries for many-to-many associations we have to write custom DQL queries.

Doctrine fetch EAGER in many-to-many association

1 Answers1

many-to-many

one-to-many

My conclusion