Cakephp 3.3 sorting pagination results by multiple columns

Question

Need some help in sorting paginated result in cakephp 3.3 I want to sort by multiple columns but pagination consider only the first if I go on next (or any other page). Is it a feature or a bug?

This is the pagination command in controller:

$this->paginate = [
            'limit' => 20,
            'order' => [
                'Iktato.evszam' => 'DESC',
                'Iktato.iktatoszam' => 'DESC'
                ]
        ];

Generated query:

SELECT 
  Iktato.id AS `Iktato__id`, 
  Iktato.iktatoszam AS `Iktato__iktatoszam`, 
  Iktato.evszam AS `Iktato__evszam`, 
  Iktato.datum AS `Iktato__datum`, 
  Iktato.honnan AS `Iktato__honnan`, 
  Iktato.felado AS `Iktato__felado`, 
  Iktato.cimzett AS `Iktato__cimzett`, 
  Iktato.targy AS `Iktato__targy`, 
  Iktato.tema AS `Iktato__tema`, 
  Iktato.iktatoszemely AS `Iktato__iktatoszemely`, 
  Iktato.megjegyzes AS `Iktato__megjegyzes`, 
  Iktato.allapot AS `Iktato__allapot`, 
  Iktato.user_id AS `Iktato__user_id` 
FROM 
  iktato Iktato 
ORDER BY 
  Iktato.evszam desc 
LIMIT 
  20 OFFSET 20

The generated pagination links has only one sorting parameter. How can we force Paginator helper to consider the second parameter too?

A mezőnevekből úgy tűnik, hogy magyar vagy :) Van egy CakePHP-s facebook csoportunk ha érdekel facebook.com/groups/cakehu — rrd

chronon chronon · Accepted Answer · 2017-09-03T14:19:41

You can accomplish this using a custom finder. In your model's table class:

public function findOrdered(Query $query, array $options)
{
    return $query
        ->order([
            'Iktato.evszam' => 'DESC',
            'Iktato.iktatoszam' => 'DESC'
        ]);
}

And then in your controller:

$this->paginate = [
    'limit' => 20,
    'finder' => 'ordered',
];

With this the pagination links should work as expected, though if you are using pagination column sorting ($this->Paginator->sort('evszam'), etc.) in your view you'll still have issues.

Cakephp 3.3 sorting pagination results by multiple columns

1 Answers