Let user sort records ?

Question

Hello,

i am trying to figure out a way to let the user sort records (etc a list of friends).

I want to give the user the opportunity to move a record (friend) straight to the top or bottom of the list, or by entering a number (in between).

First i thought of just adding a column called SortOrder (int) to the table with all the users friends and set the number according to which order the records should be shown.

But what i am trying to avoid is that etc a user have 400 friends, and if he wants to set friend number 400 to be at position 1 in the list, then i will have to update every single record with a new SortOrder.

All data is stored in a MS Sql database.

I hope someone out there have a magic solution for this ?

Answer 1

It sounds like you're looking for a linked list type structure, where each record would hold the ID of the next record in order.

Answer 2

I don't know about magic, but for moving to the top or bottom, you could just set the SortOrder to MIN/MAX(SortOrder) +/- 1. Who says the top has to be 1 or 0?

Answer 3

Here's how I'd do it: Use your SortOrder column. Presumably, there would be an initial default sort order, say alphabetical, and so everyone would be given a SortOrder value based on their alphabetical order.

Then, when a user moved someone to the top, you could just set SortOrder to max +1. If they moved someone to the bottom, then it would be min -1. If they moved someone to somewhere in the middle, then you would want to calculate which half of the middle they are moving to. If it's the top half, then bump up the SortOrder of everyone above them. If it's the bottom half, then decrease the SortOrder of everyone below.

Not sure there's a more expedient way of doing it...

Answer 4

I wouldn't imagine they'd be doing this often enough to be a real concern but, if you're worried, use the trick we pioneered with our BASIC code from days of yore.

Back when BASIC had line numbers, we'd simply number them 10, 20, 30 and so on, so that if we needed to insert one between 10 and 20, we'd call it 15. Or if 20 should have come before 10, we'd renumber it to 5.

With a 32 bit integer column you could have 200,000 friends with a spacing of 100, more than enough to move things around, especially if you're clever.

You may want to run a sweep job occasionally to renumber the friends to 100, 200, and so on (sort of a disk defragmenter for your social network). Don't try to detect this by the looking at the friend numbers, use another field, setting it to true when a user re-arranges their friends and clearing it when you defragment. This will be more efficient.

Answer 5

You could look at it as groups of friends.

Initially, everyone is in Group 0, and the order is by name or something.
- If the user then increases the "Group" of friend (a) to 1, then they move to the top
- If the user then increases the "Group" of friend (b) to 1, then (a) and (b) appear at the top
- If the user then increases the "Group" of friend (b) again, then (b) appears 1st and (a) 2nd

Just a thougt...

Answer 6

Use floating point numbers for the sort column.

Set the initial items as 0.0, 1.0 etc.

Moving to top, use min -1.0. Moving to bottom, set to max+1.0. Moving between two items, set to (prev+next)/2.0

This is similar to the line numbers approach, but there is more "space" between the numbers. Theoretically, there is still the point where you need to renumber, when two adjancted values grow to close. I have no idea how soon this happens in practice, but I expect it to be very infrequent, so this can be done with any maintenance task.