Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1940

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

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Invalid
    • 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

          Activity

          Hide
          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
          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
          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
          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;
          Hide
          Guilherme Blanco added a comment -

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

          Show
          Guilherme Blanco added a comment - According to Fabio, issue seems to be related to a typo in association mapping. Resolving for now.
          Hide
          Marco Pivetta added a comment -

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

          Show
          Marco Pivetta added a comment - Since the resolution indicated that the issue was caused by a typo, I changed the status to "invalid"

            People

            • Assignee:
              Marco Pivetta
              Reporter:
              Enea Bette
            • Votes:
              1 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: