How to store orders?

Question

I have an application that has tasks, and you can reorder them. Now I was looking for the best way to store them. Should I have colomn for the order number and recount all of them every time I change it? Please tell me a version that does not require me to update all order numbers, as it is very time-consuming (in terms of execution).

This is especially bad if I have to put one that is at the very top of the order, and then drag it down.

• Name (serial number)

-

• 1 Example (1)
• 2 Example (2)
• 3 Example (3)
• 4 Example (4)
• 5 Example (5)

-

• 2 Example (1) *
• 3 Example (2) *
• 4 Example (3) *
• 5 Example (4) *
• 1 Example (5) *

* must be changed in the database

also some tasks may be deleted due to their completion

Answer 1

You can store orders as literals and use lexical sorting:

1. A 2. Z 

Add task:

 1. A 3. L 2. Z 

Add:

 1. A 4. B 3. L 2. Z 

Move 2 between 1 and 4:

 1. A 2. AL 4. B 3. L 

and etc.

You only update one record at a time: just take the middle letter between the first, which is different: if you put between A and C , you take B , if you put between ALGJ and ALILFG , you take ALH .

The letter next to existing counters as existing, combined with next to Z I. e. if you need to put between ABHDFG and ACSD F, you consider it as between ABH and AB(Z+) , and write AB(letter 35/2) , that is, ABP .

If you run out of line length, you can always do the full order.

Update:

You can also save your data as a linked list.

See a blog post on how to do this in MySQL :

• Sort Lists

In a nutshell:

 /* This just returns all records in no particular order */ SELECT * FROM t_list id parent ------- -------- 1 0 2 3 3 4 4 1 /* This returns all records in intended order */ SELECT @r AS _current, @r := ( SELECT id FROM t_list WHERE parent = _current ) FROM ( SELECT @r := 0 ) vars, t_list _current id ------- -------- 0 1 1 4 4 3 3 2 

When moving items, you need to update no more than 4 lines.

This is apparently the most efficient way to maintain an ordered list that is updated frequently.

Answer 2

Usually I add an int or smallint column with the name "Ordinal" or "PositionOrdinal", as you suggest, and with an exact warning you indicate - the need to update a potentially significant number of records each time you re-order one record.

The advantage is that taking into account the key for a specific task and the new position for this task, the code for moving an element is just two operators:

 UPDATE `Tasks` SET Ordinal= Ordinal+1 WHERE Ordinal> =@NewPosition UPDATE `Tasks` SET Ordinal= @NewPosition WHERE TaskID= @TaskID 

There are other suggestions for a doubly linked list or lexical order. Or it can be faster, but at the cost of much more complex code, and performance will only matter if there is a lot of elements in the same group.

Whether performance or complexity of the code is important will depend on your situation. If you have millions of records, the added complexity may be worth it. However, I usually prefer simpler code, because users usually order only small lists manually. If there are not many items in the list, additional updates will not matter. This can usually handle thousands of records without a noticeable impact on performance.

The only thing to remember in your updated example is that the column is used only for sorting, and not for others that are displayed directly to the user. Thus, when dragging an item from top to bottom, as shown, the only thing you need to change is a single entry. It does not matter that you leave the first position empty. This means that there is little potential for overflowing integer sorting with sufficient re-ordering, but let me say it again: users usually only order small lists manually. I have never heard of this risk actually causing a problem.

Answer 3

From your answers, I came up with a mixture that looks like this:

Let's say that we have:

• 1 Example (1)
• 2 Example (2)
• 3 Example (3)
• 4 Example (4)
• 5 Example (5)

Now, if I sort something between 4 and 5, it will look like this:

• 2 Example (2)
• 3 Example (3)
• 4 Example (4)
• 1 Example (4.5)
• 5 Example (5)

now again something between 1 and 5

• 3 Example (3)
• 4 Example (4)
• 1 Example (4.5)
• 2 Example (4.75)
• 5 Example (5)

he will always take half the difference between the numbers

I hope this works, please correct me;)

Answer 4

We do this with the Sequence column in the database.

We use sparse numbering (for example, 10, 20, 30, ...), so we can "insert" one of the existing values. If adjacent lines have consecutive numbers, we will renumber the minimum number of lines that we can.

You can probably use decimal numbers - take the average number of sequences for the lines next to where you are inserting, then you only need to update the line "moved"

Answer 5

This is not an easy task. If you have a small number of items to sort, I would just reset all of them in their new order.

Otherwise, it seems that it will take as much or more to “check and install” to change only the changed entries.

You can delegate this work on the client side. Ask the client to keep the old order and sort order and determine which line [sort order] should be updated, then passes these tuples to the PHP-mySQL interface.

You can improve this method as follows (does not require floats):

• If all sorted items in the list are initialized by the sort order according to their position in the list, set the sort order of each item approximately as row [sort-order] = row [sort-order * K], where K is some number> average number of times, when you expect the list to be reordered. O (N), N = number of elements, but increases throughput by at least N * K with at least K open slots between each outgoing pair of elements.

• Then, if you want to insert an element between two others, it is as simple as changing its sort order as one, which is> lower element and <upper. If there is no “room” between the elements, you can simply reapply the “spread” algorithm (1) presented in the previous paragraph. The larger K, the less often it will be applied.

The K algorithm will be selectively applied in a PHP script, while the selection of a new sort order will be performed by the client (possibly Javascript).

Answer 6

I would recommend having an order column in the database. When an object is reordered, replace the order value in the database between the object you changed and objects that have the same order value, so you don’t have to re-encode the entire set of rows.

Hope this makes sense ... of course, it depends on your reordering rules.