doctrine/archived-orm
1
0
Fork 0
mirror of https://github.com/doctrine/orm.git synced 2026-03-24 06:52:09 +01:00
Code Issues 5.3k Packages Projects Releases 100 Wiki Activity

Iterate with fetch join in subquery #5149

New Issue
Open
opened 2026-01-22 14:59:42 +01:00 by admin · 9 comments
admin commented 2026-01-22 14:59:42 +01:00
Owner
Copy Link

Originally created by @lewbor on GitHub (Jun 10, 2016).

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?

Originally created by @lewbor on GitHub (Jun 10, 2016). 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?
admin commented 2026-01-22 14:59:43 +01:00
Author
Owner
Copy Link

@maxolasersquad commented on GitHub (Oct 26, 2016):

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!';
@maxolasersquad commented on GitHub (Oct 26, 2016): 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. ``` php $qb = $this->createQueryBuilder('o'); $qb->distinct()->join('o.manyRelationship'); $i = $qb->iterator; echo 'Profit!'; ```
admin commented 2026-01-22 14:59:44 +01:00
Author
Owner
Copy Link

@peter-gribanov commented on GitHub (Sep 25, 2019):

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 commented on GitHub (Sep 25, 2019): 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](https://github.com/doctrine/orm/blob/master/lib/Doctrine/ORM/Query/SqlWalker.php#L978-L983) is not entirely correct and maybe it's a bug. ```php 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); } } ```
admin commented 2026-01-22 14:59:44 +01:00
Author
Owner
Copy Link

@peter-gribanov commented on GitHub (Sep 25, 2019):

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
@peter-gribanov commented on GitHub (Sep 25, 2019): 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 ```
admin commented 2026-01-22 14:59:45 +01:00
Author
Owner
Copy Link

@maxolasersquad commented on GitHub (Sep 25, 2019):

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.

@maxolasersquad commented on GitHub (Sep 25, 2019): I do not believe your explanation as to why a distinct is pointless is correct. A `FavoriteAction` can have many `action`s, 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 `action`s. Try adding `distinct` and see if the error goes away.
admin commented 2026-01-22 14:59:45 +01:00
Author
Owner
Copy Link

@peter-gribanov commented on GitHub (Sep 25, 2019):

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

@peter-gribanov commented on GitHub (Sep 25, 2019): @maxolasersquad Yes. Sorry. I forgot to add a grouping. The second example demonstrates the problem. With and without `DISTINCT`, the result is the same.
admin commented 2026-01-22 14:59:46 +01:00
Author
Owner
Copy Link

@maxolasersquad commented on GitHub (Sep 25, 2019):

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

@maxolasersquad commented on GitHub (Sep 25, 2019): I wonder if you remove the group by and just leave in the distinct if that would work.
admin commented 2026-01-22 14:59:46 +01:00
Author
Owner
Copy Link

@peter-gribanov commented on GitHub (Sep 26, 2019):

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.

@peter-gribanov commented on GitHub (Sep 26, 2019): > 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](https://github.com/doctrine/orm/blob/master/lib/Doctrine/ORM/Query/SqlWalker.php#L978-L983) 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](https://github.com/doctrine/orm/blob/master/lib/Doctrine/ORM/Internal/Hydration/ObjectHydrator.php#L379) 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](https://github.com/doctrine/orm/blob/master/lib/Doctrine/ORM/Query/SqlWalker.php#L978-L983) does not work correctly. It is wrong to demand the use of `DISTINCT` where it is not necessary.
admin commented 2026-01-22 14:59:47 +01:00
Author
Owner
Copy Link

@maximecolin commented on GitHub (Feb 19, 2024):

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();
@maximecolin commented on GitHub (Feb 19, 2024): 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). ```php $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(); ```
admin commented 2026-01-22 14:59:47 +01:00
Author
Owner
Copy Link

@jurchiks commented on GitHub (Jan 3, 2026):

Just ran into this problem, what I had was the following:

$this->createQueryBuilder('u')
->select(...)
->groupBy('u.id')
->where(...)
// the following was added:
->leftJoin('u.groups', 'ug')
->andWhere('ug.id  = ?') // OR 'ug.id IS NULL' depending on filter value

u.groups definition in User entity:

/** @var Collection<UserGroup> */
#[ORM\JoinTable(name: 'user_to_group')]
#[ORM\JoinColumn(name: 'user_id', onDelete: 'CASCADE')]
#[ORM\InverseJoinColumn(name: 'group_id', onDelete: 'CASCADE')]
#[ORM\ManyToMany(targetEntity: UserGroup::class, fetch: 'EXTRA_LAZY', indexBy: 'id')]
private Collection $groups;

Despite the query already being grouped by user ID, and ug not appearing in SELECT at all, only used to check if a user has the specified group (or none at all), the code still required me to add distinct() even though that changes absolutely nothing in the result set.

The SQL for this query works perfectly without distinct(); this is clearly an incorrect assumption on Doctrine's part.

@jurchiks commented on GitHub (Jan 3, 2026): Just ran into this problem, what I had was the following: ```php $this->createQueryBuilder('u') ->select(...) ->groupBy('u.id') ->where(...) // the following was added: ->leftJoin('u.groups', 'ug') ->andWhere('ug.id = ?') // OR 'ug.id IS NULL' depending on filter value ``` `u.groups` definition in User entity: ```php /** @var Collection<UserGroup> */ #[ORM\JoinTable(name: 'user_to_group')] #[ORM\JoinColumn(name: 'user_id', onDelete: 'CASCADE')] #[ORM\InverseJoinColumn(name: 'group_id', onDelete: 'CASCADE')] #[ORM\ManyToMany(targetEntity: UserGroup::class, fetch: 'EXTRA_LAZY', indexBy: 'id')] private Collection $groups; ``` Despite the query already being grouped by user ID, and `ug` not appearing in SELECT at all, only used to check if a user has the specified group (or none at all), the code still required me to add `distinct()` even though that changes absolutely nothing in the result set. The SQL for this query works perfectly without `distinct()`; this is clearly an incorrect assumption on Doctrine's part.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
BC Break
Bug
Can't Fix
Deprecation
Documentation
DQL
Duplicate
Failing Test
Hacktoberfest
Improvement
Improvement
Incomplete
Invalid
Missing Tests
New Feature
PR Created
pull-request
Mirrored from GitHub Pull Request
 Question
Regression
Status: Waiting feedback
Waiting feedback
WIP
Won't Fix
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
admin
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5149
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?