Menu
I was wondering if anyone has a good solution to a problem I've encountered numerous times during the last years.
I have a shopping cart and my customer explicitly requests that it's order is significant. So I need to persist the order to the DB.
The obvious way would be to simply insert some OrderField where I would assign the number 0 to N and sort it that way.
But doing so would make reordering harder and I somehow feel that this solution is kinda fragile and will come back at me some day.
(I use C# 3,5 with NHibernate and SQL Server 2005)
Thank you
960
A better solution is to create an array of your elements IDs in order and turn that into a json array and store it as it is into a file or a table or wherever you want. You can then fetch your objects from the database and map your array back to a list of elements.
You can change the order of the rows by adding an ORDER BY clause at the end of your query, with a column name after. By default, the ordering will be in "ascending order", from lowest value to highest value. To change that to "descending order", specify DESC after the column name.
Decimal SortOrder column, but with only enough precision to store 0.5 difference (i.e. Decimal (8,2) or something). When sorting, grab the PKs of the row above and below where the current row was just moved to, if they exist. (You wont have a row above if you move the item to the first position, for example)
A column called order, which is an integer. When a song is moved, the order of all songs between its old and new position are changed, to reflect the change. The drawback of this is that a lot of queries need to be done each time a song is moved, and the moving algorithm is not as trivial as with the other options.
By design, it will NOT incur "a whole lot of other items in the list". However, this requires the client-side (a web service or perhaps a mobile app) to implement the linked-list travesal logic to derive the order. Some variation does not use reference i.e. linked list.
Their "rank" (what you call index) is a string value that allows a ton of breathing room in between ranked items.
Ok here is my solution to make programming this easier for anyone that happens along to this thread. the trick is being able to update all the order indexes above or below an insert / deletion in one update.
Using a numeric (integer) column in your table, supported by the SQL queries
CREATE TABLE myitems (Myitem TEXT, id INTEGER PRIMARY KEY, orderindex NUMERIC); To delete the item at orderindex 6:
DELETE FROM myitems WHERE orderindex=6; UPDATE myitems SET orderindex = (orderindex - 1) WHERE orderindex > 6; To swap two items (4 and 7):
UPDATE myitems SET orderindex = 0 WHERE orderindex = 4; UPDATE myitems SET orderindex = 4 WHERE orderindex = 7; UPDATE myitems SET orderindex = 7 WHERE orderindex = 0; i.e. 0 is not used, so use a it as a dummy to avoid having an ambiguous item.
To insert at 3:
UPDATE myitems SET orderindex = (orderindex + 1) WHERE orderindex > 2; INSERT INTO myitems (Myitem,orderindex) values ("MytxtitemHere",3) Best solution is a Doubly Linked list. O(1) for all operations except indexing. Nothing can index SQL quickly though except a where clause on the item you want.
0,10,20 types fail. Sequence column ones fail. Float sequence column fails at group moves.
Doubly Linked list is same operations for addition, removal, group deletion, group addition, group move. Single linked list works ok too. Double linked is better with SQL in my opinion though. Single linked list requires you to have the entire list.
33
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
