0

I'm encountering the dreaded N+1 problem using Doctrine, specifically when fetching tables with bidirectional one-to-one relationships. I now understand that bidirectional one-to-one relationships should be avoided in Doctrine, but I'm working on an old codebase and I want to change as little as possible since I don't know if there could be side effects.

Context

I have an Intervention table, with a Many-To-One relationship with the User table. The User table has a One-To-One relationship with the UserIdentity table. The relationship is mapped by User, and inversed by UserIdentity

The problem

I encounter the N+1 problem when trying to query the Intervention table and joining the User relation. For every unique User, a query is made to fetch a corresponding UserIdentity. This leads to about 1K unique queries.

I have faced a similar problem before, and solved it by adding the inverse side of the One-To-One relation to my SELECT call, and LEFT JOIN-ing it. However in this case it does not seem to work. I suspect this is because UserIdentity is not a direct association of Intervention, but of User instead.

What I tried

Here's what my code looks like thus far. I've trimmed everything that's irrelevant to the problem.

        $alias = "intervention";

        $qb = $this->manager
        ->createQueryBuilder()
        ->select("partial $alias .{id, uuid, purpose, type, creation, detectorUuid}, intervention_user, trackdechetsIdentity, fibsd")
        ->from(Intervention::class, $alias)
    ;

    $qb = $qb
        ->leftJoin("$alias.user", 'intervention_user')
        ->leftJoin("intervention_user.trackdechetsIdentity", "trackdechetsIdentity") // This is the UserIdentity entity
        ->leftJoin("$alias.fibsd", "fibsd") // Solving another N+1 problem, this time it's a direct one-to-one relation
        ->orderBy("$alias.id", 'DESC')
    ;
    
    return $qb->getQuery()->getResult();

This generates about as many queries as there are users in my database. Anyone know how to solve this?

Martin Lebard
  • 1

1 Answers1

0

I'll answer my own question.

I still don't know why I can't resolve this the way I did with a direct one-to-one relationship. However, using the solution from this answer did just fine.

By changing the relationship from One-To-One to a Many-To-One/One-To-Many allows for lazy loading and gets rid of the N+1 problem. See the linked answer for more explanation

Martin Lebard
  • 1