Development

HowtoSortAdminGeneratorListByForeignTableName

You must first sign up to be able to contribute.

Version 7 (modified by newacct, 8 years ago)
--

Your project contains a table of events. You want to manage these events with the admin generator. Your schema looks like this:

  event:
    id:
    name: varchar(100)
    venue_id:
    studio_id:
    instructor_id:

  venue:
    id:
    name: varchar(100)
    # Other stuff

  studio:
    id:
    name: varchar(100)
    # Other stuff

  instructor:
    id:
    name: varchar(100)
    # Other stuff

For the most part, the admin generator does a good job with this. But sorting is a problem in two places: in the actual paginated list of records, and in the dropdown menus used to filter by studio, instructor, etc. I'll present solutions for both issues. The first required the creation of a plugin, but fortunately it's finished and available for you to use. The second simply required digging deeply into undocumented Symfony code to figure out the right way to enable the behavior via generator.yml.

Alphabetically Sorting the Paginated List of Records by a Foreign Key

When the user clicks on the "Name" column, the list is sorted alphabetically by name. But when they click on the "Venue," "Studio," or "Instructor" column, the list is sorted by foreign ID- an order that doesn't make much sense to human beings (although it does usually result in sorting by order of creation, which is sometimes useful). There are two ways to fix this.

The Easy Way: Use sfAdminSortByForeignNamePlugin

The easy fix is to use my sfAdminSortByForeignNamePlugin. Once you have installed that plugin, you can automatically add support for sorting by foreign table name fields to any admin generator class by adding just a few lines to event/actions/actions.class.php (substitute your admin-generated class for event):

protected function addSortCriteria($c)
{
  if (sfAdminSortByForeignName::addSortByForeignName($c, "Event"))
  {
    return;
  }
  parent::addSortCriteria($c);
}

Note that the name of the model class being administered must be passed as the second argument to addSortByForeignName.

The Hard Way (How The Plugin Works)

That's all you really need to know. However, interested parties and those whose needs are similar but not identical to this scenario will want to read on to learn how this works.

The admin generator chapter of the Symfony book contains a clue: it refers to the addSortCriteria() method. Overriding this method in our own modules/event/actions/actions.class.php file allows us to change the way the sort order is handled.

We could do it like this:

  protected function addSortCriteria($c)
  {
    $sort_column = $this->getUser()->getAttribute('sort', null, 'sf_admin/event/sort');
    if ($sort_column == 'venue_id')
    {
      $c->addJoin(EventPeer::VENUE_ID,
        VenuePeer::ID, Criteria::LEFT_JOIN);
      if ($this->getUser()->getAttribute('type', null, 'sf_admin/event/sort') == 'asc')
      {
        $c->addAscendingOrderByColumn(VenuePeer::NAME);
      }
      else
      {
        $c->addDescendingOrderByColumn(VenuePeer::NAME);
      }
    }
    elseif ($sort_column == 'studio_id')
    {
      # Same exact code with different class names, etc.
      # Then do it again for instructor_id, etc. etc. etc.
    {
      # Don't forget to invoke the original version of the method
      # if it's not one of our special cases! Otherwise we can't
      # sort by name anymore
      parent::addSortCriteria($c);
    }
  }

This code is easy to understand. However, it is also tedious and bug-prone because we have to repeat the code for each column.

A better solution that works for all foreign key columns, provided that that the foreign class has a field called "name," is presented here:

  protected function addSortCriteria($c)
  {
    $sort_column = $this->getUser()->getAttribute('sort', null, 'sf_admin/event/sort');
    if (preg_match("/^(\w+)_id$/", $sort_column, $matches))
    {
      $foreignKey = $matches[0];
      $type = $matches[1];
      $type = ucfirst($type);
      $peer = $type . "Peer";
      $c->addJoin(constant("EventPeer::" . strtoupper($foreignKey)),
        constant("$peer::ID"), Criteria::LEFT_JOIN);
      if ($this->getUser()->getAttribute('type', null, 'sf_admin/event/sort') == 'asc')
      {
        $c->addAscendingOrderByColumn(constant("$peer::NAME"));
      }
      else
      {
        $c->addDescendingOrderByColumn(constant("$peer::NAME"));
      }
    }
    else
    {
      parent::addSortCriteria($c);
    }
  }

Here we use a regular expression to detect sort column names of the form "classname_id," extract the class name, and use PHP's constant() function to get the names of the appropriate Propel constants for this class.

By avoiding explicit reference to any particular class we arrive at a solution that works for all types, as long as they have a name field. With a little more work we can double-check that the name field actually exists and leave the column alone if it does not.

An even better solution: migrate this code into a separate class where it can be reused by many admin generator subclasses. And that is what I've done with sfAdminSortByForeignNamePlugin.

Alphabetically Sorting the Filter Dropdown Menus by Foreign Key

I was happy to have a fix for the main problem, but eventually noticed a secondary issue as well. Symfony's admin generator allows us to easily filter the displayed records by any field, including foreign key fields. And it is smart enough to display the names of the foreign objects if the field is a foreign key.

Unfortunately, however, there was no immediately obvious way to sort the list of filter options in the dropdown menu... alphabetically or otherwise.

As it turns out, there is a supported way to fix this in generator.yml. It just isn't well-documented (or indeed mentioned at all in the admin generator chapter of the book, as far as I can tell, for either Symfony 1.0 or 1.2).

All you have to do is write a static public function in your foreign model peer class that returns an array containing all of the objects of that class, in the order you want them. For instance, I use this method in my BandPeer.class.php file:

  public static function getAll()
  {
    $c = new Criteria();
    $c->addAscendingOrderByColumn(self::NAME);
    return self::doSelect($c);
  }

Then specify this method via the config/generator.yml of your admin generated class. Here's how I do it for several different foreign key filters in the back end of salsadelphia.com:

      fields:
        venue_id:
          params:
            peer_method: getAll
        studio_id:
          params:
            peer_method: getAll
        instructor_id:
          params:
            peer_method: getAll
        dj_id:
          params:
            peer_method: getAll
        band_id:
          params:
            peer_method: getAll

Note that the use of a custom peer method means that you're not restricted to alphabetical sorting. In my case I sort first by whether the user has ownership of the studio, venue, etc. and then alphabetically. That helps users find the filters they are most likely to want more quickly.

Don't forget to do a symfony cc before testing your work.

-Tom Boutell, Boutell.Com and P'unk Avenue