Kohana 3.2 ORM with columns based on DB::expr()

Question

In my current app, I have a list of plays and it is almost universally sorted by the play's name in queries. Currently, it is built simply using DB::select() statements, but while we're doing a lot of refactoring, we want to begin using Kohana's ORM library. The problem comes from the fact that we don't typically sort by the actual play name, but rather a processed version, stripping out articles and the like and sorting by that (so that The Book of Mormon appears with the Bs, rather than the Ts). I don't know how to carry that over into the ORM model definition.

The select code looks like this (abridged):

DB::select(column, column,
        DB::expr("CASE WHEN SUBSTRING_INDEX(show_name, ' ', 1) IN ('a', 'an', 'the')
              THEN SUBSTRING(show_name, INSTR(show_name, ' ') + 1)
              ELSE show_name
              END AS show_name_sort")

Is there a way to create a fake show_name_sort column that Kohana will ignore on save, but that I can still use in an order_by call? Or do I just need to create an actual column with the sorted version of the name in it?

matino matino · Accepted Answer · 2012-04-19T07:12:41

It is possible. You need to setup $_table_columns array to reflect all your columns but the "fake" one. Suppose the table has 3 columns:

id
name
description

In your model you should override $_table_columns, so ORM will not rely on SHOW FULL COLUMNS query:

class Model_Yourmodel extends ORM
{
    protected $_table_columns = array(
            'id' => '',
            'name' => '',
        );

Example query can look like this:
ORM::factory('yourmodel')->order_by('description')->find_all()

This way also save method will ignore the description column, because it's not listed in $_table_columns array.

Kohana 3.2 ORM with columns based on DB::expr()

1 Answers