Iterate with fetch join in subquery

[lewbor]

There are problem with query->iterate() method when using subquery with fetch join. E.g. there are a User and Group classes, User has a collection of group. When using query like SELECT u from User u WHERE u.id IN (SELECT DISTINCT u2.id from User u2 join u2.groups g WHERE g.name='Admin')
there are exception using iterate() method: Iterate with fetch join in class User using association groups not allowed.
But this query still return one row per User object, and there should be no problems with hydrating.
It's possible to iterate using such queries?

[maxolasersquad]

This happens when using either MANY_TO_MANY or ONE_TO_MANY join in your query then you cannot iterate over it because it is potentially possible that the same entity could be in multiple rows.

If you add a distinct to your query then all will work as it will guarantee each record is unique.

$qb = $this->createQueryBuilder('o');
$qb->distinct()->join('o.manyRelationship');
$i = $qb->iterator;
echo 'Profit!';

[peter-gribanov]

I faced a similar problem.

Iterate with fetch join in class Event using association action not allowed.

DQL

SELECT fa, a FROM FavoriteAction fa INNER JOIN fa.action a INNER JOIN a.events e WHERE fa.user = ?

That's right. The FavoriteAction.action.events is a OneToMany association and its use in selection can lead to unexpected results. But i do not use this field in the selection. I use it only to verify that actions has events. Using DISTINCT in this case is pointless.

So, this condition is not entirely correct and maybe it's a bug.

if ($this->query->getHint(Query::HINT_INTERNAL_ITERATION) === true &&
    (! $this->query->getHint(self::HINT_DISTINCT) || isset($this->selectedClasses[$joinedDqlAlias]))) {
    if ($association instanceof ToManyAssociationMetadata) {
        throw QueryException::iterateWithFetchJoinNotAllowed($owningAssociation);
    }
}

[peter-gribanov]

Anower example

SELECT e.id FROM FavoriteAction fa INNER JOIN fa.action a INNER JOIN a.events e WHERE fa.user = ? GROUP BY e.id

[maxolasersquad]

I do not believe your explanation as to why a distinct is pointless is correct. A FavoriteAction can have many actions, which would cause the query to return the same FavoriteAction multiple times. A distinct will force the results to return only one FavoriteAction, even when it has mutliple actions. Try adding distinct and see if the error goes away.

[peter-gribanov]

@maxolasersquad Yes. Sorry. I forgot to add a grouping. The second example demonstrates the problem. With and without DISTINCT, the result is the same.

[maxolasersquad]

I wonder if you remove the group by and just leave in the distinct if that would work.

[peter-gribanov]

I wonder if you remove the group by and just leave in the distinct if that would work.

Sorry. I grouped the results by the wrong field.

This query returns 37 records for my data (wrong result), but it breaks when trying to iterate.

SELECT ev.id FROM FavoriteAction e INNER JOIN e.action a INNER JOIN a.events ev WHERE e.user = ?

This query returns 37 records for my data.

SELECT DISTINCT ev.id FROM FavoriteAction e INNER JOIN e.action a INNER JOIN a.events ev WHERE e.user = ?

This query also returns 37 records for my data, but it breaks when trying to iterate according to the query results.

SELECT ev.id FROM FavoriteAction e INNER JOIN e.action a INNER JOIN a.events ev WHERE e.user = ? GROUP BY ev.id

Adding a DISTINCT does not lead to error, but this does not affect the result. All the same 37 records are returned.

SELECT DISTINCT ev.id FROM FavoriteAction e INNER JOIN e.action a INNER JOIN a.events ev WHERE e.user = ? GROUP BY ev.id

If i group by action id, then returns 4 records for my data. This is the correct result. This query does not result in error.
Interestingly, if you set setMaxResults() for this query, error will occurs in Doctrine. Disabling this condition will not cause any errors.

SELECT ev.id FROM FavoriteAction e INNER JOIN e.action a INNER JOIN a.events ev WHERE e.user = ? GROUP BY a.id

The addition of DISTINCT prevents the error in the Doctrine, but does not affect the result. The same 4 records are returned.

SELECT DISTINCT ev.id FROM FavoriteAction e INNER JOIN e.action a INNER JOIN a.events ev WHERE e.user = ? GROUP BY a.id

This query will return 37 records for my data (wrong result) and lead to error Notice: Undefined offset: 1 on this line.

SELECT e FROM FavoriteAction e INNER JOIN e.action a INNER JOIN a.events ev WHERE e.user = ?

As in the previous example, adding a setMaxResults() will result in error:

Iterate with fetch join in class Event using association action not allowed.

Adding a group will returns 4 records and will not result in error in ObjectHydrator.
But adding a setMaxResults() still leads to exception in SqlWalker.

SELECT e FROM FavoriteAction e INNER JOIN e.action a INNER JOIN a.events ev WHERE e.user = ? GROUP BY a.id

This query will return the correct data, but will also lead to exception in SqlWalker with using setMaxResults().

SELECT e, a FROM FavoriteAction e INNER JOIN e.action a INNER JOIN a.events ev WHERE e.user = ? GROUP BY a.id

From my experiments, i conclude that this condition does not work correctly. It is wrong to demand the use of DISTINCT where it is not necessary.

[maximecolin]

Hi.

I ran into the same error with a query with a leftJoin on a one-to-many relation but with groupBy on root entity id, ensuring each entity is returned only once.

I'm guessing Doctrine is able to detect join on one-to-many but maybe it's too complicated to assert a groupBy ensure entities unicity in selected rows.

I fixed this by adding ->setHint(SqlWalker::HINT_DISTINCT, true) to my query to bypass the condition without having to add DISTINCT in the query (that could impact performance).

$queryBuilder = $this
    ->createQueryBuilder()
    // ...
    ->select("stuff.id, GROUP_CONCAT(stuff.name separator ', ')")
    ->leftJoin('foobar.stuffs', 'stuff')
    ->groupBy('foobar.id');

return $queryBuilder
    ->getQuery()
    ->setHint(SqlWalker::HINT_DISTINCT, true)
    ->toIterable();