Sonataadminbundle: Docs: Recipe request: sort a list view by multiple values (by default)

[caponica]

This is a cookbook recipe request for...

• how to sort a list view by multiple values (e.g. field1 DESC, field2 ASC) by default

In symfony 1.x admin generator you could define a custom table_method in your generator.yml and use that to do multi-column sort. How do you do something similar in SonataAdmin?

You can either make a ready-to-merge rst file or send me a hyperlink, document file, or detailed comment which I will then reformat and merge into the documentation.

See #1519 for details about the co-ordination of cookbook entries and other ways to contact me.

[rande]

You cannot do that for now

[lineke]

You can override the createQuery method like this:

/**
 * Override to orderby name and date
 * 
 * @param string $context
 * 
 * @return \Sonata\DoctrineORMAdminBundle\Datagrid\ProxyQuery 
 */
public function createQuery($context = 'list')
{
    $queryBuilder = $this->getModelManager()->getEntityManager($this->getClass())->createQueryBuilder();
    $queryBuilder->select('p')
            ->from($this->getClass(), 'p')
            ->orderby('p.date DESC, p.name');

    $proxyQuery = new ProxyQuery($queryBuilder);
    return $proxyQuery;
}

It would be far better if you could do this in the $datagridValues. @rande Any ideas on how to implement this in the bundle? Maybe I can find some time to fix it soon...

[caponica]

@lineke - I just tried setting this up so I could write up an example, but when I tried it I ran into two problems.

First, orderBy() only accepts a single field. Your example works but only because Doctrine happens to treat the parameter as a string and adds "ASC" afterwards. So, I changed it to use two calls to addOrderBy() instead:

$queryBuilder->select('p')
    ->from($this->getClass(), 'p')
    ->addOrderBy('p.date', 'DESC')
    ->addOrderBy('p.name', 'ASC');

However, this led me to my next problem... the user can no longer over-ride the specified sort order by clicking on the column headings. I can write this up as a limitation of the method, but if you (or anybody else) knows of any way that the given ordering should only be used in the absence of a user selected sort column (or only to break ties _after_ considering the user's choice first) then let me know!

[lineke]

Hi Christian,

I never realized, but you are right. That is a limitation of my method.
Thomas: if you have any guidelines on how to implement in the bundle, I
would make some time to implement it so we can have it as a standard
functionality. I am not sure I could find it on my own and make it
backwards compatible as well.

Regards,

Lineke

2013/8/17 Christian Morgan [email protected]

@lineke https://github.com/lineke - I just tried setting this up so I
could write up an example, but when I tried it I ran into two problems.

First, orderBy() only accepts a single field. Your example works but only
because Doctrine happens to treat the parameter as a string and adds "ASC"
afterwards. So, I changed it to use two calls to addOrderBy() instead:

$queryBuilder->select('p')
->from($this->getClass(), 'p')
->addOrderBy('p.date', 'DESC')
->addOrderBy('p.name', 'ASC');

However, this led me to my next problem... the user can no longer
over-ride your specified sort order by clicking on the column headings. I
can write this up as a limitation of the method, but if you (or anybody
else) knows of any way that the given ordering should only be used in the
absence of a user selected sort column (or only to break ties _after_considering the user's choice first) then let me know!

—
Reply to this email directly or view it on GitHubhttps://github.com/sonata-project/SonataAdminBundle/issues/1548#issuecomment-22820373
.

[rande]

This feature has never been implemented as I have no idea about how to make a good UI for multisorting columns ... Also I never have the use case for such feature.

So the first step will be to found out how to display a nice UI ...

[lineke]

Thomas, how about we use the sorting only as a default and still only
support one field sorting in the UI?
I am guessing most people will only use it as a default anyway.

2013/8/19 Thomas [email protected]

This feature has never been implemented as I have no idea about how to
make a good UI for multisorting columns ... Also I never have the use case
for such feature.

So the first step will be to found out how to display a nice UI ...

—
Reply to this email directly or view it on GitHubhttps://github.com/sonata-project/SonataAdminBundle/issues/1548#issuecomment-22857937
.

[caponica]

I think they're two separate engineering issues, which is why I put up two different recipes ;)

The first is to be able to set multiple default sort columns (to use when a user has not made a selection), but to then ignore this default when the user does make a choice.

The second (see #1549) is to allow users to sort by multiple columns. Where I've seen this in the past it means that when you click on a column, then that column becomes the most important search term and results are shown in ASC/DESC order based on the column you clicked. However, ties are broken by the previous selection(s) you made. Effectively the column you clicked is moved to the front of the sort order, and any other selections remain but come afterwards.

For UI, as an MVP, this doesn't need any changes. However, you _could_ enhance the experience by adding an 'x' to columns that are currently part of the sort order which the user can click to remove that column. This is very mush an enhancement, though, and not needed to make multi-column sort useful!

To get back to the default sort order they just re-click on the original breadcrumb/dashboard link to the page which has no extra sort fields in its URL. You can also enhance by adding a 'reset sort order' link to the page if you want.

Multi-column sort set up in this way is very intuitive (users get things sorted by the column they clicked) and when I've set it up in systems (usually private intranets) clients find it very useful!

[benglass]

The ability have a column heading in the list view sort on multiple fields (for example a list of events where the 'Date & Time' header sorts on start date and start time) remains difficult to achieve.

It seems like it would be nice to add an addition option to the ListMapper when adding new items to the list view that would function similarly to query builder option for entity choice forms. You could allow this option to either be an array of order by fields in the format array('fieldName' => 'ASC', 'fieldName2' => 'ASC') similar to the way EntityRepository->findByName('bob', $orderBy) works or it could be a closure that is called with the query builder or ProxyQuery as an argument with the opportunity to modify the order by. I like the closure approach as its more flexible.

As noted before it would also be nice for $datagridValues to support multiple sort values. Perhaps the existing functionality could remain in place but we could add a new field 'sort' that contains a format like the one described above (an array of fields to sort by OR a closure that can modify the query).

[dirkluijk]

+1, at least for setting the default order on multiple fields.

I like @caponica's approach. When a user clicks on a column header, the new orderBy column must not _replace_ the previous one, but must be _prepended_ (i.e. higher priority). I think that would make perfectly sense for both user and developer. It also needs no UI changes, however it would be good to have a way to reset the ordering. But I would add that later.

Maybe I could add a pull request for this. I assume backward compatibility is a must?

[dirkluijk]

What about this approach? (BC is maintained)

Simple configuration, current style (order by one field):

array(
    '_sort_by'      => 'someField', // string is being replaced with FieldDescriptionInterface
    '_sort_order'   => 'asc|desc'
)

Advanced configuration (order by multiple fields):

array(
      '_sort_by' => array(
          array(
              'field'     => 'someField', // string will be replaced with a FieldDescriptionInterface obj
              'direction' => 'asc|desc'
          ),
          array(
              'field'     => 'otherField', // string will be replaced with a FieldDescriptionInterface obj
              'direction' => 'asc|desc'
          ),
      )
)

Note: I would rather prefer the 'fieldName' => 'asc|desc' style, but then you can't replace the key "fieldName" with an object FieldDescriptionInterface. Another approach is to transform this style to the structure mentioned.

List of changes:

• Change Admin::buildDatagrid() to adapt old structure to new (advanced) structure
• Change Datagrid::buildPager() to handle new structure
• Change ProxyQueryInterface and ProxyQuery:
  
  
  
  • Mark setSortBy(), getSortBy(), setSortOrder(), getSortOrder as deprecated
  
  
  • Add/implement addSortBy() and removeSortBy() which handle also the direction and whether the sortBy must be prepended or appended (extra parameter).
  
  
  • Change other code relying on these methods
  
  

Generally I would prefer "order by" over "sort by", but I think it now would not be the right time to change that.

Feedback on this more than welcome.
ping @rande

[onema]

I have been using @lineke solution to ORDER BY on multiple columns for version 2.2.* It has been working well for almost a year.

Recently upgraded to version 2.3.* and the sorting functionality stopped working. This is due to the following addition in the new version: https://github.com/sonata-project/SonataDoctrineORMAdminBundle/commit/d421f0f2ebd7b6281296435fb7083e4559168823#diff-409a051b11d25b4355f7ce9ca60eabd5; the orderBy is removed if getSortOrder() doesn't return anything!

The only way to ORDER BYon multiple columns would be to ensure that the method \Sonata\DoctrineORMAdminBundle\Datagrid\ProxyQuery::setSortOrder is called at some point.

This method is automatically called by the Sonata\AdminBundle\Datagrid\Datagrid::buildPager() method https://github.com/sonata-project/SonataAdminBundle/blob/2.3/Datagrid/Datagrid.php#L126 IF AND ONLY IF the list configuration fields are set to 'sortable' => true.

In my case I had set all columns to 'sortable' => false yet I still want to see things in a specific order; I just don't want users to change it!

I'm not sure if this is an unintended side-effect or it is how it was design to work. @rande can you confirm this please?

To work around this either set at least one column to 'sortable' => true (or don't set sortable at all) or modify the createQuery method like so:

public function createQuery($context = 'list')
{
    $proxyQuery = parent::createQuery($context);
    // Default Alias is "o"
    $proxyQuery->orderBy('o.name', 'ASC');
    // This can only be used for a single column. 
    $proxyQuery->setSortBy([], ['fieldName' => 'date']);
    $proxyQuery->setSortOrder('DESC');

    return $proxyQuery;
}

[mikeevstropov]

Why issue was closed? If it's not working???

protected $datagridValues = array(
    '_sort_order' => 'ASC',
    '_sort_by'    => 'root, lft'
);
// > Just not working without any error

and

array(
      '_sort_by' => array(
          array(
              'field'     => 'root',
              'direction' => 'asc'
          ),
          array(
              'field'     => 'lft',
              'direction' => 'asc'
          ),
      )
)
// > Expected argument of type "FieldDescriptionInterface", "array" given

Thank you @onema for your example. It's working.

public function createQuery($context = 'list')
{
    $proxyQuery = parent::createQuery('list');
    // Default Alias is "o"
    $proxyQuery->addOrderBy('o.root', 'ASC');
    $proxyQuery->addOrderBy('o.lft', 'ASC');

    return $proxyQuery;
}
// $context argument is deprecated, but i can't remove it!

[Tofandel]

@mikeevstropov Which version of the bundle are you using, because on stable 3.18.2 I can't find the method addOrderBy() ?

Edit: Okay, nevermind it's something else, the code actually works but my IDE can't find thoose methods so I went to look for them and they just forgot to add them in the interface.

[hroux]

@mikeevstropov It's better to use $query->getRootAlias() like this :

public function createQuery($context = 'list')
{
    $proxyQuery = parent::createQuery('list');
    $proxyQuery->addOrderBy($proxyQuery->getRootAlias().'.root', 'ASC');
    $proxyQuery->addOrderBy($proxyQuery->getRootAlias().'.lft', 'ASC');

    return $proxyQuery;
}

[icedevelopment]

So is there any solution to keep 'sortable' => true in configureList while sorting by multiple fields at the same time in createQuery?

[stale[bot]]

Is this still relevant? If so, what is blocking it? Is there anything you can do to help move it forward?

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.