Manual Ordering in Models

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

Have you tried acts_as_list?

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

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?

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