CODEWITHSUNDEEP
phpmysqldatabasesortingrelational
Steve
Steve

Reputation: 6424

Completely arbitrary sort order in MySQL with PHP

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:

  1. 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.
  2. 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.
  3. 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.

Upvotes: 2

Views: 555

Answers (2)

Val Kornea
Val Kornea

Reputation: 4717

The simplest expression of an ordered list is: 3,1,2,4. We can store this as a string in the parent table; so if our table is photos with the foreign key profile_id, we'd place our photo order in profiles.photo_order. We can then consider this field in our order by clause by utilizing the find_in_set() function. This requires either two queries or a join. I use two queries but the join is more interesting, so here it is:

select photos.photo_id, photos.caption
from photos
    join profiles on profiles.profile_id = photos.profile_id
where photos.profile_id = 1
order by find_in_set(photos.photo_id, profiles.photo_order);

Note that you would probably not want to use find_in_set() in a where clause due to performance implications, but in an order by clause, there are few enough results to make this fast.

Upvotes: 0

Aldo 'xoen' Giambelluca
Aldo 'xoen' Giambelluca

Reputation: 12445

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.

Upvotes: 2

Related Questions