POSTS

Easy List Ordering Sorting on Any Field

How to sort lists on any field even the one of joined tables?

This post is almost a followup to the previous one on filtering with joined tables. But what we want to do here is to add the ability to sort a list of objects (based on model objects in the database) and to add it conveniently to any action that returns a list.

The idea is that we would still use the same model:

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)

And on the resulting list we would like to be able to sort the list on any field (by the city’s post code or by state etc…) and not only on the fields directly related to the user table, just like in this picture:

We will be passing two parameters to the action that gave us this list as a query string. The two parameters we need are called “sort” and “sort_type”, which is a direct copy of what is done in the admin autogenerated actions. “sort” will be used to give a field name on which you want to sort (eg: name) and “sort_type” will tell us if we sort from A to Z or Z to A (will be either asc or desc).

The first thing I’m going to do in the index action that returned this list is store the new ordering parameters.

public function executeSearch(sfWebRequest $request)
{
  //sorting
  if ($request->getParameter('sort'))
  {
     $this->setSort(array(
         'sort'  =>  $request->getParameter('sort'),
         'sort_type'  =>  $request->getParameter('sort_type')
     ));
  }
  $this->sortType = $this->getSortType();

I call to a method setSort of my actions class and tell about my new sorting parameters. The getSortType() method will return the opposite of the current sort_type (desc if I currently have asc) or give me a default sort_type. This way in my template I can add the correct sorting type for my query string. In my own case, there is only a global sort_type. This means that if you sorted on the user name by ascendant order, then whatever field you sort on afterward, it will be by descendant order. I don’t see this as being a real issue for the users, but you could easily change this if needed.

As I’m going to sort on many different lists of different modules, the setSort(), getSortType() etc… methods are all put into a custom action class that inherits from the standard symfony actions class:

class MyActions extends sfActions
{
   public function getPager($className, Doctrine_Query $query, $page)
   { ... }

   public function getSort()
   { ... }

   public function setSort()
   { ... }

   public function getSortType()
   { ... }

   public function addSortQuery(Doctrine_Query $query)
   { ... }
}

As you can see I already have a pager method here to which I give a query that has been pre-built in the index action. The pager method will do the following:

  1. Finish building the query by adding the number of item limit
  2. Finish building the query by adding the sort query (through the addSortQuery()) method
  3. Return a pager based on that query and set at the current page.

The getSort and setSort methods are used to store the sorting parameters in the session and retrieve them. This way, if I change my pager’s page, my sorting and ordering of the table is still valid.

public function getPager($className,$query,$page)
{
   $pager = new sfDoctrinePager($className, sfConfig::get('app_max_per_page'));
   $this->addSortQuery($query);
   $pager->setQuery($query);
   $pager->setPage($page);
   $pager->init();
   return $pager;
}

public function getSort()
{
   return $this->getUser()->getAttribute('sort', null, $this->getModuleName());
}

public function setSort($sort)
{
   $this->getUser()->setAttribute('sort', $sort, $this->getModuleName());
}

public function getSortType()
{
   $sort = $this->getSort();
   if($sort['sort_type'] == 'asc')
   {
      return 'desc';
   }
   return 'asc';
}

If you are using filters, the getPager() method would be a good place too make a call to some addFiltersQuery() method from this action too. Storing the filter values in session (much like the sort values here) would also allow you to browse through pages while keeping your ordering and sorting correct.

When we set and get the sort values in the session, the third parameter, which is the namespace is used with the module’s name as a namespace. Because usually I only have one action (index) for which I have a list that I want to sort on in each module. And because when we go from the user list to let’s say a product list, we don’t want to get an error telling us we can’t sort products by city, so we need to separate those sort values depending on which module / action we are browsing.

Before looking at the code of the addSortQuery(), we need to see the templates and how the query string will be built.

<table>
  <thead>
    <tr>
      <th><a href="<?php echo url_for('@user').'?sort=last_name&sort_type='.$sortType ?>">LastName</a></th>
      <th><a href="<?php echo url_for('@user').'?sort=first_name&sort_type='.$sortType ?>">FirstName</a></th>
      <th><a href="<?php echo url_for('@user').'?sort=email&sort_type='.$sortType ?>">Email</a></th>
      <th><a href="<?php echo url_for('@user').'?sort=City.name&sort_type='.$sortType ?>">City</a></th>
      <th><a href="<?php echo url_for('@user').'?sort=City.post_code&sort_type='.$sortType ?>">Zip</a></th>
      <th><a href="<?php echo url_for('@user').'?sort=City.state&sort_type='.$sortType ?>">State</a></th>

For fields belonging to the user table (last_name, first_name, email) the value of “sort” is the field’s name in the database (as defined in the schema). For fields that belong to the city table, we first use the name of the relation we defined from User to City, then the name of the field in the city model.

The addSortQuery() method will parse the relations and add the corresponding table to the query. Here we only have one level of relation, however it could be that we have many more, for example if we had a column in our list to show the city the user is working in, it could be 4 relations away from the user: User -> Company -> Address -> City. In that case our sort would be: Company.Address.City.name. I have tested my code to go up to this many level of relations without issues. To do this, the addSortQuery() reads like this:

public function addSortQuery(Doctrine_Query $query)
{
   if(null !== $sort = $this->getSort())
   {
      // If sorting on a relation, the sort column looks like Address.City.name
      $relations = explode('.', $sort['sort']);
      // Get the root query alias
      $alias = $query->getRootAlias();
      // For each level of relation
      for($i = 0; $i < count($relations) - 1; $i++)
      {
         // Join the related table
         $query->leftJoin($alias.'.'.$relations[$i].' '.$relations[$i]);
         // and change the alias to the one for the table we just joined in order to prepare the next join
         $alias = $relations[$i];
      }
      // The last alias is the one to be used with the field name
      $query->addOrderBy($alias.'.'.$relations[count($relations)-1].' '.$sort['sort_type']);
   }
}

Let’s see what happens in the case we sort by City.post_code:

  1. Check that we do have something to sort on, otherwise, we don’t do anything
  2. We split the sort column name on each ‘.’ found in the string: { City, post_code}
  3. Get the root alias of the query (since it’s on user, lets say it’s ‘u’)
  4. We go through all except the last element of the previously exploded array (so only City in this case)
  5. Do a leftJoin(‘u.City City’)
  6. And change the current alias to City instead of ‘u’
  7. addOrderBy(‘City.post_code asc’)

Like I said, I have successfully used it on many levels of relations without any issues. You now have a Symfony actions class that allows sorting your lists in very few lines of code.

Finally, there’s one line that is in the indexAction code and that I would like to have in the code for the setSort method:

$this->sortType = $this->getSortType();

However having this in the MyAction class code did not make the $sortType variable available to the template. I need to investigate a bit more on this.