Doctrine query distinct related entity

Issue

I’m probably overlooking something very simple and just been staring at it too much, but I can’t get this DQL query to work. I get an exception stating:

Cannot select entity through identification variables without choosing at least one root entity alias.

Here’s my query. User has a many-to-one relation to Group. Note that this is a unidirectional relation! That may make no sense to you, but it makes sense in our domain logic.

SELECT DISTINCT g
FROM Entity\User u
LEFT JOIN u.group g
WHERE u.active = :active

Can you tell me what I am missing here?

Solution

I worked around the problem by doing a subselect:

SELECT g
FROM Entity\Group
WHERE g.id IN (
    SELECT DISTINCT g2.id
    FROM Entity\User u
    LEFT JOIN u.group g2
    WHERE u.active = :active
)

Answered By – Sander Marechal

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0