Do A Case Or Switch Statement In SQL And Rails

Posted By Weston Ganger

In one of my Rails apps I needed to do a case statement for a search. SQL has a built in function for this fortunately.

# Use the %q method to make the sql string more readable
Post.order(%q{
  CASE current_state 
  WHEN 'Created' THEN 1 
  WHEN 'In Progress' THEN 2 
  WHEN 'Completed' THEN 3 
  END
})

# OR a more dynamic method

the_order = ['Created','In Progress','Completed']
the_sql = "CASE current_state"
the_order.each_with_index do |x,i|
  the_sql += " WHEN #{x} THEN #{i}"
end
the_sql += " END"

Post.order(the_sql)