[DDC-1081] Unnecessary JOIN when selecting ManyToMany/Join Table by ID. Created: 27/Mar/11  Updated: 28/Mar/11

Status: Reopened
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.0
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Minor
Reporter: David Reisch Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
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.



 Comments   
Comment by Benjamin Eberlei [ 27/Mar/11 ]

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.

Comment by David Reisch [ 27/Mar/11 ]

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.

Comment by Benjamin Eberlei [ 28/Mar/11 ]

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?

Comment by David Reisch [ 28/Mar/11 ]

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
Comment by Benjamin Eberlei [ 28/Mar/11 ]

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.

Comment by David Reisch [ 28/Mar/11 ]

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.

Comment by Benjamin Eberlei [ 28/Mar/11 ]

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

Comment by David Reisch [ 28/Mar/11 ]

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_

Generated at Mon Sep 01 22:00:00 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.