How do I perform relational division using DQL?

Question

When trying to perform relational division using Doctrine 1.2, I receive a "Duplicate alias" error over what seems like the DISTINCT found in the query below:

SELECT 
    Data.ID  
FROM 
    Data 
INNER JOIN 
    TaggedData ON (Data.id = TaggedData.data_id) 
INNER JOIN 
    Tag ON (Tag.id = TaggedData.tag_id) 
WHERE 
    Tag.id IN ('1' , '2') 
HAVING COUNT(DISTINCT tag.iD)=2

If I remove DISTINCT from the query, it runs but doesn't get me what I want. Is there a proper way to get Doctrine past this issue?

Specific code:

    $query = $this->createQuery("p")
        ->select("p.*")
        ->innerJoin("p.Data s")
        ->innerJoin("s.Tags c")
    ;
    $query
        ->andWhereIn("c.id", $tags)
        ->addHaving("COUNT(DISTINCT c.id) = ?", count($tags))
    ;

score 1 · Accepted Answer · answered Oct 03 '12 at 16:29

1

Since you are returning only a single value, just execute the raw SQL:

    $sql = <<<SQL

SELECT 
    Data.ID  
FROM 
    Data 
INNER JOIN 
    TaggedData ON (Data.id = TaggedData.data_id) 
INNER JOIN 
    Tag ON (Tag.id = TaggedData.tag_id) 
WHERE 
    Tag.id IN ('1' , '2') 
HAVING COUNT(DISTINCT tag.iD)=2

SQL;

    $conn = Doctrine_Manager::getInstance()->getConnection('connection_name');
    $id = $conn->fetchOne($sql);

answered Oct 03 '12 at 16:29

Ross Smith II

11,799
1
38
43

My example simplified a bit too much in this case. I will be returning several instances of objects as a result. – Alexander Trauzzi Oct 03 '12 at 16:32

score 1 · Answer 2 · answered Oct 03 '12 at 18:16

1

You need a GROUP BY clause to use having without distinct. So i guess that if you add GROUP BY Data.ID just before HAVING all should be fine.

answered Oct 03 '12 at 18:16

psadac

2,312
5
32
41

Are you sure? http://stackoverflow.com/a/12711729/128991 - this seems to work in regular SQL, I'm just trying to bend it into DQL now. – Alexander Trauzzi Oct 03 '12 at 18:26
1

Perhaps it's not necessary to add a group by, but using `HAVING` without `GROUP BY` is not a best practice. By the way, i'm not a Doctrine expert but `->groupBy('Data.ID')` should solve your problem – psadac Oct 03 '12 at 18:38
Nah, the problem here is definitely Doctrine related. It keeps bugging out on the DISTINCT being in the HAVING. – Alexander Trauzzi Oct 03 '12 at 19:00
As it turns out, I did need it after switching to doing the query raw! Thanks for at least mentioning this as I do believe it's solved my last issue. – Alexander Trauzzi Oct 03 '12 at 19:50

How do I perform relational division using DQL?

Specific code:

2 Answers2