[DDC-1940] Doctrine DQL: erroneous sql generation from dql join with "WITH" or "WHERE" clause Created: 23/Jul/12  Updated: 25/Aug/13  Resolved: 25/Aug/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.2.2
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Enea Bette Assignee: Marco Pivetta
Resolution: Invalid Votes: 1
Labels: None
Environment:

LAMP, debian squeeze


Attachments: File Entities.rar    
Issue Links:
Duplicate
is duplicated by DDC-2235 Single table inheritance discriminato... Resolved

 Description   

I'm having big troubles while developing a quietly advanced DQL query for a tiny DMS: The schema: DmsObject is a superclass for which two subclasses exist (document and folder) UserRights and GroupRight (which are associative entities in the db, pointing respectively to user and group tables). User and Group represent (obvious) the dms "actors".

SELECT o, ur, gr 
from module\EDMS\business\DmsObject o 
join o.userRights ur 
join o.groupRights gr
WHERE o.ownerUser=ur.user
AND o.ownerGroup=gr.group

The WHERE condition is WRONG! Doctrine switches the two tables. I've already checked the mapping (it's ok!) and checked also where the fk's point in the database (ok!).

...
LEFT JOIN dms_folder d1_ 
    ON d0_.id = d1_.id 
LEFT JOIN dms_document d2_ 
    ON d0_.id = d2_.id 
INNER JOIN dms_user_object_rights d3_ 
    ON d0_.id = d3_.document_id 
INNER JOIN dms_group_object_rights d4_ 
    ON d0_.id = d4_.document_id 
WHERE d0_.sys_group_owner = d3_.user_id 
    AND d0_.sys_user_owner = d4_.group_id
...

This seems to be a bug in the DQL translator.



 Comments   
Comment by Benjamin Eberlei [ 29/Jul/12 ]

Enea Bette Can you attach the entities (stripped down to the fields we need here)?

Can you check guilherme? This looks really weird.

It should be:

WHERE d0_.sys_user_owner = d3_.user_id AND d0_.sys_group_owner = d4_.group_id
Comment by Guilherme Blanco [ 29/Jul/12 ]

Enea Bette Can you please provide your entities?
I can try to reproduce the issue, but I need your entities as a base for a failing unit test.

Comment by Hugo Henrique [ 11/Apr/13 ]

I'm having a similar problem with the query:

SELECT um, p FROM Ciwwic\AppBundle\Entity\Provider p LEFT JOIN Ciwwic\UserBundle\Entity\UserMeta um WITH um.user = p.id WHERE p.id = 30

When you run this query DQL she returns an empty array.
I getting solve my problem by adding WHERE clauses example as:

SELECT p, um FROM Ciwwic\AppBundle\Entity\Provider p LEFT JOIN Ciwwic\UserBundle\Entity\UserMeta um WHERE p.id = 30 AND um.user = 30
Comment by Fabio B. Silva [ 14/Apr/13 ]

Hi Enea

If i got it correctly
Your associations DmsObject#ownerUser and DmsObject#ownerGroup are flipped.
Note that ownerUser points to sys_group_owner and ownerGroup to sys_user_owner

/**
 * @ORM\ManyToOne(targetEntity="library\system\business\User", fetch="EAGER")
 * @ORM\JoinColumn(name="sys_group_owner", referencedColumnName="ID")
 */
protected $ownerUser;
/**
 * @ORM\ManyToOne(targetEntity="library\system\business\Group", fetch="EAGER")
 * @ORM\JoinColumn(name="sys_user_owner", referencedColumnName="ID")
 */
protected $ownerGroup;

It should be :

/**
 * @ORM\ManyToOne(targetEntity="library\system\business\User", fetch="EAGER")
 * @ORM\JoinColumn(name="sys_user_owner", referencedColumnName="ID")
 */
protected $ownerUser;

/**
 * @ORM\ManyToOne(targetEntity="library\system\business\Group", fetch="EAGER")
 * @ORM\JoinColumn(name="sys_group_owner", referencedColumnName="ID")
 */
protected $ownerGroup;
Comment by Guilherme Blanco [ 19/Aug/13 ]

According to Fabio, issue seems to be related to a typo in association mapping.
Resolving for now.

Comment by Marco Pivetta [ 25/Aug/13 ]

Since the resolution indicated that the issue was caused by a typo, I changed the status to "invalid"

Generated at Wed Oct 22 10:05:35 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.