Specific ordering of records based on list of IDs? with MySQL & PHP

Question

I have a 'user' table in MySQL

When users login to my app, they can see a list of all other users and via some javascript-drag-drop can arrange which order the other users appear in. We want to save that order.

And it is saved to their record (i.e. 'order_pref' = 4,3,5,23... list of user_ids in the order they specified).

So that's fine but when it comes to displaying.. Is there an easy way I can sort based on this comma delimited list of user_ids using just one query?

Answer 1

You can't in MySQL. Conceivably you can in PHP but this is not the best way to model this problem.

You should assign an order number and then sort by that rather than storing the IDs in a fixed order. Assuming:

• The logged in user is 302
• The list of user IDs are 11, 432, 788, 343

You store:

UserID  OrderNum  UserDisplay
 302      100       11
 302      200      432
 302      300      788
 302      400      343

And then the problem becomes:

SELECT UserDisplay
FROM UserOrder
WHERE UserID = 302
ORDER BY OrderNum

Much simpler. Now changing the order requires updating the OrderNum field. That's why I didn't do steps of 1. Instead I did steps of 100 so if you want to move 343 to second place just set the OrderNum to 150. You could probably do much larger intervals (eg 1000000) and may need to very occasionally renumber those values.

Answer 2

There's a few ways you can do this, best case is to do it with a second table for user-specific ordering. Adding a single column to the User table won't work as presumably each user will have their own ordering.

Depending on the scale and complexity of your app, you can do this with a single table (more managable, but slower with millions of users), or create tables for each user (more scalable). In the following examples, the current user is user # 2, the ordering is 4,3,5,23.

create table if not exists user_ordering (
  owner_id integer unsigned not null,
  user_id integer unsigned not null,
  order_index integer not null default '0',
  unique owner_user (user_id,owner_id),
  index owner_idx (owner_id,order_index)
) ENGINE=MyISAM;

To save an ordering, insert sequentially:

INSERT INTO user_ordering (owner_id,user_id,order_index) VALUES (2,4,0);
INSERT INTO user_ordering (owner_id,user_id,order_index) VALUES (2,3,1);
INSERT INTO user_ordering (owner_id,user_id,order_index) VALUES (2,5,2);
INSERT INTO user_ordering (owner_id,user_id,order_index) VALUES (2,23,3);

To select, just join and use a "default" sort for users not in the list:

SELECT U.* FROM user U
LEFT OUTER JOIN user_ordering G ON U.id=G.user_id AND G.owner_id=2
ORDER BY G.order_index,U.name;

If you want to save some time you can use a double-primary key with auto incrementing to cause incrementing to happen on a "owner-by-owner" basis, e.g.

create table if not exists user_ordering (
  owner_id integer unsigned not null,
  user_id integer unsigned not null,
  order_index integer not null AUTO_INCREMENT,
  unique owner_user (user_id,owner_id),
  PRIMARY KEY (owner_id,order_index)
) ENGINE=MyISAM;

If you are given a list to update, you could do the quick-and-dirty:

DELETE FROM user_ordering WHERE owner_id=2;
INSERT INTO user_ordering (owner_id,user_id) VALUES (2,4);
INSERT INTO user_ordering (owner_id,user_id) VALUES (2,3);
INSERT INTO user_ordering (owner_id,user_id) VALUES (2,5);
INSERT INTO user_ordering (owner_id,user_id) VALUES (2,23);

You may even be able to use the multiple-insert version:

DELETE FROM user_ordering WHERE owner_id=2;
INSERT INTO user_ordering (owner_id,user_id) VALUES (2,4),(2,3),(2,5),(2,23);

For massive databases, you may do better to create a single table per user, like:

create table if not exists user_ordering_2 (
  user_id integer unsigned not null,
  order_index integer not null AUTO_INCREMENT,
  unique owner_user (user_id),
  PRIMARY KEY (order_index)
) ENGINE=MyISAM;

This requires somewhat more advanced tools for keeping your schema up to date, but it would scale better.