Page 1 of 6 123 ... LastLast
Results 1 to 15 of 76
  1. #1
    Join Date
    Jul 2010
    Posts
    1

    Unanswered: How to deal with order numbers?

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tim toady View Post
    What is the best way to go about updating the order field in this type of situation?
    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

    Quote Originally Posted by tim toady View Post
    Is there a better way to keep track of order?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2010
    Posts
    2
    What I don't understand: Does your order number need to be constant, or can it be changed?

  4. #4
    Join Date
    Jul 2010
    Posts
    34
    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.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    overkill? really?

    so what would you advise instead, andrew?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2010
    Posts
    34
    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.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  8. #8
    Join Date
    Jul 2010
    Posts
    34
    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..

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Andrew Rose View Post
    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.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jul 2010
    Posts
    34
    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.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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.
    George
    Home | Blog

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Andrew Rose View Post
    The whole number position may be a requirement from the front end
    then i would shoot the front end designer, as she has no business dictating how the back end should be implemented

    Quote Originally Posted by Andrew Rose View Post
    then there is no need to "re-sequence" the table, just 2 row updates.
    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?


    Quote Originally Posted by Andrew Rose View Post
    5.0625 and 5.125 where the room for reordering quickly drops off... just a thought.
    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


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by r937 View Post
    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?
    Code:
    -- Something like this, perhaps
    UPDATE da_table
    SET    seq = CASE WHEN seq = 8 THEN 4 ELSE seq + 1 END
    WHERE  seq >= 4
    ...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.
    George
    Home | Blog

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gvee View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jul 2010
    Posts
    34
    Quote Originally Posted by gvee View Post
    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)
    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.

    Quote Originally Posted by gvee View Post
    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.
    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.

    Quote Originally Posted by gvee View Post
    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.
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •