I have an events table with a state column. If everything goes according to plan, the states can only be one of:
scheduledinvitednotifiedstartedendedIs it possible to order by state and specify which value comes first, second, third, etc...?
Bonus Points: Is there a way to do this easily in Rails 3?
1.If you just need a 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, no need to change the ruby code, play the sql fiddle: http://sqlfiddle.com/#!12/976e9/3.
2.In mu's suggestion, you can use a enum type, it's more efficient, if you need to change the order, you can recreate the enum. see this sql fiddle: 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 pure ruby way, 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 named "states", with columns "name" and "sequence", and specify the order in "sequence". Join the "events" and "states" then. When you change the order, you don't need to change the code.
enum type in PostgreSQL, they sort sensibly. OTOH, trying to get AR to play nice with an enum could be more work than it is worth.
enum, I tried it and added your suggestion to the answer, do I understand correctly? If there are something wrong. please point it, thank you:)