Manual Ordering in Models

rubyonrails-talk
1

What is the best way to track order in a model? For example, maybe the user can reorder items in a todo list.

I’ve thought of including a field called “order” or something, but then I’d have to update every single one when I change the order. I’ve also thought of specifying the “order” as a number in the thousands, and just halve the difference whenever something is inserted between something else.

I could try to create some kind of linked-list in the database (so, specify “nextItem” and then update them accordingly), but that’s kind of hacky.

One other idea is to have a separate model that tracks the order somehow.

Any ideas?

Thanks!

~sean

2

Have you tried acts_as_list?

3

Sean Clark Hess wrote:

I've thought of including a field called "order" or something, but then I'd have to update every single one when I change the order.

You can update them all with just 2-3 SQL statements, so it doesn't have be to a performance concern. I've done this several times.

Side note: I think "position" is the more commond name for a column like this, at least within Rails apps.

HTH

4

Any hint on what those SQL statements might be? Something like UPDATE * WHERE position > N … then… I’m not so good at procedural SQL. Maybe you were saying you’d get all the records higher than N, then loop through and increment the position?

5

Sean Clark Hess wrote:

Any hint on what those SQL statements might be?

This UNTESTED code might get you off to a good start.

      conditions_hash = {:old_position => old_position, :new_position => new_position}       if old_position > new_position         MyModel.update_all('position = position + 1',             ['position >= :new_position AND position < :old_position', conditions_hash)       elsif old_position < new_position         MyModel.update_all('position = position - 1',             ['position <= :new_position AND position > :old_position', conditions_hash])       end #do nothing if old_position == new_position       my_model.update_attribute :position, new_position

You should also 'clean' new_position. If it is > the maximum position, then you should set it to the maximum position. If it is < 1 then you should set it to one.

HTH