rails controller custom text order

Question

I have a model Post with columns body and status.

# Post.rb:
STATUSES = ["Active", "Frozen", "Finished"]

# Posts_controller:
`@posts = Post.order('status DESC')`

How can I make the order not alphabetical, but custom?

Like:

@posts = Post.order('status Finished, Active, Frozen')

Databases do not support custom order out of the box, but something like https://stackoverflow.com/a/29785879/2483313 (without the where condition and scoped to the status column) might work for you. — spickermann, Mar 31 '18 at 12:55
Look into using an [`enum`](http://api.rubyonrails.org/v5.1/classes/ActiveRecord/Enum.html#method-i-enum) for the status where the values are stored as integers in the database. Assuming you only want a fixed order, rather than customising per query you'd be able to order the options in the enum tor match the desired order. — mikej, Mar 31 '18 at 13:57

score 1 · Answer 1 · answered Apr 01 '18 at 08:43

A commenter suggested using enum, and that may be best for you. You can also try adding a function at the database level that let's you do custom sorts, depending on your database. Here's a quick blog post on how you might set that up. Your sort function might look something like this (credit to blog post cited above):

CREATE OR REPLACE FUNCTION custom_sort(anyarray, anyelement)
  RETURNS INT AS 
$$
  SELECT i FROM (
     SELECT generate_series(array_lower($1,1),array_upper($1,1))
  ) g(i)
  WHERE $1[i] = $2
  LIMIT 1;
$$ LANGUAGE SQL IMMUTABLE;

This gives you a function you can call with a custom ordering, so your sort might look like this:

ORDER BY custom_sort (ARRAY['Finished', 'Active', 'Frozen'], posts.status)

rails controller custom text order

1 Answers1