Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1256

Generated SQL error with DQL WITH and JOINED inheritance

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Minor Minor
    • Resolution: Duplicate
    • Affects Version/s: 2.0.3, 2.3
    • Fix Version/s: None
    • Component/s: DQL
    • Security Level: All
    • Labels:
      None
    • Environment:
      Ubuntu 11.04

      Description

      I've created an entity that has a one to one relationship to a class in an inheritance tree and I'm using class table inheritance in Doctrine. When I try to add a DQL WITH statement on a column in the super class to the join, the generated SQL incorrectly places the statement in the child classes JOIN ON section.

      Here's the DQL:

      SELECT p FROM Fampus_Entity_Photo p LEFT JOIN p.flag f WITH f.status='ok'
      

      Here's the generated SQL:

      SELECT p0_.id AS id0, p0_.name AS name1, p0_.path AS path2, p0_.type AS type3, p0_.reference_id AS reference_id4, p0_.view_count AS view_count5, p0_.created AS created6, p0_.modified AS modified7, p0_.event_id AS event_id8, p0_.user_id AS user_id9, p0_.school_id AS school_id10, p0_.flag_id AS flag_id11 FROM photos p0_ LEFT JOIN flaggedcontent_photo f1_ ON p0_.flag_id = f1_.id AND (f2_.status = 'ok') LEFT JOIN flaggedcontent f2_ ON f1_.id = f2_.id
      

      Note that f2_.status = 'ok' is the correct statement, but it is in the wrong LEFT JOIN ON section.

      Here are my entities (significantly clipped):

      /**
       * @Table(name="photos")
      */
      class Photo {
          /**
           * @OneToOne(targetEntity="FlaggedContent_Photo", mappedBy="photo")
           * @JoinColumn(nullable=true)
           */
          protected $flag;
      }
      
      /**
       * @DiscriminatorColumn(name="type")
       * @DiscriminatorMap({
       *     "photo" = "FlaggedContent_Photo"
       * })
       * @InheritanceType("JOINED")
       * @Table(name="flaggedcontent")
      */
      abstract class FlaggedContent {
          /**
           * Database identifier
           *
           * @Id
           * @Column(type="integer")
           * @GeneratedValue(strategy="AUTO")
           */
          protected $id;
      
          /**
           * Status
           *
           * @Column(type="text")
           * @var string
           */
          protected $status;
      }
      
      /**
       * @Entity
       * @Table(name="flaggedcontent_photo")
       */
      class FlaggedContent_Photo extends FlaggedContent {
          /**
           * @OneToOne(targetEntity="Photo", inversedBy="flag")
           */
          protected $photo;
      }
      

        Issue Links

          Activity

          Justin Hendrickson created issue -
          Benjamin Eberlei made changes -
          Field Original Value New Value
          Description I've created an entity that has a one to one relationship to a class in an inheritance tree and I'm using class table inheritance in Doctrine. When I try to add a DQL WITH statement on a column in the super class to the join, the generated SQL incorrectly places the statement in the child classes JOIN ON section.

          Here's the DQL:
          SELECT p FROM Fampus_Entity_Photo p LEFT JOIN p.flag f WITH f.status='ok'

          Here's the generated SQL:
          SELECT p0_.id AS id0, p0_.name AS name1, p0_.path AS path2, p0_.type AS type3, p0_.reference_id AS reference_id4, p0_.view_count AS view_count5, p0_.created AS created6, p0_.modified AS modified7, p0_.event_id AS event_id8, p0_.user_id AS user_id9, p0_.school_id AS school_id10, p0_.flag_id AS flag_id11 FROM photos p0_ LEFT JOIN flaggedcontent_photo f1_ ON p0_.flag_id = f1_.id AND (f2_.status = 'ok') LEFT JOIN flaggedcontent f2_ ON f1_.id = f2_.id

          Note that f2_.status = 'ok' is the correct statement, but it is in the wrong LEFT JOIN ON section.

          Here are my entities (significantly clipped):

          /**
           * @Table(name="photos")
          */
          class Photo {
              /**
               * @OneToOne(targetEntity="FlaggedContent_Photo", mappedBy="photo")
               * @JoinColumn(nullable=true)
               */
              protected $flag;
          }

          /**
           * @DiscriminatorColumn(name="type")
           * @DiscriminatorMap({
           * "photo" = "FlaggedContent_Photo"
           * })
           * @InheritanceType("JOINED")
           * @Table(name="flaggedcontent")
          */
          abstract class FlaggedContent {
              /**
               * Database identifier
               *
               * @Id
               * @Column(type="integer")
               * @GeneratedValue(strategy="AUTO")
               */
              protected $id;

              /**
               * Status
               *
               * @Column(type="text")
               * @var string
               */
              protected $status;
          }

          /**
           * @Entity
           * @Table(name="flaggedcontent_photo")
           */
          class FlaggedContent_Photo extends FlaggedContent {
              /**
               * @OneToOne(targetEntity="Photo", inversedBy="flag")
               */
              protected $photo;
          }
          I've created an entity that has a one to one relationship to a class in an inheritance tree and I'm using class table inheritance in Doctrine. When I try to add a DQL WITH statement on a column in the super class to the join, the generated SQL incorrectly places the statement in the child classes JOIN ON section.

          Here's the DQL:

          {code}
          SELECT p FROM Fampus_Entity_Photo p LEFT JOIN p.flag f WITH f.status='ok'
          {code}

          Here's the generated SQL:

          {code}
          SELECT p0_.id AS id0, p0_.name AS name1, p0_.path AS path2, p0_.type AS type3, p0_.reference_id AS reference_id4, p0_.view_count AS view_count5, p0_.created AS created6, p0_.modified AS modified7, p0_.event_id AS event_id8, p0_.user_id AS user_id9, p0_.school_id AS school_id10, p0_.flag_id AS flag_id11 FROM photos p0_ LEFT JOIN flaggedcontent_photo f1_ ON p0_.flag_id = f1_.id AND (f2_.status = 'ok') LEFT JOIN flaggedcontent f2_ ON f1_.id = f2_.id
          {code}

          Note that f2_.status = 'ok' is the correct statement, but it is in the wrong LEFT JOIN ON section.

          Here are my entities (significantly clipped):

          {code}
          /**
           * @Table(name="photos")
          */
          class Photo {
              /**
               * @OneToOne(targetEntity="FlaggedContent_Photo", mappedBy="photo")
               * @JoinColumn(nullable=true)
               */
              protected $flag;
          }

          /**
           * @DiscriminatorColumn(name="type")
           * @DiscriminatorMap({
           * "photo" = "FlaggedContent_Photo"
           * })
           * @InheritanceType("JOINED")
           * @Table(name="flaggedcontent")
          */
          abstract class FlaggedContent {
              /**
               * Database identifier
               *
               * @Id
               * @Column(type="integer")
               * @GeneratedValue(strategy="AUTO")
               */
              protected $id;

              /**
               * Status
               *
               * @Column(type="text")
               * @var string
               */
              protected $status;
          }

          /**
           * @Entity
           * @Table(name="flaggedcontent_photo")
           */
          class FlaggedContent_Photo extends FlaggedContent {
              /**
               * @OneToOne(targetEntity="Photo", inversedBy="flag")
               */
              protected $photo;
          }
          {code}
          Benjamin Eberlei made changes -
          Priority Major [ 3 ] Minor [ 4 ]
          Benjamin Eberlei made changes -
          Workflow jira [ 12797 ] jira-feedback [ 13946 ]
          Benjamin Eberlei made changes -
          Workflow jira-feedback [ 13946 ] jira-feedback2 [ 15810 ]
          Benjamin Eberlei made changes -
          Workflow jira-feedback2 [ 15810 ] jira-feedback3 [ 18066 ]
          Benjamin Eberlei made changes -
          Link This issue is duplicated by DDC-2131 [ DDC-2131 ]
          Igor Bekrenyov made changes -
          Status Open [ 1 ] Awaiting Feedback [ 10000 ]
          Igor Bekrenyov made changes -
          Affects Version/s 2.3 [ 10185 ]
          Benjamin Eberlei made changes -
          Status Awaiting Feedback [ 10000 ] Open [ 1 ]
          Benjamin Eberlei made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Resolution Duplicate [ 3 ]
          Benjamin Eberlei made changes -
          Link This issue duplicates DDC-349 [ DDC-349 ]
          Marco Pivetta made changes -
          Link This issue is referenced by DDC-2869 [ DDC-2869 ]

            People

            • Assignee:
              Benjamin Eberlei
              Reporter:
              Justin Hendrickson
            • Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: