Creating a linked list or similar queue in MySQL?

Question

I have a table of items that need to be displayed in a certain order, but that order can be changed. Items can be added at the beginning, end, or in the middle, and items can be rearranged. How can I set up the table to keep track of that order in such a way that it's easy to modify but the list can also be fetched in order with a single query?

For example, I could have a "NEXT_ID" column to do it linked list-style, but then how would I run a SELECT query to get the rows in order of the NEXT_ID chain?

Apologies in advance for the super-obvious solution I'm probably missing.

Answer 1

I have this problem often, and I solved it with a simple solution : an extra column called Sort Order (or DisplayOrder, whatever floats your boat really) . This allows me the flexibility to use auto-generated, auto-incremented ID column and have a special pre-defined sort.

In my case, I need them to come out of the database with an alphabetical order except that some items like "Other" and "N/A" are always last.

ProdID ProdText SortOrder
2      "Anchovies"    1
3      "Rivet"        2
4      "N/A"          4
5      "Other"        3

SELECT ProdID, ProdText ORDER BY Sort Order

Answer 2

Create a column in the table that represents the sort order. Put an index on this column so that the MySQL engine can retrieve based on this column quickly. When you change the order, update the values in this field for all records to keep it consistent.

For example, when you insert a new record in the middle:

UPDATE table SET sort_order = sort_order + 1 WHERE sort_order >= 5;
INSERT INTO table (sort_order, column1, column2) VALUES (5, 'value1', 'value2');

Something more complicated, like moving #3 down to #6 and sliding all others up:

UPDATE table
SET sort_order = Case sort_order When 3 Then 6 Else sort_order - 1 End
WHERE sort_order BETWEEN 3 AND 6;

Answer 3

Unless I am misunderstanding what you are looking for it would seem you could just add a DISPLAY_ORDER column that is a numeric index of how things should be returned. This can easily be changed and rearranged. Plus value can be used in order by.