Numeric sort in CakePHP paginator

Question

I have the following code in CakePHP.

<th> <?php echo $this->Paginator->sort('standard_id', 'Standard'); ?></th>
<th> <?php echo $this->Paginator->sort('id'); ?> </th>
<th> <?php echo $this->Paginator->sort('title'); ?> </th>

My data for standard_id is like this:

• 3rd
• 4th
• 10th
• 11th
• ... etc.

My problem is standard_id is not sorted properly, it is considering it as a string. I want to apply a numeric sort.

Any Idea?

Answer 1

Add the following to your model:

public function __construct($id = false, $table = null, $ds = null) {
    parent::__construct($id, $table, $ds);
    $this->virtualFields['standard_id_numeric'] = sprintf('CAST(%s.standard_id as UNSIGNED)', $this->alias);
}

Now you should be able to sort by standard_id_numeric.

Alternatively, you can declare the virtual field as:

public $virtualFields = array(
    'standard_id_numeric' => 'CAST(Standard.standard_id as UNSIGNED)'
);

However, this will throw an error if you instantiate your model with an alias.

See

• Cookbook 2.x: Virtual fields: Pagination and virtual fields.