I have a table in MySQL that I’m accessing from PHP. For example, let’s have a table named THINGS:
things.ID – int primary key
things.name – varchar
things.owner_ID – int for joining with another table
My select statement to get what I need might look like:
SELECT * FROM things WHERE owner_ID = 99;
Pretty straightforward. Now, I’d like users to be able to specify a completely arbitrary order for the items returned from this query. The list will be displayed, they can then click an “up” or “down” button next to a row and have it moved up or down the list, or possibly a drag-and-drop operation to move it to anywhere else. I’d like this order to be saved in the database (same or other table). The custom order would be unique for the set of rows for each owner_ID.
I’ve searched for ways to provide this ordering without luck. I’ve thought of a few ways to implement this, but help me fill in the final option:
- Add an INT column and set it’s value to whatever I need to get rows returned in my order. This presents the problem of scanning row-by-row to find the insertion point, and possibly needing to update the preceding/following rows sort column.
- Having a “next” and “previous” column, implementing a linked list. Once I find my place, I’ll just have to update max 2 rows to insert the row. But this requires scanning for the location from row #1.
- Some SQL/relational DB trick I’m unaware of…
I’m looking for an answer to #3 because it may be out there, who knows. Plus, I’d like to offload as much as I can on the database.
Advertisement
Answer
From what I’ve read you need a new table containing the ordering of each user, say it’s called *user_orderings*.
This table should contain the user ID, the position of the thing and the ID of the thing. The (user_id, thing_id) should be the PK. This way you need to update this table every time but you can get the things for a user in the order he/she wants using ORDER BY on the user_orderings table and joining it with the things table. It should work.