Hello everyone,

In part of an application I am working on the user is able to put specific items in any order they choose. A user can delete/reorder items and it all needs to be saved back to the database.

Currently, there is a field in place in the db with a number indicating the order number.

say a person deletes an item. The order may then be 0,1,3,4 when it should be 0,1,2,3.

What is the best way to go about updating the order field in this type of situation? Is there a better way to keep track of order?

Thanks

the best way is to make sure they are in sequence, without worrying about whether there are any gaps in the numbers, or even what the values of those numbers might be -- they should never be shown to the user anyway, and the "move up" or "move down" options/buttons should not show them

i kinda like using a floating point number

that way, you can "resequence" an item to fall between two other items simply by setting its sequence number to the average of the sequence numbers of the two items it has to go between

What I don't understand: Does your order number need to be constant, or can it be changed?

It should be up to the front end to figure out the new order and update the backend accordingly on a row by row basis. Using floating point numbers seems like a more efficient way of dealing with very large lists but may be overkill for your situation.

overkill? really?

so what would you advise instead, andrew?

Load a list of items on the front end with there respective positions. When an item is moved from one position to another the front end should update this change by informing the back end that, for example, item2 is now in the position of item1 and item1 is in the position of item2 which ends up as a couple of SQL queries on the back end.

The next part is what to do when a user deletes an item. In most cases I would say don't worry about it. New items are added with a position 1 greater than the maximum position, and when reordering items they simply exchange positions. If you really have to fill in the gaps, the front end should send a complete list of the items with their new positions to the back end, or you could have something on the back end that runs though the items updating them with there new positions.

A question for your floating point method, what happens when you end up trying to squeeze more than 1 item between two whole numbers? The code needed to deal with this, I can imagine would be pretty complex for a task so trivial.

Andrew; your operations require more than one query (or a complicated single query) to be executed when a single item is re-ordered. This will affect more than one row in its operation.

Using the float method allows a single query to be written for each execution, which affects a single row! If you ask me, this is far superior not just in terms of efficiency, but a lot less complex too

But two rows are effected at the simplest level, so update these rows. Why introduce a complex system of floating point numbers to the table (pun intended)? To what level of precision do you go to ensure you have room between whole numbers? And what happens if you run out of room? How do you move rows around that are between the same whole numbers? It all seems very convoluted, plus google doesn't seem to have anything on the subject..

you imagine wrong

to "move" (and i put the word in quotes because of course it isn't moved at all, only the sequence number is changed) an item, you simply update it so that its sequence number is equal to the average of the two numbers it must fall between

for example, to "squeeze" an item between the two whole numbers, say 5 and 6, you would assign it a value of 5.5

then to "squeeze" another item between 5 and 5.5, you would assign it a value of 5.25

then to "squeeze" another item between 5 and 5.25, you would assign it a value of 5.125

then to "squeeze" another item between 5 and 5.125, you would assign it a value of 5.0625


i could go on for several pages like this, and this method would continue to work fine

overkill? i don't think so

meanwhile, with your whole numbers, you'd be re-sequencing the entire table above the point of insertion

i would call your method hugely inefficient

Ah I see. Makes much better sense, thanks for explaining it. I'll be putting that method in my own toolbox

A couple of things though:

1, Why do you assume that floating point numbers are acceptable? The whole number position may be a requirement from the front end, and this would be lost using your method.

2, If the positions are not important as long as they are in sequence, then there is no need to "re-sequence" the table, just 2 row updates.

Is it possible, with your method that items could get "stuck" between fractions of the whole numbers say: 5.0625 and 5.125 where the room for reordering quickly drops off... just a thought.

The "whole number" position can be calculated on the fly using windowed functions if required. I don't know the MySQL equivalent, but in SQL Server we'd use Row_Number() OVER (ORDER BY our_floating_sequence_column)

Try knock up an example illustrating your second point. Include a dozen rows or so and then "move" the top row to the middle.... I think you'll find that more than two rows are updated when using the integer sequencing method.

As for the "getting stuck" point; we don't have to start at 0 and 1, we could just as easily factor up: start at 1M and 0 - that should give us a few more levels of precision. Also, I believe float can have a precision of up to 15 decimal places, so we can go a long way down too.

then i would shoot the front end designer, as she has no business dictating how the back end should be implemented

but there is so a need

suppose you had whole number sequences, with the following data --

3 curly
4 larry
5 moe
6 shemp
7 joe
8 curly joe

now move curly joe in between curly and larry

still think you can do it with just two updates?


i think perhaps you need to perform a test to convince yourself exactly what "quickly drops off" really means

in theoretical terms, the room ~never~ drops off, because you can ~always~ find the average between two floating point numbers

in practical terms, there may be a limit based on the precision of the floating point numbers as constrained by the hardware...

... and i shall leave it to you as an exercise to determine just exactly how many times you can do it

...But it affects 5/6 of the rows
I think that's where some of the confusion is here. It's not the number of statements, it is the number of rows affected.

thanks for emphasizing this

that's what i had in mind when i said you'd have to re-sequence the entire table above the point of insertion

it's not the number of update statements, it's the number of rows affected

using whole sequence numbers is, in my mind, a non-starter

I find overheads like this equivalent to the programming of: for(i=0; i<str.len; i++). A little extra work before hand can save a lot of cpu cycles and hard drive rattling down the road. On the other hand, if you have good caching in place then this may not be an issue.

I see where you and r937 are going. I was focused on simple up and down movements as opposed to drag and drop which would require set resequencing. In the case of the latter I would update every single item with it's new position as seen from the front end.

I would never consider such an arbitrary solution as that. Plus there is a limit, no matter how far away it seems, there is a limit. Off course, you could say that the integer value in and off itself is also a limit, but I'd rather take my chances with that than dwindling fractions limited by the databases/cpu level of precision on floating point numbers