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?

Isn't missing an `ON` here after the LEFT JOIN? – dan-lee Apr 18 '12 at 07:20 — dan-lee, Apr 18 '12 at 07:20
No. This is Doctrine DQL, not SQL. – Sander Marechal Apr 18 '12 at 07:27 — Sander Marechal, Apr 18 '12 at 07:27

score 16 · Answer 1 · answered Dec 30 '12 at 15:24

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.

Stephen Fuhry · Answer 2 · 2012-04-18T17:19:23.973

7

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

edited Apr 18 '12 at 17:19

answered Apr 18 '12 at 13:22

Stephen Fuhry

12,624
6
56
55

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. – Sander Marechal Apr 18 '12 at 15:21
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 – Stephen Fuhry Apr 18 '12 at 17:17
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. – Sander Marechal Apr 19 '12 at 05:46

score 6 · Accepted Answer · answered Apr 19 '12 at 05:49

6

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 Apr 19 '12 at 05:49

Sander Marechal

22,978
13
65
96

Ah, that makes sense. I do seem to remember running into that once. Kindof an annoying workaround. – Stephen Fuhry Apr 19 '12 at 15:29

score 4 · Answer 4 · edited Aug 31 '21 at 12:18

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')
    ->....

edited Aug 31 '21 at 12:18

Kamafeather

8,663
14
69
99

answered May 16 '14 at 14:30

Hennes

1,340
1
10
26

score 2 · Answer 5 · answered Jan 23 '13 at 10:08

2

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

answered Jan 23 '13 at 10:08

Tarjei Huse

1,231
11
14

score 2 · Answer 6 · answered Aug 31 '17 at 15:34

2

I use this

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

answered Aug 31 '17 at 15:34

despotbg

740
6
12

1

You are querying not an entity in a whole, but only it's identity. – Vladislav Rastrusny Dec 08 '17 at 13:42

Doctrine query distinct related entity

6 Answers6

Linked