Custom sorting

[SpadXIII]

I've messaged Banks about this a little while ago, but I've come up with something like a solution.

What I was trying to do is have custom sorting on a specific field. This field is a tinyint with values ranging from 0 to 4. I wanted to sort it like 1,2,3,4,0. To sort this with a regular query, you'd add:

SELECT * FROM `table` ORDER BY FIELD(`field_name`, 1,2,3,4,0)

But I'd like to have this sorting as default in my Jelly model, so I added it in the meta with the sorting method:

$meta->table('table')
    ->sorting(array(
        DB::expr('FIELD(`table`.`field_name`, 1,2,3,0)')
    ));
// I've used the value part and not the key part of the array, because a class instance is not valid when used as a key

This would result in a false query though:

SELECT * FROM `table` ORDER BY `table`.0 FIELD(`table`.`field_name`, 1,2,3,0)

I've made 2 changes in Jelly_Builder_Core to get it work: In the method 'order_by', I've added:

public function order_by($column, $direction = NULL)
{
    // Swap the column and direction only when using a Database_Expression for sorting
    if (is_int($column) AND $direction instanceof Database_Expression)
    {
        $column = $direction;
        $direction = NULL;
    }

    return parent::order_by($this->_column($column, TRUE), $direction);
}

Another change in the same file :

protected function _column($field, $join = TRUE, $value = NULL)
{
    // Return the field if this is a Database_Expression
    if ($field instanceof Database_Expression)
    {
        return $field;
    }
    // etc

[banks]

Does it not work to do this instead:

$meta->sorting(array(
    'FIELD("table.field_name", 1, 2, 3, 4, 0)' => NULL 
))

Kohana's query builder (and therfore Jelly's) intelligently quotes table columns when specified in this manner (i.e. with double quotes around the column).

The same logic makes this possible in Query builder: select(array('SUM("views")' => 'total_views'))->...

As far as I know this should work in order_by() too.

[banks]

Obviously table.field_name should actually be model.field_name in Jelly...

[SpadXIII]

hmm, why didn't I think of that? Hmm.. But yes, intelligent quoting that works.

[banks]

In that case I'll close this issue. Thanks for you help testing Jelly!