How to optimize a query from N+1 without breaking the data in doctrine2?

Question

There are the following tables: countries, visas, embassies. (one country can have many visas and embassies).

I make a query that gets several countries records:

$countries = $this->entityManager
    ->createQueryBuilder()
    ->select('c')
    ->from(Country::class, 'c')
    ->setMaxResults($condition->first)
    ->setFirstResult($condition->offset)
    ->getQuery()
    ->getResult();

ok, everything works.

further, when we work with $currencies in a loop and get visas and embassises, we get a problem with N + 1 queries. In my case it is 200+ queries at a time.

Let's try to solve the problem with join:

$countries = $this->entityManager
    ->createQueryBuilder()
    ->select('c')
    ->from(Country::class, 'c')
    ->leftJoin('c.visas', 'cv')
    ->addSelect('cv')
    ->leftJoin('c.embassies', 'ce')
    ->addSelect('ce')
    ->setMaxResults($condition->first)
    ->setFirstResult($condition->offset)
    ->getQuery()
    ->getResult();

so this solves the problem with N+1, turning 200+ queries into 4 queries, but the following problem appears:

for example, in our table (or found by query condition) there is 1 country record, but this country, for example, has 3 visas and 5 embassies records in the database.

So, in the case of using join, when 1 country is found, then the relations on visas and embassises will also return 1 record (and it doesn’t matter that there may be more of them).

This behavior is due to leftJoin, and on the one hand - these are 200+ queries and on the other hand - incomplete data return if there are fewer countries records than possible relations.

How can I solve this problem?

Answer 1

You can't solve it with join, you should make other queries for additional data using WHERE IN operator. First you get your countries, get array of their id, for example $ids, and then make query like

$queryBuilder->where('embassies.country_id IN (:ids)')
             ->setParameter('ids', $ids)

And do the same thing for visas. So instead of 200+ queries you can get all the data with 3.