Pagination Sort in Cakephp 3.x

Question

In cakephp 3.x i can't do paginate order in a find

This is my controller:

//AgentsController.php
public function show()
{
    $agents = $this->Agents->find()
    $this->set('agents', $this->paginate($agents));
}

And here part of my view

//show.ctp
<!-- ....... -->
<table class="table table-striped">
   <thead>
      <tr>
        <th>
            <?php echo $this->Paginator->sort('full_name', 'Nome', array('escape' => false)); ?>
        </th>
        <th>
            <?php echo $this->Paginator->sort('username', 'Email', array('escape' => false)); ?>
        </th>
        <th>
            <?php echo $this->Paginator->sort('regions', 'Regioni', array('escape' => false)); ?>
        </th>
      </tr>
   </thead>
<!-- ....... -->

Where i wrong?

You mean when clicking on one of the sort links? If that is the case, what is the generated url? — José Lorenzo Rodríguez, Mar 09 '15 at 21:30
There isn't order by. Only Select, From, Join, Limit and offset — DarioLap, Mar 10 '15 at 10:06
In this example case isn't it, but in my project is an associated table (Users.username) — DarioLap, Mar 10 '15 at 10:14

score 12 · Accepted Answer · answered Mar 10 '15 at 10:45

12

The Paginator will block any attempt of sorting by a column that does not exist in the primary table of the query you are using. In this case you have 2 options. The first option is changing the sort links to tell cake that the column belongs to a related table:

<?php echo $this->Paginator->sort('Users.full_name', 'Nome'); ?>

Or you can tell it in the component that sorting by a given set of columns is allowed:

$this->paginate = ['sortWhitelist' => ['full_name', 'username']]

answered Mar 10 '15 at 10:45

José Lorenzo Rodríguez

9,594
35
32

Good! it works. I haven't sortWhitelist in my controller. Thanks! – DarioLap Mar 10 '15 at 12:19
3

@Pratik The answer probably shouldn't say "or", according to the docs you _must_ use the `sortWhitelist` option in case you want to sort on associated data, and the field must be supplied in `Users.full_name` format. See **http://book.cakephp.org/3.0/en/controllers/components/pagination.html#control-which-fields-used-for-ordering** – ndm Apr 27 '15 at 11:22
please visit http://stackoverflow.com/questions/29893881/cakephp-3-x-sorting-of-another-model-is-not-working – Pratik Apr 27 '15 at 11:24

score -1 · Answer 2 · edited Oct 14 '15 at 06:27

-1

Try:

$this->paginate = ['sortWhitelist' => ['full_name','username','regions'],'limit' => 3];

This will also set pagination limit too.

edited Oct 14 '15 at 06:27

DavidW

29,336
6
55
86

answered Oct 14 '15 at 06:04

Krunal Dave

272
6
16

3

This is almost the same as the 6-month-old accepted answer, changing the limit (which isn't in the question). Not a useful answer. – AD7six Oct 14 '15 at 07:46

Pagination Sort in Cakephp 3.x

2 Answers2

Linked