[DDC-2076] Optimization for MEMBER OF Created: 14/Oct/12  Updated: 28/May/15

Status: Open
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: Git Master
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Minor
Reporter: Christophe Coevoet Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dql


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 = ?)

Comment by Christophe Coevoet [ 28/May/15 ]

Guilherme Blanco 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

Generated at Mon Nov 30 15:46:11 EST 2015 using JIRA 6.4.10#64025-sha1:5b8b74079161cd76a20ab66dda52747ee6701bd6.