[RFC] Add mention how to disable force-eager and that it will mess up any complex queries

[diimpp]

Hi,

I was doing custom Filter for my current project and just spent like 5 hours debugging empty result for my query, which was result of api-platform/core/src/Bridge/Doctrine/Orm/Extension/FilterEagerLoadingExtension.php wrapping part of my query with IN operator.

I was using groupBy, having with where in query together, which got disrupted as part with where was moved into sub-select.

Next configuration example solved my problem fully.

Acme\Entity\Entity:
    collectionOperations:
        get:
            force_eager: false

Thanks.

[xtfs]

Hello, are you using Groups? I had a problem with this when I was using xdebug, but when disabled the xdebug, worked well.

[diimpp]

@xtfs Hi, what kind of Groups? Normalization context groups? Yes, I do.

I don't think, that I'm using xdebug, unless it's enabled in vanilla docker-compose setup.

[soyuka]

You should definitely disable eager loading when you write your own queries. I do custom queries in data providers. Did you write an extension to alter the query? We may have a bug, could you give me more informations about how to reproduce?

[diimpp]

@soyuka Hi

I was working on custom Filter, not the Extension, my query looks like this

GET /acmes?type.id[]=1&type.id[]=2 SELECT o FROM App\Entity\Acme o LEFT JOIN o.types type WHERE type.id IN ('1', '2') GROUP BY o.id HAVING COUNT(DISTINCT type.id) = 2

Which pulls Acme collection, which will have at least two types of 1 and 2. WHERE IN, GROUP BY and HAVING combination is the key for this query.

But after processing with FilterEagerLoadingExtension, query will become

SELECT o FROM App\Entity\Acme o LEFT JOIN o.types type WHERE o IN(SELECT o_2 FROM App\Entity\Acme o_2 LEFT JOIN o_2.types type_2 WHERE type_2.id IN ('1', '2')) GROUP BY o.id HAVING COUNT(DISTINCT type.id) = 2

Note, how second query introduces IN operator and moves my WHERE IN into subquery, changing the relation aliases, which renders GROUP BY and HAVING constructs useless.

Those queries are example and not exact output of QB, but it should convey the idea.

Thank you for idea with custom data provider.

[soyuka]

May you give me your custom filter so that I can reproduce? Thanks!

[diimpp]

@soyuka Sorry, it will be really cumbersome to share whole filter, but query in question looks like this.

$valueParameter = $queryNameGenerator->generateParameterName($field);
 
$queryBuilder
    ->select('o')
    ->andWhere(sprintf('%s.%s IN (:%s)', $alias, $field, $valueParameter))
    ->setParameter($valueParameter, $values)
;
 
$queryBuilder
    ->addGroupBy('o.id')
    ->andHaving(sprintf('COUNT(DISTINCT %s.%s) = :numberOfValues',  $alias, $field)) // Ensures, that all values are matched, not just some of them.
    ->setParameter('numberOfValues', count($values))
;

I'm using Postgresql, but I don't think it have anything to do with this.

Thanks.

[soyuka]

Thanks, to close this issue we should add a documentation to the Filter section saying that by altering queries, the developer should disable eager loading to avoid mistransformations.