CakePHP paginator case sensitive

Question

When trying to sort data using Paginator CakePHP, it returns case-sensitive sorting results. I tried adding UPPER around the field that I specify in the order request, but this does not seem to work. I am using CakePHP 2.0.3 with a PostgreSQL database. My $ paginate variable and the code to call it:

var $paginate = array( 'limit' => 25, 'order' => array( 'User.first_name' => 'asc' ) ); .. (In my view function) $users = $this->paginate('User'); 

How to make CakePHP return case-insensitive sorting result?

Answer 1

It's a shame that you cannot use something like this:

  'UPPER(User.first_name)' => 'ASC' 

Which would be pretty simple.

I had this problem in the project, as a result of which I replaced the "order" array like this:

 'order' = "UPPER(first_name) ASC" 

which successfully gave me the correct result.

Unfortunately, however, clicking the column header fields to sort my paginated table then replicated the problem, resulting in my names being case sensitive again.

The solution I came across was to change the PaginatorComponent of cakephp itself so that my sort order is always correct.

In PaginatorComponent (/lib/Cake/Controller/Component/PaginatorComponent.php) I changed the function validateSort, replacing the following line:

 $options['order'] = $order; 

With four lines below:

 foreach($order as $order_field=>$order_direction){ $neworder['UPPER('.$order_field.')'] = $order_direction; } $options['order'] = $neworder; 

This is not elegant and essentially just forces "UPPER ()" around the field name.

This worked in my specific situation, when I needed to always have dimensionless queries.