CODEWITHSUNDEEP
mysql
Sam
Sam

Reputation: 6552

Sort column in MySQL for user defined order

I'm needing to be able to give users the ability to sort their data. The schema I have is like this: http://sqlfiddle.com/#!2/48100

I want to add a sort column to this table. The sort column should be an integer with a value starting at 1 for each set of giverUserId. If, however, there are duplicate giverUserId+receiverUserId combinations, they must maintain the same sort value.

Therefore, if I have rows A, B, C, and D with the giverUserId = 23 and receiverUserId = 11, 13, 17 and 13, respectively, then the sort values for each row should be 1, 2, 3, 2, respectively. If I insert a new row E with giverUserId = 23 and receiverUserId = 13, then it's sort value should be 2 as well. This way, I can retrieve my results set with the specific order by ordering on the sort column: A, B, D, E, C. Even though both D and E have a more recent timestamp, the come before C because of the sort column.

A user could modify a row's sort value by updating all rows where giverUserId = x, and receiverUserId = y. I'd also increment/decrement rows that become misplaced for the sort change of a specific row. I got this part pretty much figured out.

What I don't have figured out, is how to maintain a sort column like this. Should I use auto_increment, or is there some other special MySQL like things that could help me with what I'm trying to accomplish? What I need to figure out is how to write such an INSERT statement that would insert with a sort = (sort column value where giverUserId=x AND receiverUserId=y) OR MAX(sort column where giverUserId=x).

I'm using JavaScript/Node.js and MySQL on the backend.

Upvotes: 0

Views: 454

Answers (2)

Don
Don

Reputation: 2377

As presented, this seems over-thought (depending on how big your ID numbers go).

Assuming receiverUserId < 100000:

SELECT *
FROM user_generosity_gifts
ORDER BY giverUserId*100000 + receiverUserId;

This means you have no maintenance of sort to do when inserting or updating rows.

I may have misunderstood the question ...

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

This seems complicated. Instead of dealing with a new column in this table, think about adding a new table:

create table CustomSortOrder (
    giverUserId int,
    receiverUserId int,
    customOrder int
);

Then, you can use this in a query like:

select ugg.*
from user_generosity_gifts ugg left outer join
     CustomSortOrder cso
     on ugg.giverUserId = cso.giverUserId and
        ugg.receiverUserId = cso.receiverUserId
order by giverUserId, coalesce(cso.customOrder, receiverUserId);

This gives you all the flexibility you need, by maintaining the CustomSortOrder table. You can readily update the order for a given receiver id, in one place. I would recommend having an index on giverUserId, receiverUserId for better performance.

Upvotes: 1

Related Questions