CODEWITHSUNDEEP
mysqlsql
Alexis King
Alexis King

Reputation: 43852

How can I store user-ordered data in a MySQL database?

In a particular application, a user is able to store widgets in a favorites list. Users and widgets each have their own IDs, so I devised a Favorites table to store users' favorites:

+-----------+------------------+
|   Field   |       Type       |
+-----------+------------------+
| userid    | int(11) unsigned |
| widgetid  | int(11) unsigned |
| dateadded | int(11) unsigned |
+-----------+------------------+

This is all well and good, but now I want the user to be able to manually reorder the widgets in their favorites. Suddenly, the dateadded column is unhelpful, since updating it would only allow a user to bump a widget to the front of the list.

Therefore, it would seem I need an index column instead:

+-----------+------------------+
|   Field   |       Type       |
+-----------+------------------+
| userid    | int(11) unsigned |
| widgetid  | int(11) unsigned |
| index     | int(11) unsigned |
+-----------+------------------+

This way, to reorder columns, I can just manipulate the DB table like an array—pull out a row, renumber the others to "free" a slot, and reinsert the row. For example, to move the object at index 13 to index 5, I would do the following:

SELECT * FROM Favorites WHERE `index` = 13;
DELETE * FROM Favorites WHERE `index` = 13;

UPDATE Favorites
SET `index` = `index` + 1
WHERE `index` >= 5 AND `index` < 13;

INSERT INTO Favorites (userid, widgetid, index) VALUES (<data from select>, 5);

However, this feels extremely hacky and very un-SQL, and I can't imagine the performance would be fantastic, either.

What is a "proper" solution to storing arbitrarily-reorderable data in a MySQL database?

Upvotes: 2

Views: 369

Answers (1)

StanislavL
StanislavL

Reputation: 57401

I would use index values with some step e.g. 10, 20, 30, 40.

Then when I would like to move index 40 to be between 10 and 20 I first update the index to be 15 where index=40. And then update all the user's records once more restoring the 10, 20, 30, 40 order based on the select below.

select f.*, 
       @new_index:=@new_index+10 as new_index
from Favorites f, (select @new_index:=0) as sess_var
where f.user_id=:theUserId
order by f.index

The query will return you all the old records with new values for the index. Adapt it to be used in your second update.

UPDATE:

update Favorites f
      join (the query above) sub on
    f.user_id = sub.user_id and f.widget_id= sub.widget_id
set index=sub.new_index

Upvotes: 1

Related Questions