Implement Editable Order of Items in MySQL

Question

I would like to show a table of items, where user can add, delete and reorder the items. The first way I thought to store these items in MySQL DB was to have an order column in the table, but it seems pretty inefficient because when user deletes an item, I need to update the order of all the following items. And if I just use ORDER BY with id or something, I don't know how to let user reorder the items as they want. What do you think is the best way to handle this?

The stack I use: React JS -> Node JS / Express -> MySQL

Answer 1

A way I have seen is to have a sperate column which states the ID of the item it is behind. Then in your Query you can order from that column when selecting to make it display in that order.

This means that if you delete a field, you only have to update the one behind it to then state its behind the one ahead of what you just deleted (That was a jumple of words...)

Does this help?

Answer 2

You'll definitely need a new column to do this ordering. It requires the storage of more information than you can represent with just the item ID, name, and all that.

I have done this successfully with the kind of order column you mention. Here are some tricks I used.

• Don't worry about what happens when you delete a row. Things work just fine if you have gaps in the values of your order column.

• Use the same data type for your order column as your autoincrementing id column (BIGINT UNSIGNED probably) but allow NULL values.

• Use ORDER BY COALESCE(order, id * 1000), id so you'll use the id value for ordering newly inserted rows that have never had their order set. New rows come at the end. Intutitive enough default behavior.

• To put item with id a right after the item with id b, update the order value in row a to COALESCE(b.order, id * 1000) + 1.

Like this

id    name    order   COALESCE(order, id*1000)
1     orange  NULL    1000
2     banana  NULL    2000
3     apple   NULL    3000
4     fig     NULL    4000

This makes your order values be 1000, 2000, 3000, 4000 and so forth. To order the fourth item right after the second item you'll set its order to 2001. Like this

id    name    order   COALESCE(order, id*1000)
1     orange  NULL    1000
2     banana  NULL    2000
4     fig     2001    2001
3     apple   NULL    3000

If you do enough reordering that you insert more than 1000 items after any given item, you'll have to clean up your order column by rewriting it for the whole table. It's OK if you have some identical order values. The ORDER BY clause I suggested will keep them in their original ID order.