2010S

Symfony Filter Forms and Related Tables

This is a problem that many people seem to have: they see the filters used in the admin backend generated by Symfony and think “wow how great is that!!??!!”. However there is little to no documentation on how to use those filters if you want to implement them customized and tuned for your own needs in a frontend environment.

In this post I’ll explain how I have used the Symfony filter forms, and hope that you will find valuable information on how you could start using them yourself.

The problem:

My problem was the following, imagine this schema:

User:
  columns:
    name:         text(100)
    first_name:   text(100)
    email:        text(255)
    city_id:      integer
  relations:
    City:         ~

City:
  columns:
    name:         text(100)
    post_code:    text(20)
    state:        text(100)

Now what we would like to do is that when we show the user list we want to be able to filter that list. The basic use of filters would provide us with filters like the ones created for the admin modules. However we will try to go further than this.

The problem with filters like they are in the admin modules is that I am restricted to filter on one table only. I could only filter on the fields of the user table. Yes this includes the city_id and I would have a dropdown list with the city name to chose from. However what happens if I only know the postcode of the city and want to use this as a search criteria to find users in that city?

What we would like to get is something like this picture:

In this picture, if I want to filter those users by state and not by city, I can just type the state name in the appropriate field. The state field belongs to the city table, not to the user table on which our primary filter is going to be.

Now let’s see how to do this!

The basic filter (User table):

In the action of the user module, we want to add the filter form object as a class variable so that the view will have access to it and can display the filter. As you will see, those filters are called FormFilter for a reason which is that they are based on forms and behave a lot like the standard forms.

public function executeIndex(sfWebRequest $request)
{
   $this->filter = new UserFormFilter();
   $this->users = $this->getRoute()->getObjects();
}

We will also add a filter action to receive the parameters from the filter field. Ideally you might want this action to just store the filter values in a session variable. Then in the index you could build the complete query based on the filter values, maybe some ordering values (to set the ORDER BY part of your query) and even the pager. The automatic actions of the admin module created by symfony will show you a good example of how to mix those 3 concepts.

For now, we only add a filter action like this:

public function executeFilter(sfWebRequest $request)
{
   $this->filter = new UserFormFilter();
   $this->filter->bind($request->getParameter($this->filter->getName()));
   if($this->filter->isValid())
   {
      $this->users= $this->filter->getQuery()->execute();
   }
   $this->setTemplate('index');
}

This looks very much like the processForm method of the autogenerated frontend symfony modules:

  1. Create a form object
  2. Bind it with the values we received from the request
  3. Check that the values are valid (based on the validators)
  4. Build a query (magically done by the FilterForm based on the given values)
  5. Execute that query to get the users
  6. We use the same template as the one for the index

The index template displays the form filter and the list of users.

If all you needed was the basic 1 table filter, you’re done already! Filters are based on the symfony forms so it you need more customization like setting which fields should or not be shown, which values are to be accepted etc… Then you can directly edit the filters in /lib/filters just like you would edit a normal form class: set some widgets, unset others, add validators …

If you need more than just this and want to filter values on related tables, keep reading!

Advanced filters:

If like me you needed something more complex. Symfony doesn’t build the query including the embedded forms, so we need to take care of that ourselves. However this is still quite simple. The user form now needs to embed the one for the city. Since I will only always display my form in this format, I did this through the configure method of the UserFormFilter:

class UserFormFilter extends BaseUserFormFilter
{
   public function configure()
   {
      $this->embedForm('City', new CityFormFilter());
      ....
   }

Your form now contains an embedded form named City, so all the parameters and values for this form will be in a sub array of the values we get back from the user having the array key: City.

When the form filter method “getQuery” is called, it itself calls the buildQuery method which in turns calls the doBuildQuery method. This is almost the place where the stuff is actually done! For each field, the doBuildQuery method will attemt to call a method like “add#field_name#ColumnQuery”. Since we got our embedded City form in a sub array, the ‘City’ sub array will also trigger the method “addCityColumnQuery”, that’s where we will do our customizations.

A form filter can be set a base query on which it will add all the filtering “where” conditions. So my first thought was to leftJoin the City on the user:

$userQuery->leftJoin('u.City c');

And use this as my base query. However the “buildQuery” method will not use the ‘c’ alias but will prefix all the city fields with ‘u’, thinking those are fields of the same base table as the one in the user query. I couldn’t find a way to change the alias that is taken into account here (this would require changing Symfony files which would not be good for the next symfony update) and therefore had to find another solution.

The final solution is to have a subquery that will filter on the cities, and check that the user.city_id is in the results of the subquery.

class UserFormFilter extends BaseUserFormFilter
{
   public function configure()
   {
      $this->embedForm('Address', new CityFormFilter());
   }

   public function addCityColumnQuery($query, $field, $values)
   {
      // we initiate the query to only select the ids
      $q = new Doctrine_Query();
      $q->select('c.id')->from('City c');

      // get the embedded form
      $cityForm = $this->getEmbeddedForm('City');

      // set the initial query we just created
      $cityForm ->setQuery($q);

      // get the filtering query
      $q= $cityForm ->buildQuery($values);

      // I will explain that one more just after
      $params = $q->getParams();

      // add the subquery to our initial query
      $query->andWhere('u.city_id IN ('.$q->getDql().')', $params['where'] );
   }

As I said we add a WHERE … IN clause with a subquery inside the IN, the Doctrine queries however when we output them like this will still contain ‘?’ instead of parameter values, but the parameters are nowhere to be found, this is why we need to get the query parameters involved in the WHERE clause of this subquery and put them here.

Conclusion

Well it took me a damn lot of time to eliminate all the other solutions I have tried, so I hope this one helps! Finally, this “article” might not be perfectly clear for everyone, so if some points are still not clear, I’d be pleased to edit it for more people to use it.