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

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


 Description   

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

Generated at Tue Nov 25 23:35:00 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.