Cake3 Paginator Sort Error

Question

I have a very simply model which I want to paginate. The pagination works but the sort links do not have any effect:

My Controller:

public $paginate = [
        'limit' => 10,
        'order' => [
            'Properties.id' => 'asc'
        ],
        'sortWhitelist' => [
            'Properties.id',
            'Properties.name',
            'Properties.active'
        ],
];

My query:

$properties = $this->Properties->find('all')->where($options)->contain($contains)->order(['Properties.id']);

$this->set('properties', $this->paginate($properties));

My view displays 10 items per page and the links to pages/next/previous work fine. When I click the sort link:

$this->Paginator->sort('id', 'ID')

The url called is:

properties/index/3?sort=id&direction=desc

The page re-loads but the order of the data does not change.

Answer 1

The whitelist expects the exact field name that you are using in sort. It will not automatically prepend the primary table's name, so your whitelist should look like this:

public $paginate = [
    'limit' => 10,
    'order' => [
        'Properties.id' => 'asc'
    ],
    'sortWhitelist' => [
        'id',
        'name',
        'active'
    ],
];

It will see "id" in the query and check that the exact field name "id" exists in the whitelist.

Also, it appears you are including an order() on your query. To allow the paginator to set the order, you should remove this clause.