cakephp3- table sorting not working

Question

Version: 3.3.*

I’m using the $paginator->sort() method to create links in the column headers for tables of paginated search results in my CMS. You should be able to click them once to sort in ascending order, and then click again to reverse into descending order. But the reverse was never working for me.

But this is not for all fields. Let say I have 5 fields.

<tr class="design">
    <th scope="col"><?= $this->Paginator->sort('Students.id','Id',array('class'=>'link')); ?></th>
    <th scope="col"><?php echo $this->Paginator->sort('Students.userName','Name',array('class'=>'link')); ?></th>
    <th scope="col"><?php echo $this->Paginator->sort('Students.age','Age',array('class'=>'link')); ?></th>
    <th scope="col"><?php echo $this->Paginator->sort('Students.currentClass','Class',array('class'=>'link')); ?></th>
    <th scope="col"><?php echo $this->Paginator->sort('Students.dateJoined','Joined Date',array('class'=>'link')); ?></th>
</tr>

I can sort the table both ways using username, age, and school but not using id and joined. When I fetch the list initially I have specified in my Model function to fetch result based on id and joined in ascending order. Is it because of this, I'm not being able to sort it in descending order?

Is there any way I can achieve this?

Controller function

public function index()
{
    //Listing Students
    $this->paginate = [
                        'finder'        =>  'ListStudents',
                        'sortWhitelist' => ['Students.id',
                                            'Students.userName',
                                            'Students.age',
                                            'Students.currentClass',
                                            'Students.dateJoined',],
                        'limit'         => 25,
                        ];
    $students = $this->paginate($this->Students);  
    $this->set(compact('students'));
    $this->set('_serialize', ['students']);
}

Model Function

public function findListStudents(Query $query, array $options)
{   
    $query 
            ->select(['id','userName','age','currentClass','dateJoined'
                ])
    $query->group('Students.id');

    $query->order(['status' => 'asc']);
    $query->order(['Students.dateJoined' => 'asc']);
    $query->order(['Students.id' => 'asc']);  
    return $query;
}

The issue mentioned in this link is similar to the one I'm facing.I tried the solution mentioned in it but it ain't working.

Answer 1

When I fetch the list initially I have specified in my Model function to fetch result based on id and joined in ascending order. Is it because of this, I'm not being able to sort it in descending order?

Yes, this is the case. You can verify that by commenting out the $query->order statements in your custom finder method. You will find out that descentant sorting Students.id will work then.

I would recommend to simply leave the sorting out of the custom finder method and define the default sorting options when configuring your $paginate variable instead.

StudentsController

public function index()
{
    $this->paginate = [
        'finder' =>  'ListStudents',
        'sortWhitelist' => [
            'Students.id',
            'Students.userName',
            'Students.age',
            'Students.currentClass',
            'Students.dateJoined'
        ],
        'limit' => 25,

        // add custom sorting options here
        'order' => [
            'Students.status' => 'asc',
            'Students.dateJoined' => 'asc',
            'Students.id' => 'asc' 
        ]
    ];
    $students = $this->paginate($this->Students);  
    $this->set(compact('students'));
    $this->set('_serialize', ['students']);
}

StudentsTable

public function findListStudents(Query $query, array $options)
{   
    $query ->select([
        'id', 'userName', 'age', 'currentClass', 'dateJoined'
    ]);
    $query->group('Students.id');

    // remove the sorting here
    /*
    $query->order(['status' => 'asc']);
    $query->order(['Students.dateJoined' => 'asc']);
    $query->order(['Students.id' => 'asc']);  
    */

    return $query;
}

Answer 2

To achieve this, you have to modify your controller and model functions a little bit.

First of all, add extraOptions to your controller function

public function index()
{
    //Listing Students
    $this->paginate = [
                        'finder'        =>  'ListStudents',
                        'sortWhitelist' => ['Students.id',
                                            'Students.userName',
                                            'Students.age',
                                            'Students.currentClass',
                                            'Students.dateJoined',],
                        'limit'         => 25,
                         'extraOptions' =>[
                    'params' => $params                                       ]
                        ];
    $students = $this->paginate($this->Students);  
    $this->set(compact('students'));
    $this->set('_serialize', ['students']);
}

Now you need to override your dateJoined and id sort order. By default, its asc. If user passes a specific direction, it should be taken. So, for that you need to modify your model function a little.

public function findListStudents(Query $query, array $options)
{   
    $query 
            ->select(['id','userName','age','currentClass','dateJoined'
                ])
    $query->group('Students.id');
    $direction  = (!empty($options['extraOptions']['params']['direction'])) ? $options['extraOptions']['params']['direction'] : 'asc';
    $query->order(['status' => 'asc']);
    $query->order(['Students.dateJoined' => $direction]);
    $query->order(['Students.id' => $direction]); 
    return $query;
}

Now sorting will work fine when you are passing a specific direction.