Many to many search with doctrine2 and symfony2

My last post was about how to search for a many to one entity where I needed to check the values of two attributes by using "exists". I couldn't find any other "clean" ORM/DQL query to do this.

While experimenting with many to many relations I encountered how to easily retrieve all users that are associated with a certain work category. Many to many relations in doctrine create a relational table between two entities, which is no entity itself. You cannot use the table to create joins. I'd like to cite the doctrine2 orm reference:

"Why are many-to-many associations less common? Because frequently you want to associate additional attributes with an association, in which case you introduce an association class. Consequently, the direct many-to-many association disappears and is replaced by one-to-many/many-to-one associations between the 3 participating classes."

The association class is your third entity which you can use to create your search queries using JOINs for example.

In this case we have a pure many to many relationship. I built a form with an element of type entity which represents the work categories.

As this draws a multiselect control with the ID of the work categories as value on submitting the form, you have access to them in your search query. The key is to use "MEMBER OF" then:


Surprisingly for me, this creates a very similar query as I used for the one-to-many search query:

AND EXISTS (SELECT 1 FROM users_workcategories u2_ INNER JOIN user_work_category u3_ ON u2_.workcategory_id = u3_.id WHERE u2_.user_id = u0_.id AND u3_.id = ?) AND EXISTS (SELECT 1 FROM promoters_workcategories u2_ INNER JOIN user_work_category u3_ ON u2_.workcategory_id = u3_.id WHERE u2_.user_id = u0_.id AND u3_.id = ?)

Doctrine is aware of of the third relational table and uses it for JOINs to assemble the native SQL query that looks far more complicated than the DQL string. For people not familiar with advanced SQL queries, this is a live safer. On the other hand, SQL people surely need to be argued into this new approach.