Menu
Suppose I have some objects, and I want the user to be able to reorder them in any way they wish, say, by dragging them around. So I'd have
And then the user drags 'milk' to the top, making the new order
Is there a best practice how to store the order of these objects in a database? The naive approach would probably be to just store a numerical value called "order" for each object, but this seems like too much hassle to me, because you'd have to shuffle the order-values around most of the time.
677
An "ALTER TABLE ORDER BY" statement exist in the syntaxes accepted by MySQL. According to the documentation, this syntax: - only accept *one* column, as in "ALTER TABLE t ORDER BY col;" - is used to reorder physically the rows in a table, for optimizations.
Sorting helps to sort the records that are retrieved. By default, it displays the records in ascending order of primary key. If we need to sort it based on different columns, then we need to specify it in ORDER BY clause. If we need to order by descending order, then DESC keyword has to be added after the column list.
The best way I've found to handle this is to have a floating point order field. When you move something between two other items, set that field to halfway between its neighbors.
This is cheap on both reads and writes. The only downside is the floats keep getting longer :)
169
The "naive" approach you suggest is also the best practice!
28
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
