DDC-2076: Optimization for MEMBER OF #2617

New Issue

admin · 2026-01-22T13:58:28+01:00

admin commented

2026-01-22 13:58:28 +01:00

Originally created by @doctrinebot on GitHub (Oct 14, 2012).

Originally assigned to: @beberlei on GitHub.

Jira issue originally created by user stof:

Currently, using MEMBER OF for a ManyToMany collection does a join on the table of the related entity, whereas all it needs is in the join table.

Using the following DQL:

SELECT p FROM Player p
WHERE NOT :team MEMBER OF p.targetedBy

Here is the current generated SQL:

WHERE NOT EXISTS (SELECT 1 FROM player*team p1_ INNER JOIN Team t2_ ON p1_.team_id = t2_.id WHERE p1_.player_id = p0_.id AND t2*.id = ?)

whereas it could drop the join:

WHERE NOT EXISTS (SELECT 1 FROM player*team p1_ WHERE p1_.player_id = p0_.id AND p1_.team*id = ?)

Originally created by @doctrinebot on GitHub (Oct 14, 2012). Originally assigned to: @beberlei on GitHub. Jira issue originally created by user stof: Currently, using MEMBER OF for a ManyToMany collection does a join on the table of the related entity, whereas all it needs is in the join table. Using the following DQL: ``` sql SELECT p FROM Player p WHERE NOT :team MEMBER OF p.targetedBy ``` Here is the current generated SQL: ``` sql WHERE NOT EXISTS (SELECT 1 FROM player*team p1_ INNER JOIN Team t2_ ON p1_.team_id = t2_.id WHERE p1_.player_id = p0_.id AND t2*.id = ?) ``` whereas it could drop the join: ``` sql WHERE NOT EXISTS (SELECT 1 FROM player*team p1_ WHERE p1_.player_id = p0_.id AND p1_.team*id = ?) ```

admin added the Improvement label 2026-01-22 13:58:28 +01:00

admin commented

2026-01-22 13:58:29 +01:00

@doctrinebot commented on GitHub (May 28, 2015):

Comment created by stof:

[~guilhermeblanco] is there any case where the CollectionMemberExpression would really need to join the target entity table rather than just using the join table ? I don't see any

@doctrinebot commented on GitHub (May 28, 2015): Comment created by stof: [~guilhermeblanco] is there any case where the CollectionMemberExpression would really need to join the target entity table rather than just using the join table ? I don't see any

admin commented

2026-01-22 13:58:30 +01:00

@acasademont commented on GitHub (Feb 23, 2017):

This would come in really handy!

Also, shouldn't this syntax work in a similar way?

SELECT p FROM Player p WHERE p.targetedBy IN :team

and automatically joining only the join table.

@acasademont commented on GitHub (Feb 23, 2017): This would come in really handy! Also, shouldn't this syntax work in a similar way? `SELECT p FROM Player p WHERE p.targetedBy IN :team` and automatically joining only the join table.

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#2617