Using order by FIELD in CakePHP

Question

I'm trying to implement custom sort order into CakePHP 2 application, using the following code:

var $paginate = array(
    'Project' => array(
        'conditions' => array('Project.deleted' => 0),
        'order' => array(
            'Project.pinned' => 'desc',
            'FIELD(Project.status, 1, 3, 4, 0, 2) DESC'
            'Project.date_start' => 'asc',
            'Project.name' => 'asc',
        ),
    ),
);

But, for some reason FIELD(Project.status, 1, 3, 4, 0, 2) DESC is ignored by Cake. Can you help me to make it work?

Answer 1

I made it work by making changes in /lib/Cake/Controller/Component/PaginatorComponent.php

I just found out how to do this and I know it is not perfect since it does not validate if the statement is correct but i think it's a good start for someone who got this issue.

In the function validateSort I added the last elseif for the case it is an ORDER BY FIELD. The problem is that the $order[$alias.'.'.$field] = $value can't work with FIELD since it works differently than most other ORDER BY clause.

foreach ($options['order'] as $key => $value) {
    $field = $key;
    $alias = $object->alias;
    if (strpos($key, '.') !== false) {
        list($alias, $field) = explode('.', $key);
    }

    if ($object->hasField($field)) {
        $order[$alias . '.' . $field] = $value;

    } elseif ($object->hasField($key, true)) {
        $order[$field] = $value;
    } elseif (isset($object->{$alias}) && $object->{$alias}->hasField($field, true)) {
        $order[$alias . '.' . $field] = $value;                 
    } elseif (strpos($alias, 'FIELD')==0){
        $order[$key]=$value;
    }

 }