DDC-1940: Doctrine DQL: erroneous sql generation from dql join with "WITH" or "WHERE" clause #2611

Closed
doctrinebot opened this Issue Jul 23, 2012 · 9 comments

2 participants

@doctrinebot

Jira issue originally created by user noise085:

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.

@doctrinebot

Comment created by @beberlei:

[~noise085] 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
@doctrinebot

Comment created by @guilhermeblanco:

[~noise085] 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.

@doctrinebot

Comment created by noise085:

DmsObject (Superclass), Document (SubClass), Folder(SubClass, composite of Folder and Documents).

UserRight, GroupRight (associative entities, relation: 0..n 0..n DmsObject->User DmsObject->Group)

Group, User (system entities, no inverse association is defined!).

@doctrinebot

Comment created by hugohenrique:

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

@doctrinebot

Comment created by @FabioBatSilva:

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;
@doctrinebot

Comment created by @guilhermeblanco:

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

@doctrinebot

Comment created by @ocramius:

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

@doctrinebot

Issue was closed with resolution "Invalid"

@Ocramius Ocramius was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
@doctrinebot doctrinebot added the Bug label Dec 7, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment