Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1081

Unnecessary JOIN when selecting ManyToMany/Join Table by ID.

    Details

    • Type: Improvement Improvement
    • Status: Reopened
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 2.0
    • Fix Version/s: None
    • Component/s: ORM
    • Security Level: All
    • Labels:
      None

      Description

      With the schema:

       
      Image
          @Id
          $id
      
      Tag
          @Id
          $Id
      
      Tag_Image
          @Id
          @OneToOne(targetEntity="Tag")
          @JoinColumn(name="tag")
          $tag
      
          @Id
          @OneToOne(targetEntity="Image")
          @JoinColumn(name="image")
          $image
      

      Given the following DQL,

          SELECT img
          FROM Image 
          LEFT JOIN img.tags tag
          WHERE tag.id=:tag
      

      Doctrine Generates this SQL

       
          SELECT i0_.id AS id1 
          FROM Image i0_ 
          LEFT JOIN Tag_Image t2_ 
              ON i0_.id = t2_.image 
          LEFT JOIN Tag t1_ 
              ON t1_.id = t2_.tag 
          WHERE t1_.id = 37
      

      Which unncessarily joins against Tag, given that the foreign key Tag.id is also found in Tag_Image.tag.

        Activity

        Hide
        David Reisch added a comment -

        With this change, the original query is invalid:

            LEFT JOIN i.tags t
            WHERE t.id=:tag
        

        Because i.tags of type Tag_Image has no field id

        [Semantical Error] line 0, col 138 near 'id=:tag ': Error: Class domain\Tag_Image has no field or association named id

        I attempt the logical modification:

            LEFT JOIN i.tags t
            WHERE t.tag=:tag
        

        and get

        SELECT i0_.id AS id0
        FROM Image i0_ 
        LEFT JOIN Tag_Image t1_ ON i0_.id = t1_.image 
        LEFT JOIN Tag_Image t1_ ON t1_.id = t1_.tag 
        WHERE t1_.tag = 37
        

        SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 't1_

        Show
        David Reisch added a comment - With this change, the original query is invalid: LEFT JOIN i.tags t WHERE t.id=:tag Because i.tags of type Tag_Image has no field id [Semantical Error] line 0, col 138 near 'id=:tag ': Error: Class domain\Tag_Image has no field or association named id I attempt the logical modification: LEFT JOIN i.tags t WHERE t.tag=:tag and get SELECT i0_.id AS id0 FROM Image i0_ LEFT JOIN Tag_Image t1_ ON i0_.id = t1_.image LEFT JOIN Tag_Image t1_ ON t1_.id = t1_.tag WHERE t1_.tag = 37 SQLSTATE [42000] : Syntax error or access violation: 1066 Not unique table/alias: 't1_
        Hide
        Benjamin Eberlei added a comment -

        If you change the targetEntity to Tag_Image then it might already be enough to get this working without another join.

        Show
        Benjamin Eberlei added a comment - If you change the targetEntity to Tag_Image then it might already be enough to get this working without another join.
        Hide
        David Reisch added a comment -

        No argument on the ticket type...

        Ahh, I store some metadata in Tag_Image, which is why I manage it explicitly.

        In any case thanks for looking at this.

        Show
        David Reisch added a comment - No argument on the ticket type... Ahh, I store some metadata in Tag_Image, which is why I manage it explicitly. In any case thanks for looking at this.
        Hide
        Benjamin Eberlei added a comment -

        The targetEntity is wrong. I suppose it should be Image_Tag or not? If it should be Tag, then you don't need that Image_Tag entity at all.

        In that case i have to check if you can use the shortcut notation, however it will again not work with the left join - only inner. This is an assumption the ORM makes and there is not yet code included for the optimization. This is not a bug, but an improvement ticket. The functionality works.

        Show
        Benjamin Eberlei added a comment - The targetEntity is wrong. I suppose it should be Image_Tag or not? If it should be Tag, then you don't need that Image_Tag entity at all. In that case i have to check if you can use the shortcut notation, however it will again not work with the left join - only inner. This is an assumption the ORM makes and there is not yet code included for the optimization. This is not a bug, but an improvement ticket. The functionality works.
        Hide
        David Reisch added a comment -

        That is correct, thanks for taking another look at this.
        Sorry I had forgotten to include that information.

        Image
            /**
                @Id
            */
            $id
        
            /**
                @ManyToMany(targetEntity="Tag")
                @JoinTable(name="Tag_Image", 
                                    joinColumns={@JoinColumn(name="image")},
                                    inverseJoinColumns={@JoinColumn(name="tag")})
            */
            $tags
        
        Show
        David Reisch added a comment - That is correct, thanks for taking another look at this. Sorry I had forgotten to include that information. Image /** @Id */ $id /** @ManyToMany(targetEntity="Tag") @JoinTable(name="Tag_Image", joinColumns={@JoinColumn(name="image")}, inverseJoinColumns={@JoinColumn(name="tag")}) */ $tags
        Hide
        Benjamin Eberlei added a comment -

        I misread the mappings, sorry, i though its a @OneToOne but its actually an assocition entity with @OneToOnes.

        Can you show me the Image::$tags mapping also?

        Show
        Benjamin Eberlei added a comment - I misread the mappings, sorry, i though its a @OneToOne but its actually an assocition entity with @OneToOnes. Can you show me the Image::$tags mapping also?
        Hide
        David Reisch added a comment -

        There is no owning isde of the association, you can clearly see there is an association table/entity.

        I can't understand how this behavior is expected. If no properties of Tag are selected for, there is no need to join against Tag since the id is already available via the association table.

        Show
        David Reisch added a comment - There is no owning isde of the association, you can clearly see there is an association table/entity. I can't understand how this behavior is expected. If no properties of Tag are selected for, there is no need to join against Tag since the id is already available via the association table.
        Hide
        Benjamin Eberlei added a comment -

        This is not a bug, but expected behavior.

        You can select against the alias if its on the owning side of the association:

        SELECT img
        FROM Image img 
        WHERE img.tag=:tag
        

        In this case it is not a left join though, if you want a left join you HAVE to join.

        Show
        Benjamin Eberlei added a comment - This is not a bug, but expected behavior. You can select against the alias if its on the owning side of the association: SELECT img FROM Image img WHERE img.tag=:tag In this case it is not a left join though, if you want a left join you HAVE to join.

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            David Reisch
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: