[thelist] SQL user-defined order

Ken Snyder kendsnyder at gmail.com
Wed Oct 10 12:43:28 CDT 2007


On 10/10/07, Matt Slocum <matt.slocum at gmail.com> wrote:
> I want the user to be able to decide the order of a list of
> categories. The way I first tried to do it is making an 'order' field
> that keeps a numerical order. The problem is that if the last item is
> moved to the beginning, then every 'order' value has to be updated.
> This is possible, but very inefficient.
>
> Does anyone have any ideas?

A couple thoughts.

You can typically change sequence in the database based on the moved
item id and its new position using just two queries:
UPDATE category SET sequence = sequence + 1 WHERE sequence >= @MOVED_TO;
UPDATE category SET sequence = @MOVED_TO WHERE id = @MOVED_ITEM_ID;

When moving the last item to the beginning, @MOVED_TO would equal 1.

Most likely, performing just these two queries often shouldn't
overburden the database. The alternative (using a submit button or
setting a timeout for a few seconds then doing one query for each item
setting resting sequence based on the id) is fine, just not as user
friendly.

- Ken Snyder



More information about the thelist mailing list