Sonata admin - “order by” field in related table

Question

I have a Product admin class. The Product entity has a many-to-one relationship with a Category entity, i.e. a product is associated with a category.

In the admin "list" page for products, I need to sort by the category name (alphabetically) that each product is associated with.

Setting the default field to sort by is easy if the field is on the entity itself (see Sonata admin bundle order for how to do this). But I cannot figure out how to sort by a field in a related table.

Any help is appreciated.

Answer 1

It seems a workaround, but it works. You have to add a join overriding createQuery() method, than assign a default sortBy overriding $datagridValues:

<?php
use Sonata\DoctrineORMAdminBundle\Datagrid\ProxyQuery;

class ExpenseAdmin extends Admin
{
    protected $datagridValues = array(
        '_page'       => 1,
        '_sort_order' => 'ASC', // sort direction
        '_sort_by' => 'c.name' // field name
    );

    /**
     * @return \Sonata\AdminBundle\Datagrid\ProxyQueryInterface
     */
    public function createQuery($context = 'list')
    {
        $query = parent::createQuery($context);

        return new ProxyQuery($query
            ->join(sprintf('%s.category', $query->getRootAlias()), 'c'));
    }
}

Answer 2

Asume name is the property of entity Category by wich you want to sort. You may do this in you ProductAdmin.php

protected function configureListFields(ListMapper $listMapper)
{

     $listMapper->add('category.name', null, array(
            'sortable' => true,
     ));
     ...
}

This way you leverage the ordering links in the header of the list, generated by Sonata.

Edit

If you would also like to have a link on the category name in products list to quickly edit the Category entity, assuming you have created a CategoryAdmin class, you should write your code like this:

protected function configureListFields(ListMapper $listMapper)
{

     $listMapper->add('category', null, array(
            'sortable' => 'category.name',
     ));
     ...
}

And in your Category class you should implement the __toString() method like this:

public function __toString()
{
    return $this->getName();
}