[SOLVED] Doctrine n+1 issue with ManyToMany relation despite using EAGER loading

Doctrine n+1 issue with ManyToMany relation despite using EAGER loading

I am testing out API Platform and I am running into n+1 issues with Doctrine. I have an entity called Car which has a ManyToMany relation with Driver. I have not done anything custom, I am just testing API Platform in it's default (apparently) state.

Car entity:

#[ORM\ManyToMany(targetEntity: Driver::class, mappedBy: 'car', fetch: 'EAGER')]
#[ORM\JoinTable(name: "cars_to_drivers")]
    private Collection $drivers;

Driver entity:

 #[ORM\ManyToMany(targetEntity: Car::class, mappedBy: 'driver', fetch: 'EAGER')]
 #[ORM\JoinTable(name: "cars_to_drivers")]
        private Collection $cars;

When I hit the GET endpoint to get the cars (30 per page), I can see in the Doctrine profiler that there are 32 queries being run: one to get the count, one to get all the cars, and one query per car to get the drivers for that car. Doctrine seems to insist on making these extra queries despite me forcing eager loading. How do I solve this problem?

Solution

You do not need to revert to raw sql to accomplish N+1 solutions. As Cerad suggested, you need to make a custom query, but he didn't mean SQL. This can be done in DQL or with the QueryBuilder.

This is the simplest example. This will remove the N+1 problem when selecting cars with related drivers.

// CarRepository.php
    public function getCars()
    {
        return $this->createQueryBuilder('car')
            ->innerJoin('car.drivers', 'drivers')
            ->addSelect('drivers');
            ->getQuery()
            ->getResult();
    }
// ...

Then you use the getCars query instead of whatever you are currently using. Of course you can filter or limit the query (for pagination).