$ this-> Paginator-> sort不适用于cakephp 3中的数字

Question

When I am using paginator sort in cakephp 3 it is not working properly.It is display the following order

Sno
1
11
13
18
2
25
3
instead of 
Sno
1
2
3
11
13
18
25

My view page code is

           <th> <?= $this->Paginator->sort('Sno') ?></th>
            <th><?= $this->Paginator->sort('id','Order ID') ?></th>
            <th><?= $this->Paginator->sort('user_id','Cust ID') ?> </th>
            <th><?= $this->Paginator->sort('first_name','Cust Name') ?></th>
            <th><?= $this->Paginator->sort('created_date','Date') ?></th>
            <th><?= $this->Paginator->sort('order_qty','Order Qty') ?></th>

Can any body please help me. Thanks in advance.

Answer 1

The behavior is perfectly normal because the column type you are using to store your ids is string and not numerical. When ordering strings it happens that 11 comes before 2

I suggest you to change your schema and switch to a numerical id.

But if for some reasons you can't do that then you can create a calculated field to store the numeric value

in your find call in the controller you have to add the field

->select(['my_id' => "CAST(REPLACE(id, 'CUS_', '') AS UNSIGNED)"])

and in the view

<?= $this->Paginator->sort('my_id','Order ID') ?>

remember to add the field to the sortWhitelist

$this->paginate = [
        'sortWhitelist' => ['my_id']
];