Efficiently implement drag-and-drop sorting of product lists: the minimum cost solution

The drag-and-drop sorting function of front-end product lists, especially when supporting cross-page sorting, requires clever database design and algorithms to ensure efficiency. This article introduces a low-cost and high-efficiency solution without major modification of product addition and modification logic.

Assuming that the sort field already exists in the database, the initial value is 0, and the product list is arranged in reverse order of time. Our goal is to update sort value through drag and drop operations to achieve sorting.

Initialize sort field:

First, we need to initialize the sort field, assign a sort value to each item, and reserve enough space to avoid conflicts. We can use the following SQL statement to assign an incremental sort value to each product and set a larger interval (for example, 1000):

 SET @sort := 0;
UPDATE product SET sort = (@sort := @sort 1000) ORDER BY id;

For example, the initial data might be as follows:

Drag and drop sorting algorithm:

When a user drags a product, we only need to update sort value of the moved product and the affected product. Assuming that the user moves the item 3 between the item 1 and the item 2, we can calculate the new sort value of the item 3:

新sort 值= 商品1的sort 值(商品2的sort 值- 商品1的sort 值) / 2

For example, the new sort value for item 3 would be: 1000 (2000 - 1000) / 2 = 1500

To avoid being too concentrated in sorting values, you can add a small number of random numbers when calculating intermediate values. The updated data is as follows:

Avoid too dense sorting values:

As the number of drag operations increases, the sorting values ??may be too dense, making subsequent sorting difficult. To solve this problem, we can reassign the sort value periodically to maintain sufficient intervals. The following SQL statements can be used:

 SET @sort := 0;
UPDATE product SET sort = (@sort := @sort 1000) ORDER BY sort;

This method ensures that there is enough gap between the sort values ??by reordering and assigning sort values, thus ensuring the long-term effectiveness of the sort algorithm.

Summarize:

Through the above methods, we can realize the drag-and-drop sorting function of the product list at the minimum cost, and maintain the long-term stability of the sorting algorithm without significantly modifying the logic of product addition and modification. This method uses the reserved sorting value space and regular reallocation mechanism to effectively solve the problem of sorting value dense and improve sorting efficiency.

The above is the detailed content of How to implement drag-sorting function of product list at a minimum cost?. For more information, please follow other related articles on the PHP Chinese website!