How to sort a column holding roman numbers using Pagination in Cakephp 2.x

Question

I have a field (radio buttons) to select a century.

So when I show a list of my fields in the view, I can sort the columns (ASC or DESC), but roman numerals are strings with these options:

(XVI, XVII, XVIII, XIX, XX, XXI)

Is there any way to tell paginator to use a custom usort function, but just for that column?

I already have a function to convert and sort the romans, but how can I use it along with the paginator sort function in the view?

CakePHP 2.x.

Answer 1

You have to do the sorting at the database level, so usort() won't help.

A possible solution is to do the following:

1. Create a MySQL user defined function to convert roman numerals to arabic numbers. A few are provided in the following question:

   • MySQL Custom Function to Turn Roman Numeral Into Arabic

.

2. Create a virtual field that uses this function to return the century in numeric format:

   public function __construct($id = false, $table = null, $ds = null) {
       parent::__construct($id, $table, $ds);
       $this->virtualFields['century_number'] = sprintf('convert_to_number(%s.century))', $this->alias);
   }
   

3. Use this virtual field to sort the results in your pagination. CakePHP can handle this, as stated in the Cookbook 2.x: Pagination and virtual fields.

Although the above works, perhaps it would make things easier if you stored the centuries in a numeric field type (or as a date). Converting to roman numerals can be easily done in the view.