0

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
  • 17,199
  • 8
  • 49
  • 58
monksp
  • 929
  • 6
  • 14

1 Answers1

0

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:

  1. id
  2. name
  3. 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.

matino
  • 17,199
  • 8
  • 49
  • 58
  • Sounds promising. And I get to remove the full columns query at the same time, cutting some extraneous db queries. ;) So how do I then pass the `DB::expr()` call to the query that initially loads the models? Or is this just for when I have a sort column in the actual table? – monksp Apr 20 '12 at 16:00
  • I don't quite understand what you mean by "how do I then pass the DB::expr() call to the query that initially loads the models". You queries should stay exactly the same, but the new column you can use for order by. – matino Apr 20 '12 at 18:52
  • What I had wanted to do originally was to include the case statement in my original question in the query that loads the orm models, rather than have an extra column with the sortable name. – monksp Apr 22 '12 at 16:50