Doctrine query distinct related entity

Question

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?

Sarajane

Isn't missing an ON here after the LEFT JOIN? – Conversational

No. This is Doctrine DQL, not SQL. – Sarajane

Answer 1

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
)

Sarajane

Ah, that makes sense. I do seem to remember running into that once. Kindof an annoying workaround. – Kalpak

Answer 2

Since this is the first Google match when searching for the error message "Cannot select entity through...", I decided to respond despite the topic was posted few months ago.

The trick is to use JOIN ... WITH ... (like JOIN ... ON ... in SQL).

I was having the message with this code:

SELECT ro, COUNT(ro)
FROM FH\MailerBundle\Entity\Recipient r
JOIN r.selectedOption ro
GROUP BY ro.id

I solved the problem by this code:

SELECT ro, COUNT(ro)
FROM FH\MailerBundle\Entity\RecipientOption AS ro
JOIN FH\MailerBundle\Entity\Recipient AS r WITH r.selectedOption = ro
GROUP BY ro.id

I needed to specify full namespaces and classes for both entities.

Neoimpressionism

Answer 3

You need to select FROM the root entity alias.. meaning you can't SELECT only from a table you're joining on, as you can in plain sql.. so something like this should do it:

SELECT DISTINCT g
FROM Entity\Group g
INNER JOIN g.user u
WHERE u.active = :active

Kalpak

Oops, a transcription error on my part. My apologies. My actual entities are named differently, though their relation (and the rest of the DQL) is the same. – Sarajane

Ah, in that case, it's complaining about you querying FROM Users, but not including the Users Entity in the SELECT portion of your query, hence the at least one root entity alias in your warning - root entity alias just means what's in your FROM clause, for all intensive purposes.. I've updated my answer – Kalpak

And that's the exact problem. My domain model says that the relationship is uni-directional so I can't query from the other side like you are doing here. I worked around it by doing a subselect. – Sarajane

Answer 4

I had a similar problem and solved it by multiple from calls, like this:

$this->getDoctrine()->createQueryBuilder()
    ->from('ProjectMainBundle:Group', 'g')
    ->from('ProjectMainBundle:User', 'u')
    ->select('distinct(g)')
    ->where('u.group = g')
    ->andWhere('u.active = :active')
    ->....

Eunuchoidism

Answer 5

You can do this using DQL's new WITH keyword:

SELECT DISTINCT g
FROM Entity\User u
LEFT JOIN Entity\Group g
WITH u in g.users
WHERE u.active = :active

Sandbox

Answer 6

I use this

$qb = $this->createQueryBuilder('o')
           ->select('DISTINCT IDENTITY(o.user)')

Educable

You are querying not an entity in a whole, but only it's identity. – Gibbous