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.

Search form using symfony2 and doctrine2

This post is about creating a search form and using the doctrine2 query builder to dynamic built the query. You need an object you can bind the form values to. I used the symfony2 entity generator to build my heavy search form data holder really quickly. Let it create an empty repository class for you, which will hold the search function later. Use the symfony2 form generator to create a form based on the entity. As this entity won't be saved in the database the doctrine annotations are not needed. I kept them for later use and just removed the "Entity" annotation.

Next thing you do is writing a template for your form and to specify some validation rules. My controller action which receives the search POST looks like this:



We need to implement the findBySearchCriteria function in the empty repository class of the user entity. This function returns a query builder object which we can do everything we want with.

The following examples show what is possible with the doctrine2 query builder in the search function.

Many-to-one search

The user has a languages attribute which is of type Language. The entity Language has three attributes:

1. a language name (e.g. english, french, etc)
2. points that define how good the language is spoken by the user (10 very good, 1 very bad)
3. a back reference to the user

We want to find users that marked their english knowledge at least with 5 points and their french with at least 7 points. To accomplish this, we need to check, if the user has an language entry which fulfills the given criteria. We check, if such a language entry exists.

As "exists" in the query builder expects a dql subquery, we give it one.

"u" is the alias for the User class of the main query builder. The same method is used for the french language points.

The resulting DQL is:

SELECT u, l FROM Acme\UserBundle\Entity\User u WHERE (EXISTS( SELECT langE FROM AcmeUserBundle:Language langE WHERE langE.user = u AND langE.language = :langName AND langE.points BETWEEN 10 AND :points))

Nested AND / OR queries

Next requirement is that the zip code of the user entity has to lie in a given range of zip codes. The user who defines the search criteria is able to provide a list of zip code ranges. The format for a range is "xxxxx-xxxxx", so a zip code consists of five digits.

The zip code range search query needs to be built using OR. The user's zip code can lie in one range OR another. Native SQL could look like this:

WHERE (zipCode BETWEEN :zipCodeFrom1 AND :zipCodeTo1) OR (zipCode BETWEEN :zipCodeFrom2 AND :zipCodeTo2)

To combine this with the language query, the OR part has to be nested within an AND part:

WHERE ((zipCode BETWEEN :zipCodeFrom1 AND :zipCodeTo1) OR (zipCode BETWEEN :zipCodeFrom2 AND :zipCodeTo2)) AND (EXISTS( SELECT .... 

To accomplish the OR nesting you can use add() on the orx expression. The following example shows how to do that:


This results in a really nice nested query like this:

SELECT u FROM Acme\UserBundle\Entity\User u WHERE ((u.zipCode BETWEEN :zipFrom1 AND :zipTo1) OR (u.zipCode BETWEEN :zipFrom2 AND :zipTo2)) AND (EXISTS( SELECT langE FROM AcmeUserBundle:Language langE WHERE langE.user = u AND langE.language = :langName AND langE.points BETWEEN 10 AND :points))

Building nested queries is really easy. It took me some time to find out that I can use add() on the orx expression to dynamically add parts to it.