The best way to keep an ordered list in a database while keeping order

Question

I was wondering if anyone has a good solution to the problem that I have encountered many times over the past years.

I have a shopping basket, and my customer is clearly asking for the order to be significant. Therefore, I need to save the order in the database.

The obvious way would be to simply insert some OrderField where I would assign a number from 0 to N and sort it that way.

But that will make reordering more difficult, and somehow I will feel that this decision is rather fragile and will someday come back to me.

(I am using C # 3.5 with NHibernate and SQL Server 2005)

thank

Answer 1

FWIW, I think the way you propose (i.e. making an order in a database) is not a bad solution to your problem. I also think this is the safest / most reliable way.

Answer 2

Well, here is my decision to make programming easier for those who are going on with this thread. The trick can update all order indices above or below insert / delete in one update.

Using a numeric (integer) column in your table supported by SQL queries

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

To remove an item in 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 it as a mannequin to avoid an ambiguous element.

Paste in 3:

  UPDATE myitems SET orderindex = (orderindex + 1) WHERE orderindex > 2; INSERT INTO myitems (Myitem,orderindex) values ("MytxtitemHere",3) 

Answer 3

The best solution is the Doubly Linked list . O (1) for all operations except indexing. Nothing can index SQL quickly, but with the exception of the where clause for the desired item.

0,10,20 types fail. The column sequence is not working. Floating-column column crashes when moving a group

Doubly Linked list - these are the same operations for adding, deleting, deleting a group, adding a group, moving a group. A single list of links also works. In my opinion, a double join is better with SQL. One linked list requires you to have the whole list.

Answer 4

How to use linked list implementation? If there is one column, the value (order number) of the next element will be held. I think it is much easier to use when inserting orders between them. No need to change the numbering.

Answer 5

Unfortunately, there is no magic bullet for this. You cannot guarantee the order of any SELECT WITHOUT order by. You need to add the column and the program around it.

I don’t know that I would recommend adding spaces in the order sequence, depending on the size of your lists and hits on the site, you could get very little for most of the processing of logic (you, d still need to consider the case where all spaces have been exhausted). I would carefully look at what advantages this will give you in your situation.

Sorry, I can not offer anything better, I hope this helped.

Answer 6

I would not recommend the approach of A, AA, B, BA, BB in general. There is a lot of additional processing to determine the hierarchy and insert records between them, not at all funny.

Just add OrderField, integer. Do not use spaces, because then you will either have to work with a non-standard "step" on your next middle insert, or first you need to re-synchronize your list, and then add a new entry.

Having 0 ... N is easy to reorder, and if you can use Array methods or List methods outside SQL to re-order the collection as a whole, update each record or you can determine where you are inserting and +1 or -1 each recording after or after it, respectively.

Once you have a small library written for him, it will be a piece of cake.

Answer 7

I would just enter the order field. This is the easiest way. If the client can change the order of the fields or you need to insert them in the middle, simply rewrite the order fields for all elements of this batch.

If along the line you find this limitation due to poor performance during insertions and updates, then you can use the varchar field, rather than an integer. This allows a fairly high level of accuracy when pasting. for example, to insert between elements "A" and "B", you can insert an element ordered as "AA". This is almost certainly unnecessary for a shopping cart.

Answer 8

At the level of abstraction above the basket. The elements make it possible to claim that CartOrder (which has 1-n with CartItem), you can maintain a field called itemOrder, which can only be a list of identifiers (PKs) of CartItem data, separated by commas. This will be at the application level that you need to parse and organize product models. A big plus for this approach will be in the case of rearrangement of orders, there can be no changes on individual objects, but since the order is saved as an index field inside the rows of the table of order items, you will have to issue an update command for each row that updates their index field. Please let me know your criticisms of this approach, I am curious to know how this can fail.

Answer 9

I solved it pragmatically as follows:

• The order is defined in the user interface.

• The backend receives a POST request containing identifiers and the corresponding position of each item in the list.

• I start the transaction and update the position for each identifier.

Done.

Therefore, ordering is expensive, but reading an ordered list is super cheap.

Answer 10

I would recommend storing spaces in the order number, so instead of 1,2,3, etc. use 10,20,30 ... If you just need to insert another element, you can put it at 15, and then reorder everything at that moment.

Answer 11

Ok, I would say the short answer is:

Create an autoidentity primary key in the basket table, then insert the rows in the correct order from top to bottom. Then, choosing the order from the table using the primary key auto-identification column, you will get the same list. In doing this, you must remove all items and reinsert, and then in case of changes to the contents of the basket. (But this is still a pretty clean way to do this) If this is not possible, go to the order column, as suggested by others.

Answer 12

When I use Hibernate and you need to keep @OneToMany order, I use Map , not List .

 @OneToMany(fetch = FetchType.EAGER, mappedBy = "rule", cascade = CascadeType.ALL) @MapKey(name = "position") @OrderBy("position") private Map<Integer, RuleAction> actions = LazyMap.decorate(new LinkedHashMap<>(), FactoryUtils.instantiateFactory(RuleAction.class, new Class[] { Rule.class }, new Object[] { this })); 

In this example, Java position is an Integer RuleAction property, so the order is preserved this way. I think in C # it will look pretty similar.