doctrine/archived-orm
1
0
Fork 0
mirror of https://github.com/doctrine/orm.git synced 2026-03-23 22:42:18 +01:00
Code Issues 5.3k Packages Projects Releases 100 Wiki Activity

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

New Issue
Closed
opened 2026-01-22 13:11:26 +01:00 by admin · 14 comments
admin commented 2026-01-22 13:11:26 +01:00
Owner
Copy Link

Originally created by @doctrinebot on GitHub (Mar 27, 2011).

Originally assigned to: @billschaller on GitHub.

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.

Originally created by @doctrinebot on GitHub (Mar 27, 2011). Originally assigned to: @billschaller on GitHub. 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.
admin added the Improvement label 2026-01-22 13:11:26 +01:00
admin closed this issue 2026-01-22 13:11:27 +01:00
admin commented 2026-01-22 13:11:28 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Mar 27, 2011):

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 commented on GitHub (Mar 27, 2011): 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.
admin commented 2026-01-22 13:11:29 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Mar 27, 2011):

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 commented on GitHub (Mar 27, 2011): 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.
admin commented 2026-01-22 13:11:30 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Mar 28, 2011):

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 commented on GitHub (Mar 28, 2011): 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?
admin commented 2026-01-22 13:11:30 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Mar 28, 2011):

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 commented on GitHub (Mar 28, 2011): 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 ```
admin commented 2026-01-22 13:11:31 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Mar 28, 2011):

Comment created by @beberlei:

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.

@doctrinebot commented on GitHub (Mar 28, 2011): Comment created by @beberlei: 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.
admin commented 2026-01-22 13:11:32 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Mar 28, 2011):

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 commented on GitHub (Mar 28, 2011): 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.
admin commented 2026-01-22 13:11:33 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Mar 28, 2011):

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 commented on GitHub (Mar 28, 2011): 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.
admin commented 2026-01-22 13:11:33 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Mar 28, 2011):

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}

@doctrinebot commented on GitHub (Mar 28, 2011): 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}
admin commented 2026-01-22 13:11:34 +01:00
Author
Owner
Copy Link

@billschaller commented on GitHub (Jan 6, 2016):

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

@billschaller commented on GitHub (Jan 6, 2016): This appears to have been mostly a mapping/modelling conundrum.
admin commented 2026-01-22 13:11:34 +01:00
Author
Owner
Copy Link

@maximivanov commented on GitHub (May 13, 2018):

I do also see an unnecessary JOIN with many-to-many mapping. Quick example:

Post.php:

/**
 * @ORM\ManyToMany(targetEntity="Tag", inversedBy="posts")
 * @ORM\JoinTable(name="post_tag")
 */
private $tags;

Tag.php:

/**
 * @ORM\ManyToMany(targetEntity="Post", mappedBy="tags")
 */
private $posts;

Fetching with query builder:

$qb = $this->createQueryBuilder('p')
           ->select('p.id')
           ->join('p.tags', 't')
           ->andWhere('t.id = :tagId')
           ->setParameter('tagId', $tagId);

Results in following SQL:

SELECT 
  p0_.id AS id_0 
FROM 
  posts p0_ 
  INNER JOIN post_tag p2_ ON p0_.id = p2_.post_id 
  INNER JOIN tags t1_ ON t1_.id = p2_.tag_id 
WHERE 
  t1_.id = ?

While I would expect query without an extra JOIN:

SELECT 
  p0_.id AS id_0 
FROM 
  posts p0_ 
  INNER JOIN post_tag p2_ ON p0_.id = p2_.post_id 
WHERE 
  p2_.tag_id = ?

Is there a way to instruct doctrine to do so?

@maximivanov commented on GitHub (May 13, 2018): I do also see an unnecessary JOIN with many-to-many mapping. Quick example: Post.php: ``` /** * @ORM\ManyToMany(targetEntity="Tag", inversedBy="posts") * @ORM\JoinTable(name="post_tag") */ private $tags; ``` Tag.php: ``` /** * @ORM\ManyToMany(targetEntity="Post", mappedBy="tags") */ private $posts; ``` Fetching with query builder: ``` $qb = $this->createQueryBuilder('p') ->select('p.id') ->join('p.tags', 't') ->andWhere('t.id = :tagId') ->setParameter('tagId', $tagId); ``` Results in following SQL: ``` SELECT p0_.id AS id_0 FROM posts p0_ INNER JOIN post_tag p2_ ON p0_.id = p2_.post_id INNER JOIN tags t1_ ON t1_.id = p2_.tag_id WHERE t1_.id = ? ``` While I would expect query without an extra JOIN: ``` SELECT p0_.id AS id_0 FROM posts p0_ INNER JOIN post_tag p2_ ON p0_.id = p2_.post_id WHERE p2_.tag_id = ? ``` Is there a way to instruct doctrine to do so?
admin commented 2026-01-22 13:11:34 +01:00
Author
Owner
Copy Link

@Ocramius commented on GitHub (May 13, 2018):

Is there a way to instruct doctrine to do so?

Not really, but if you are optimising an SQL query, just keep it in SQL?

@Ocramius commented on GitHub (May 13, 2018): > Is there a way to instruct doctrine to do so? Not really, but if you are optimising an SQL query, just keep it in SQL?
admin commented 2026-01-22 13:11:34 +01:00
Author
Owner
Copy Link

@maximivanov commented on GitHub (May 13, 2018):

Keeping plain SQL is a possible solution but not really convenient. I simplified query here to keep example short. In project I'm fetching entities, not just fields.

@maximivanov commented on GitHub (May 13, 2018): Keeping plain SQL is a possible solution but not really convenient. I simplified query here to keep example short. In project I'm fetching entities, not just fields.
admin commented 2026-01-22 13:11:35 +01:00
Author
Owner
Copy Link

@kiler129 commented on GitHub (Jan 24, 2019):

It will be really good to be able to not join resulting table but just the m2m table. Is there any way @Ocramius to do that without going to native SQL (and hardcoding column names)?

@kiler129 commented on GitHub (Jan 24, 2019): It will be really good to be able to not join resulting table but just the m2m table. Is there any way @Ocramius to do that without going to native SQL (and hardcoding column names)?
admin commented 2026-01-22 13:11:35 +01:00
Author
Owner
Copy Link

@Ocramius commented on GitHub (Feb 3, 2019):

What's the big holdup with getting your hands dirty where needed? It's not like your schema will change each week, and if this can be improved in a demonstrable efficient way, then it should be a new issue. This has been closed for years now.

@Ocramius commented on GitHub (Feb 3, 2019): What's the big holdup with getting your hands dirty where needed? It's not like your schema will change each week, and if this can be improved in a demonstrable efficient way, then it should be a new issue. This has been closed for years now.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
BC Break
Bug
Can't Fix
Deprecation
Documentation
DQL
Duplicate
Failing Test
Hacktoberfest
Improvement
Improvement
Incomplete
Invalid
Missing Tests
New Feature
PR Created
pull-request
Mirrored from GitHub Pull Request
 Question
Regression
Status: Waiting feedback
Waiting feedback
WIP
Won't Fix
No Label Improvement
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
admin
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: doctrine/archived-orm#1354
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?