SUBSCRIBE VIA RSS


Subscribe to our feed

Symfony Experts

Symfony Experts
If you have an urgent question for a symfony-related issue, this is the place to ask.

Topics

Stack Overflow


The old fashioned way

RECENT TUNES

January 8, 2011 – 3:01pm Symfony 1.4 admin generator sort on custom column

If you use the symfony 1.4 admin generator with doctrine you may notice that the column headings in the list view are links that enable you to sort your results by that column. If you customize the list view to include columns that include data from a foreign table, the generator will not automatically create these sort links. Here is how I make foreign columns sortable.

Let’s say I have a model “Payment” and it has a one-to-many relationship with “Person” (i.e. one person has many payments, or the payment table has a foreign key “person_id”). I want the list of payments sortable by “last_name”.

In generator.yml:

 config:
  fields:
   last_name: { is_real: true }
...
  list:
    display:  [id, date, amount, last_name]

Let’s update our Payment.class.php file with methods to fetch the person’s last name.

class Payment extends BasePayment
{
...
  public function getLastName()
  {
    return $this->Person->last_name;
  }

Now, if you reload your generated list view, you’ll see the column heading for last_name has been converted to a link. Now, we’ll have to add this foreign column (last_name) to our query and also add some logic to sort by the proper column when necessary.

Let’s add a custom table method to our list query, adding another line to generator.yml:

...
config:
  list:
  ...
    table_method: doSelectJoinPerson

Let’s add the new method to our PaymentTable.class.php:

class PaymentTable extends Doctrine_Table
{
  ...  
  public static function doSelectJoinPerson($query)
  {
    $rootAlias = $query->getRootAlias();
 
    return $query->select($rootAlias . '.*, p.last_name')
      ->leftJoin($rootAlias . '.Person p');
  }
}

The table_method parameter in our list configuration is used to define which method should be executed on our model’s table when it fetches results from the database. Our main model will always have an alias “r” as defined in the symfony/doctrine source code (check out sfFormFilterDoctrine::doBuildQuery()).

We now need to pass the table alias for our foreign table (in this case, we used ‘p’ for Person) into our sort query if ‘last_name’ is given as our sort parameter. Let’s override the addSortQuery($query) in our actions class:

class paymentActions extends autoPaymentActions
{
...
  protected function addSortQuery($query)
  {
    if (array(null, null) == ($sort = $this->getSort()))
    {
      return;
    }
 
    if (!in_array(strtolower($sort[1]), array('asc', 'desc')))
    {
      $sort[1] = 'asc';
    }
 
    switch ($sort[0]) {
      case 'last_name':
        $sort[0] = 'p.last_name';
        break;
    }
 
    $query->addOrderBy($sort[0] . ' ' . $sort[1]);
  }
...

Update:A few comments below pointed out for the default admin generator theme you’ll also need to add the following method to your controller (actions.class.php):

protected function isValidSortColumn($column)
{
  return Doctrine_Core::getTable(‘Payment’)->hasColumn($column) || $column == ‘last_name’;
}

Once you get used to this process, it’s really not too hard to implement for all of your admin-generated modules. No plugins necessary.

Update 2: Updated doSelect query to use root alias defined in the query per the comments below.

10 Responses to Symfony 1.4 admin generator sort on custom column

  1. Sydney says:

    Nice and simple.

    One little thing, I had to add the following code to the controller to make it work.

    protected function isValidSortColumn($column)
    {
    return Doctrine_Core::getTable(‘Payment’)->hasColumn($column) || $column == ‘last_name';
    }

  2. robo47 says:

    Is it possible that you missed overwriting isValidSortColumn() in the action-class ?
    Because unless I do that, the column is clickable, but no sorting happens and the arrows showing the direction are not shown.

    Something like:

    protected function isValidSortColumn($column)
    {
    if ($column == ‘last_name’) {
    return true;
    }
    return Doctrine_Core::getTable(‘Payment’)->hasColumn($column);
    }

    does the trick

  3. Scott Meves says:

    @Syndey and @robo47 — It’s interesting that you both ran into the same issue so I’m sure for most people adding the isValidSortColumn method will be necessary. I am using a custom admin theme (from Apostrophe CMS) that doesn’t use the same method to generate the sort column headings, and I had no idea.

    Thanks for sharing!!

  4. Jochen Daum says:

    Thank you, saved my life!

  5. Kyle says:

    Instead of referring to the alias “r”, instead use the $query->getRootAlias() method.

    So would be something like

    $rootAlias = $query->getRootAlias();

    return $query->select(rootAlias . ‘.*, p.last_name’)->leftJoin(‘r.Person p’);

    I try my best to not to refer to hardcoded values and this way your schema can change and this won’t break etc

  6. Pingback: Symfony Admin Generator | f00dMonsta

  7. Roman says:

    Hi!

    But what if I have multiple columns of foreign tables?
    Is is it possible to define many methods in table_method?

  8. tbaumann says:

    Thx for this great how-to! One thing was missing in fact of the filter mechanism. I needed to enhance the lib/filter/doctrine/PaymentFormFilter.class.php with the following method (referring to your last_name example):

    public function addLastNameColumnQuery($query, $field, $value)
    {
    $query->andWhere(‘p.last_name= ?’, $value);
    return $query;
    }

  9. Daniel says:

    I am using Symfony 1.4 with default admin generator theme and here you do not need to override “isValidSortColumn” method.

  10. Guill Lo says:

    This has been a great help! I was having this problem a couple of months ago but couldn’t find a way to handle it. Put it aside, and now I found your solution. Thanks for sharing!

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>