Sort by state in database

Question

Given I have a model house and it lives through several states, something like this: Dreaming —> Planning —> Building —> Living —> Tearing down

If I would want to retrieve let's say ten houses from the databse and order them by the state field, I'd get first all houses in the Building state, then Dreaming, then Living, …

Is it possible to fetch all houses from the database and order them by the state in the order intended before retrieving them? Meaning, first all houses in the Dreaming state, then Planning, etc. E.g. by providing the order in an array for comparison of sorts.

I'd like to avoid doing this in Ruby after having fetched all entries as well as I wouldn't want to use IDs for the states.

After reading up on enum implementations, I guess, if I can make it work, I'll try to combine the enum column plugin with the state_machine plugin to achieve what I'm after. If anyone has done something like this before (especially the combination under Rails 3), I'd be grateful for input!

Answer 1

Here's some information on how to use SQL ENUMs in rails -- they're relatively database portable and do roughly what you want -- http://www.snowgiraffe.com/tech/311/enumeration-columns-with-rails/

Answer 2

If you are using MySQL, then the solution is to do ORDER BY FIELD(state, 'Building', 'Dreaming', 'Living', ...):

House.order("FIELD(state, 'Building', 'Dreaming', 'Living', ...)")