Doctrine 2 - ORM

Doctrine DQL: erroneous sql generation from dql join with "WITH" or "WHERE" clause

Details

  • Type: Bug Bug
  • Status: Open Open
  • Priority: Major Major
  • Resolution: Unresolved
  • Affects Version/s: 2.2.2
  • Fix Version/s: None
  • Component/s: DQL
  • Security Level: All
  • Labels:
    None
  • Environment:
    LAMP, debian squeeze

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.

Issue Links

is duplicated by

Bug - A problem which impairs or prevents the functions of the product. DDC-2235 Single table inheritance discriminator in WHERE when using arbitrary join syntax

  • Major - Major loss of function.
  • Resolved - A resolution has been taken, and it is awaiting verification by reporter. From here issues are either reopened, or are closed.

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Benjamin Eberlei added a comment -

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
Show
Benjamin Eberlei added a comment - 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
Hide
Permalink
Guilherme Blanco added a comment -

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.

Show
Guilherme Blanco added a comment - 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.
Hide
Permalink
Hugo Henrique added a comment -

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
Show
Hugo Henrique added a comment - 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
Hide
Permalink
Fabio B. Silva added a comment -

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;
Show
Fabio B. Silva added a comment - 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;

People

  • Assignee:
    Guilherme Blanco
    Reporter:
    Enea Bette
Vote (1)
Watch (7)

Dates

  • Created:
    Updated: