Sorting By Custom Column in the Symfony Admin Generator
Comments(11) Did you ever wish you could sort by a partial column in the admin generator? Using DbFinder and a few lines of code, it is now possible.
The symfony admin generator allows you to select which properties of a model you want to display. You can include foreign key fields, or even a partial field to display pretty much everything you want in the list view. The following example uses this ability to display the name of article authors, based on the fact that the Article model has a many to one relationship to the User model:
# in mymodule/config/generator.yml
generator:
class: sfPropelAdminGenerator
param:
model_class: Blog
theme: default
list:
display: [=title, user, category, _nb_posts, created_at]
fields:
user: { name: Author }
This generator configuration includes a partial field that counts the number of blog posts for each blog:
The problem is that only the "True" fields (that is, the ones that correspond to a column in the main table) are sortable. The result is that, with the following example, only the title column is sortable.
With symfony alone, there is no way to make the other columns sortable except overriding the whole _list_th_tabular.php partial in your module, overriding the addSortCriteria() method in the action, and losing the ability to add or remove columns in the future.
Enters DbFinderPlugin. You probably know from this blog that DbFinder offers a very powerful and yet simple way to replace Propel Criteria queries. What you might not know is that the DbFinder plugin bundles a full admin generator theme. It has the exact same features and syntax as the standard symfony admin generator, but it is entirely written with DbFinder queries. And to make this generator theme very usable, it includes the batch_actions extension from symfony 1.1 (that's what allows to display the checkboxes on the left side of the list to perform an action on several records at a time), and the ability to sort by any type of column.
To use the DbFinder admin generator, no need to switch your entire project to DbFinder. Just install the plugin, edit the generator.yml of one of your generated modules, and change the class property from sfPropelAdminGenerator (or sfDoctrineAdminGenerator, if you use Doctrine) to DbFinderAdminGenerator. Refresh the page in your browser, and you should normally see no change. That's good news: despite the fact that all the generator code has been rewritten to work with DbFinder instead of Propel, it is completely backwards compatible.
And once a generated module uses DbFinder, you gain access to the new sort_method option for custom fields:
# in mymodule/config/generator.yml
generator:
class: DbFinderAdminGenerator
param:
model_class: Blog
theme: default
list:
display: [=title, user, category, _nb_posts, created_at]
fields:
user: { name: Author, sort_method: orderByUsername }
category: { sort_method: orderByCategory }
nb_posts: { sort_method: orderByNbPosts }
Refresh the list view, and voila, the column headers are now clickable.
Don't click the new links yet: you've defined three methods for custom ordering, and you still have to write them. To do so, you need to create a BlogFinder, which is a finder class specific to the Blog model class. So create a lib/model/BlogFinder.php class with the following content:
class BlogFinder extends DbFinder
{
protected $class = 'Blog';
public function orderByUsername($order = 'asc')
{
return $this->orderBy('User.Name', $order);
}
public function orderByCategory($order = 'asc')
{
return $this->orderBy('Category.Name', $order);
}
public function orderByNbPosts($order = 'asc')
{
return $this->
leftJoin('BlogPost')->
groupBy('Blog.Id')->
withColumn('COUNT(BlogPost.Id)', 'nbPosts')->
orderBy('nbPosts', $order);
}
}
The finder is smart enough to guess the relationship between the Blog and the User model, as well as the relationship with the Category model, because the YAML schema defines foreign keys between the related tables.
Clear the cache (to allow the autoloading to find the new finder class), refresh your list, and enjoy fully sortable columns.
To finish, here is a small trick to drastically improve your backend performance. Every time the _nb_posts partial is called (and that's once per row in the list), symfony issues a COUNT query. That means that the current configuration will run n+1 queries, n being the number of results per page (typically 20). That's pretty bad for performance. What if you could hydrate an additional column in the main query and use this column in the _nb_posts partial? With DbFinder, that's very easy. Just add a finder_methods setting to your list configuration, as follows:
# in mymodule/config/generator.yml
list:
display: [=title, user, category, _nb_posts, created_at]
fields:
user: { name: Author, sort_method: orderByUsername }
category: { sort_method: orderByCategory }
nb_posts: { sort_method: orderByNbPosts }
finder_methods: [withNbPosts]
Symfony executes all the methods defined in the finder_methods before displaying the list. It allows you to define a default ordering, to filter out some records, or, like here, to add custom column to the main query.
Now it's time to create this BlogFinder::withNbPosts() method. Since it contains part of the code of orderByNbPosts(), and that the finder generator executes sort methods at the end of the action, you can reduce the orderByNbPosts() code accordingly:
public function withNbPosts($order = 'asc')
{
return $this->
leftJoin('BlogPost')->
groupBy('Blog.Id')->
withColumn('COUNT(BlogPost.Id)', 'nbPosts');
}
public function orderByNbPosts($order = 'asc')
{
return $this->orderBy('nbPosts', $order);
}
Now the main list query includes the call for the calculated nbPosts column, and you can change the _nb_posts partial to use it:
Refresh the list view: Ta-da, the result is the same, but using a single query instead of n+1.
So the DbFinder generator offers the same features as the current symfony 1.1 generator, except more. Don't wait until you upgrade your project to symfony 1.2 to enhance your generated modules. Read the DbFinder admin generator documentation, and download the plugin right away.