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!

score 3 · Answer 1 · answered Jan 26 '11 at 08:22

3

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', ...)")

answered Jan 26 '11 at 08:22

psyho

7,142
5
23
24

Thanks! There isn't a DB independent way to use this function, is there? – polarblau Jan 26 '11 at 08:36
Well, you could use case/when/else instead - it works both in MySQL and Postrgesql http://stackoverflow.com/questions/1309624/simulating-mysqls-order-by-field-in-postgresql – psyho Jan 26 '11 at 10:06

score 3 · Accepted Answer · answered Jan 26 '11 at 08:43

3

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/

answered Jan 26 '11 at 08:43

corprew

1,991
14
17

That sure looks interesting! Thanks a lot. I've to admit that I've no clue of the enum type. A quick look–up found this: https://github.com/ikspres/enum_fu — Do you happen to have experience with either of these libraries? Does sorting really work as intended? – polarblau Jan 26 '11 at 08:51
I think I'll go with a mixed approach and try to use the enum_column plugin. – polarblau Jan 26 '11 at 11:13

score 2 · Answer 3 · answered Jan 26 '11 at 08:34

2

If you want to order the collection after a certain criteria then you must store that criteria somewhere.

I don't know if this goes against your "not in Ruby" criteria but I would probably do something like this:

class House < ActiveRecord::Base
  STATES { 0 => "Dreaming",
           1 => "Planning",
           2 => "Building",
           3 => "Living",
           4 => "Tearing Down" }

  validates_inclusion_of :state, :in => STATES.keys

  def state_name
    STATES[self.state]
  end
end

@houses = House.order("state")

In this case the db field state is an integer instead of a string. It makes it very effective for database storage as well as querying.

Then in your view, you call state_name to get the correct name from the STATES hash stored in the model. This can also be changed to use i18n localization by using labels instead of strings in the hash.

answered Jan 26 '11 at 08:34

DanneManne

21,107
5
57
58

Thanks for the answer. That's what I meant by field ID, though. I'd love to be able to use the state name as string for this purpose. – polarblau Jan 26 '11 at 08:39
No problem. I just like to ask, why is it necessary to use the strings? Are there other applications using the same db? – DanneManne Jan 26 '11 at 08:43
Actually this field is probably going to be used by a state machine of sorts and I'd just like to keep the code as simple as possible. Having this layer of abstraction there would help me certainly in this one case but I've a feeling it would introduce problems and possibilities for bugs in others. — What do you think? – polarblau Jan 26 '11 at 08:46
Actually, I would think that my solution fits very well in a state machine scenario. Like def next_state; STATES[self.state + 1]; end just to name one example. But if you think it will be too complicated then you shouldn't do it. – DanneManne Jan 26 '11 at 08:54
From what I understand does the enum_column plugin what you suggest but masks the 'ID's. So +1 and thanks again, I think I might go with a mixed approach. – polarblau Jan 26 '11 at 11:12
Glad to hear. I do think the pro's outweighs the con's. – DanneManne Jan 26 '11 at 17:06

Sort by state in database

3 Answers3