How to sort external fields using Paginator in CakePHP 3

Question

I'm developing an application using cakephp 3.0. I'm wondering if the paginator allows to sort fields coming from other tables which are included in the 'contain' clause. For example I have this statement:

    $this->paginate = [
        'contain' => ['Actors']
    ];

The table 'Actors' contains the field 'description'. Is it possible to sort by the description field of the table Actors?

I tried to specify the field using the dot notation:

$this->Paginator->sort('Actors.description', __('Description'))

as well as to include the model within the option array:

$this->Paginator->sort('description', __('Description'), ['model' => 'Actors'])

In both cases it doesn't seem to work. These two approaches come from cakephp 2.0 where everything works fine (cakephp Paginator -> sort - model option). Apparently in the new documentation there aren't any updates.

Check out this answer http://stackoverflow.com/questions/28948164/pagination-sort-in-cakephp-3-x?answertab=votes#tab-top — Matt Stephens, Aug 10 '15 at 15:59
thank you. This helped me a lot. Below you may read how I solved my issue — f0rb1dd3n403, Aug 11 '15 at 10:53

score 4 · Accepted Answer · edited May 23 '17 at 11:46

As José Lorenzo says here Pagination Sort in Cakephp 3.x, Paginator blocks each field which is not available in the primary table. Thus if you need to order by a foreign field it is necessary to specify the full qualified name used by cakephp in the joining table.

In my example I need to specify the following:

$this->paginate = [
    'contain' => ['Actors'],
    'sortWhitelist'=>['Actors.description']
];

and use this expression in the view:

<?= $this->Paginator->sort('Actors.description', __('Description')) ?>

In this way everything works like a charm. If you are not able to identify the right field you may enable the query debug and see which field is used in the query.

How to sort external fields using Paginator in CakePHP 3

1 Answers1

Linked