Thursday, April 19, 2012

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

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?





No comments:

Post a Comment