Custom relevance sorting

[eluser]sqwk[/eluser]
EDITED:

This started off as a question whether MySQL CASE works with activerecord in order to do a custom relevance sorting based on multiple ENUM values. It does, and I now have some working code—thought I'd post it here in case anyone else is looking for something similar…

Code:

if(isset($importance_a))
    $case[] = 'CASE WHEN importance_a = "high" THEN 4 WHEN importance_a = "medium" THEN 2 WHEN importance_a = "low" THEN 1 ELSE 0 END';
if(isset($importance_b))
    $case[] = 'CASE WHEN importance_b = "high" THEN 4 WHEN cellar = "importance_b" THEN 2 WHEN importance_b = "low" THEN 1 ELSE 0 END';

if(isset($case)) {
    $this->db->select('*, ('.implode(" + ", $case).') AS relevance', FALSE);
    $this->db->order_by('relevance desc, -importance_a asc, -importance_b asc');
}

This is will create a temporary column relevance and compute its value based on various ENUM field, then sort by relevance with NULLs last. Still not sure about the performance, but should be alright…