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.


24 thoughts on “Symfony filter forms and related tables

  1. Pingback: Easy list ordering / sorting on any field | Synofony

  2. Pingback: Tweets that mention Symfony filter forms and related tables | Synofony -- Topsy.com

  3. Hi,
    I thinks there’s just an error in your last extract of code.

    There should be :
    $this->embedForm(‘City’, new CityFormFilter());
    instead of :
    $this->embedForm(‘Address’, new CityFormFilter());

    Great job !

  4. Hi all,
    If you are using Propel, the following code might be helpful for you guys

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

    public function addCityColumnQuery($query, $field, $values)
    {
    $cityFilter = new CityFormFilter();
    $c = $cityFilter ->buildCriteria($value);
    $criteria->addJoin(CityPeer::ID, UserPeer::CITY_ID);
    $criteria->mergeWith($c);
    }
    }

    • uuups sorry
      It should be
      class UserFormFilter extends BaseUserFormFilter
      {
      public function configure()
      {
      $this->embedForm(‘City’, new CityFormFilter());
      }

      public function addCityColumnQuery($criteria, $field, $values)
      {
      $cityFilter = new CityFormFilter();
      $c = $cityFilter ->buildCriteria($value);
      $criteria->addJoin(CityPeer::ID, UserPeer::CITY_ID);
      $criteria->mergeWith($c);
      }
      }

  5. You are able to use table joining in the filter:

    public function getFields()
    {
    return array_merge(parent::getFields(), array(‘city_name’ => ‘Text’));
    }

    protected function addCityNameColumnQuery(Doctrine_Query $query, $field, $value)
    {
    $fieldName = $this->getFieldName($field);

    $query->innerJoin(sprintf(‘%s.City re’, $query->getRootAlias()))
    ->addWhere(sprintf(‘re.%s = ?’, $fieldName), $value);
    }

    You should add widget ‘city_name’ and validator ‘city_name’ also

  6. I´ve been trying to use this kind of fileter, but I still don´t manage to make it work. What code should I put on the template (indexSuccess.php) for this to work??

    Regards

    • I’m not sure I quite understand your question. The template on indexSuccess.php is a list of users or users + cities based on the result of the filters.

      The other template just includes a form and each embedded form you might need.

  7. Hi khepin,
    thank you for this, guess this spared me a lot of time. :)
    But for my problem to fit, i would have to include more than one value in my subquery/join at the addColumnQuery and i don’t know how to solve this.
    This is because i have 2 values in my filter (calender week and year), which have to be used for 3 other joins (3 other tables where this values have to be taken into account)
    so, my question here is how to “transport” values into the addColumnQuery function not originating from the filter field and without using an embedded form, which (as is understood) passes an array instead of a single value.
    Know what i mean ? :)

    Thanks in advance…

  8. I just came accross your post, which is great. Although it has been some time since you wrote this article, I hope you still remember bits of it.

    So I am trying to implement a filter on frontend. The filter appears with the fields that I want in a place I want it. But I could not finish implementation due to a problem to wich I could not find an answer in any Symfony forrums or articles.

    The problem: after pressing “Filter” button, the screen is redirected to “new” action and opens a form, together with . I could not figure out why, but I guess it has something to do with how my “indexSuccess.php” is written.

    <form action="" method="post" id="getName() ?>" >

    renderHiddenFields() ?>

    I was wondering if you could provide with example. I would probably could find where did I go wrong.

  9. Hmm… the code part did not appear right
    “<form action="” method=”post” id=”getName() ?>” >

    renderHiddenFields() ?>

  10. Hi – great post. Do you know of a way to combine form filter values with pagination links? I have my form filter working, but when I click a pagination link the whole list shown again (instead of the filtered list).

    • That was a long time ago and the soft is now using a search engine rather than a myriad of filters. I know it’s possible, really don’t remember how exactly.

  11. I don’t know doctrine, so i would write querys in raw SQL. What kind of result should return addCityColumnQuery() functiom? Greetings from Poland ;)

    • This was a long time ago so I don’t remember all details, however if you are not using Doctrine,this wouldn’t be of much interest to you I believe.

      • I am not using, but i’m going to. Unfortunately just now i have a job to do, but i havn’t enough time to learn from the begining (and belive me, i spent a lot of time solving this problem). Your article is very helpful, but i have problem with doctrine. Thanks for your help. I will continue to seek solutions. Pozdrowienia z Polski ;)

  12. Does anyone know how to make use of “knp_paginator” bundle to build the same result for filtering a table with several page?
    I really need it, please assist in this one.

    Thanks a lot

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>