Skip to content

Loading…

DDC-1081: Unnecessary JOIN when selecting ManyToMany/Join Table by ID. #1675

Closed
doctrinebot opened this Issue · 9 comments

3 participants

@doctrinebot

Jira issue originally created by user putgeminmouth:

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.

@doctrinebot

Comment created by @beberlei:

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.

@doctrinebot

Comment created by putgeminmouth:

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.

@doctrinebot

Comment created by @beberlei:

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?

@doctrinebot

Comment created by putgeminmouth:

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
@doctrinebot

Comment created by @beberlei:

The targetEntity is wrong. I suppose it should be ImageTag or not? If it should be Tag, then you don't need that ImageTag 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.

@doctrinebot

Comment created by putgeminmouth:

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.

@doctrinebot

Comment created by @beberlei:

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

@doctrinebot

Comment created by putgeminmouth:

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
{quote}
[Semantical Error] line 0, col 138 near 'id=:tag ': Error: Class domain\Tag_Image has no field or association named id
{quote}
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

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

@beberlei beberlei was assigned by doctrinebot
@zeroedin-bill
Doctrine member

This appears to have been mostly a mapping/modelling conundrum.

@beberlei beberlei was unassigned by zeroedin-bill
@zeroedin-bill zeroedin-bill self-assigned this
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.