Sort list view in Sonata Admin by related entity fields

Question

Using Sonata Admin Bundle, which is a great add-on for Symfony, I have bumped into the problem described as follows.

Let's say we have 3 entities: City, State and Country. They all have the properties id and name. City has a many-to-one relation to State and State has a many-to-one relation to Country. They all have __toString methods displaying the value of the property name.

We can create a list view for the entity City in Sonata Admin like this:

protected function configureListFields(ListMapper $listMapper)
{
    $listMapper
        ->addIdentifier('id')
        ->add('name')
        ->add('state')
        ->add('state.country')
    ;
}

For illustration the view could look like this:

|-----||--------------------||--------------------||--------------------|
| Id ^|| Name ^             || State              || State Country      |
|-----||--------------------||--------------------||--------------------|    
| 1   || New York           || New York           || USA                |
| 2   || Acapulco           || Guerrero           || Mexico             |
| 3   || Calgary            || Alberta            || Canada             |
| 4   || Tijuana            || Baja California    || Mexico             |
| 5   || Vancouver          || British Columbia   || Canada             |
| 6   || Los Angeles        || California         || USA                |
|-----||--------------------||--------------------||--------------------|

Per default the list is sortable by the columns Id and Name, the sign ^ should depict that. I would like to be able to sort the list by the related entity fields and have a link pointing to the show action for the related entity.

Here is how I have achieved the sorting by State:

//...
->add('state', null, array(
    'route' => array('name' => 'show'),
    'sortable' => true,
    'sort_field_mapping' => array('fieldName' => 'name'), // property name of entity State
    'sort_parent_association_mappings' => array(array('fieldName' => 'state')) // property state of entity City
))
//...

Now the list view is sortable by the property name of the entity State and all fields in the column State point to the show page for the current state:

|-----||--------------------||--------------------||--------------------|
| Id ^|| Name ^             || State ^            || State Country      |
|-----||--------------------||--------------------||--------------------|    
| 3   || Calgary            || Alberta            || Canada             |
| 4   || Tijuana            || Baja California    || Mexico             |
| 5   || Vancouver          || British Columbia   || Canada             |
| 6   || Los Angeles        || California         || USA                |
| 2   || Acapulco           || Guerrero           || Mexico             |
| 1   || New York           || New York           || USA                |
|-----||--------------------||--------------------||--------------------|

How do I sort the list view by the Country (City->State->Country)? Something like this:

|-----||--------------------||--------------------||--------------------|
| Id ^|| Name ^             || State ^            || State Country      |
|-----||--------------------||--------------------||--------------------|    
| 3   || Calgary            || Alberta            || Canada             |
| 5   || Vancouver          || British Columbia   || Canada             |
| 2   || Acapulco           || Guerrero           || Mexico             |
| 4   || Tijuana            || Baja California    || Mexico             |
| 6   || Los Angeles        || California         || USA                |
| 1   || New York           || New York           || USA                |
|-----||--------------------||--------------------||--------------------|

When I try something like the above code snippet:

//...
->add('state.country', null, array(
    'route' => array('name' => 'show'),
    'sortable' => true,
    'sort_field_mapping' => array('fieldName' => 'country.name'), // property name of entity Country
    'sort_parent_association_mappings' => array(array('fieldName' => 'state.country')) // property country of entity State
))
//...

then an exception error is thrown. I tried different combinations, but all without success.

I could do:

  protected function configureListFields(ListMapper $listMapper)
{
    $listMapper
        ->addIdentifier('id')
        ->add('name')
        ->add('state.name')
        ->add('state.country.name')
    ;
}

and get the sorting issue solved, but then there are no links to the entities.

The official documentation is very good, but is missing this topic. So, how to sort a list view by hierarchical entities?

Answer 1

The next day after posting the question I was digging around the source code of SonataAdminBundle and Symfony and found the solution. It is actually very easy. Here it goes:

//...
->add(
    'state.country',
    null,
    array(
        'associated_property' => 'name', // property name of entity Country
        'sortable' => true, // IMPORTANT! make the column sortable
        'sort_field_mapping' => array(
            'fieldName' => 'name' // property name of entity Country
        ),
        'sort_parent_association_mappings' => array(
            array('fieldName' => 'state'), // property state of entity City
            array('fieldName' => 'country') // property country of entity State
        )
    )
)
//...

With associated_property we set the property that should be displayed. This can be omitted if we have defined a __toString method in the entity. In this case it means the name of the country will be displayed in the column.

The option sort_field_mapping requires an array with the key fieldName holding the property by which we're sorting. Here we sort by the country's name. We could however sort by population, assuming we have that property in the entity Country, although we're displaying the value for the name.

And sort_parent_association_mappings is the most interesting part. Here we define the properties by which the join query should be created: City has a property state, which is the entity State, which itself has the property country being the entity Country.

I hope my explanation is comprehensible and can help other people too.