Postgres: sort rows by column with known values

Question

I have an events table with a state column. If everything goes according to plan, the states can be only the following:

• scheduled
• invited
• notified
• started
• ended

Is it possible to order state and indicate what value will be first, second, third, etc. ??

Reward Points: Is there a way to make this easy in Rails 3?

Answer 1

1.If you just need sql in postgres, here it is:

 select * from events order by (case state when 'scheduled' then 1 when 'notified' then 2 when 'invited' then 3 when 'started' then 4 when 'ended' then 5 end) 

you can change the order of states in sql, you do not need to change the ruby ​​code, play the sql script: http://sqlfiddle.com/#!12/976e9/3 .

2. Under the assumption of mu, you can use an enumeration type, it is more efficient, if you need to change the order, you can recreate the enumeration. see this sql script: http://sqlfiddle.com/#!12/f6f3d/2

 CREATE TYPE states AS ENUM ('invited', 'scheduled', 'notified', 'started', 'ended'); create table events( name varchar(100), state states ); select * from events order by state; 

3. In a pure ruby, you can define a hash:

 test_hash = {'scheduled'=>1, 'notified'=>2, 'invited'=>3, 'started'=>4, 'ended'=>5} Events.all.sort! {|x, y| test_hash[x.state] <=> test_hash[y.state]} 

4. But, in my opinion, you should add a table with the name "status", with the columns "name" and "sequence" and indicate the order in the "sequence". Then join the “events” and “states”. When you change the order, you do not need to change the code.