[ACCEPTED]-Best way to save a ordered List to the Database while keeping the ordering-sql-order-by

Ok here is my solution to make programming 9 this easier for anyone that happens along 8 to this thread. the trick is being able 7 to update all the order indexes above or 6 below an insert / deletion in one update.

Using 5 a numeric (integer) column in your table, supported 4 by the SQL queries

CREATE TABLE myitems (Myitem TEXT, id INTEGER PRIMARY KEY, orderindex NUMERIC);

To delete the item at 3 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 2 is not used, so use a it as a dummy to avoid 1 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 11 except indexing. Nothing can index SQL quickly 10 though except a where clause on the item 9 you want.

0,10,20 types fail. Sequence column 8 ones fail. Float sequence column fails at 7 group moves.

Doubly Linked list is same 6 operations for addition, removal, group 5 deletion, group addition, group move. Single 4 linked list works ok too. Double linked 3 is better with SQL in my opinion though. Single 2 linked list requires you to have the entire 1 list.

FWIW, I think the way you suggest (i.e. committing 3 the order to the database) is not a bad 2 solution to your problem. I also think it's 1 probably the safest/most reliable way.

How about using a linked list implementation? Having 4 one column the will hold the value (order 3 number) of the next item. I think it's by 2 far the easiest to use when doing insertion 1 of orders in between. No need to renumber.

Unfortunately there is no magic bullet for 13 this. You cannot guarentee the order of 12 any SELECT statement WITHOUT an order by clause. You 11 need to add the column and program around 10 it.

I don't know that I'd recommend adding 9 gaps in the order sequence, depending on 8 the size of your lists and the hits on the 7 site, you might gain very little for the 6 over head of handling the logic (you'd still 5 need to cater for the occasion where all 4 the gaps have been used up). I'd take a 3 close look to see what benifits this would 2 give you in your situation.

Sorry I can't 1 offer anything better, Hope this helped.

I wouldn't recommend the A, AA, B, BA, BB 16 approach at all. There's a lot of extra 15 processing involved to determine hierarchy 14 and inserting entries in between is not 13 fun at all.

Just add an OrderField, integer. Don't 12 use gaps, because then you have to either 11 work with a non-standard 'step' on your 10 next middle insert, or you will have to 9 resynchronize your list first, then add 8 a new entry.

Having 0...N is easy to reorder, and 7 if you can use Array methods or List methods 6 outside of SQL to re-order the collection 5 as a whole, then update each entry, or you 4 can figure out where you are inserting into, and 3 +1 or -1 each entry after or before it accordingly.

Once 2 you have a little library written for it, it'll 1 be a piece of cake.

I would just insert an order field. Its 13 the simplest way. If the customer can reorder 12 the fields or you need to insert in the 11 middle then just rewrite the order fields 10 for all items in that batch.

If down the 9 line you find this limiting due to poor 8 performance on inserts and updates then 7 it is possible to use a varchar field rather 6 than an integer. This allows for quite a 5 high level of precision when inserting. eg 4 to insert between items 'A' and 'B' you 3 can insert an item ordered as 'AA'. This 2 is almost certainly overkill for a shopping 1 cart though.

On a level of abstraction above the cart 17 Items let's say CartOrder (that has 1-n 16 with CartItem) you can maintain a field 15 called itemOrder which could be just a comma 14 - separated list of id(PK) of cartItem records 13 relevant . It will be at application layer 12 that you require to parse that and arrange 11 your item models accordingly . The big plus 10 for this approach will be in case of order 9 reshufflings , there might not be changes 8 on individual objects but since order is 7 persisted as an index field inside the order 6 item table rows you will have to issue an 5 update command for each one of the rows 4 updating their index field. Please let 3 me know your criticisms on this approach, i 2 am curious to know in which ways this might 1 fail.

I solved it pragmatically like this:

1. The order is defined 6 in the UI.

2. The backend gets a POST request 5 that contains the IDs and the corresponding 4 Position of every item in the list.

3. I start 3 a transaction and update the position for 2 every ID.

Done.

So ordering is expensive but reading 1 the ordered list is super cheap.

I would recommend keeping gaps in the order 4 number, so instead of 1,2,3 etc, use 10,20,30... If 3 you need to just insert one more item, you 2 could put it at 15, rather than reordering 1 everything at that point.

Well, I would say the short answer is:

Create 11 a primary key of autoidentity in the cartcontents 10 table, then insert rows in the correct top-down 9 order. Then by selecting from the table 8 with order by the primary key autoidentity 7 column would give you the same list. By 6 doing this you have to delete all items 5 and reinsert then in case of alterations 4 to the cart contents. (But that is still 3 quite a clean way of doing it) If that's 2 not feasible, then go with the order column 1 like suggested by others.