Custom Sorting

smook

This is pretty general - I'm looking for suggestions on how people allow users to custom sort lists - and not sort by columns but physically move Item 6 above Item 1 and Item 2 below Item 10 etc.

Currently I've created an sort column in the tables that I want sorted and given each item a sort number. If i want to move Item 1 below Item 6 ... i do 2 queries:

    1) subtract 1 from everything with sort order <=6's
    2) renumber item #1's sort order to #6

Of course if I was moving #6 to #1's spot it would be adding 1 to everything and then renumbering it the other way.

Anyway... does anyone have a better method of performing this kind of custom sorting? I've thought about keeping a sort order array or something like that but that doesn't cut down the amount of queries I perform.

theeper

I think you are looking for bubblesort or something similar.

smook

Well... bubbling sorts are a nice algorithm.... but that still doesn't help me with how exactly I store the sorted state in the database. ... does it?

7khat

WHy exactly do you want to sort stuff inside a database?
It's be easier if you sorted it when it cam out of the db, no?

smook

Agreed it would be much MUCH easier to sort outside of the database... however - this is custom sorting so I have to SAVE that custom arrangement somewhere.

What I have is a list of projects in the db - sometimes people want Project A to be above Project B - sometimes they want it at the end of the list. The point being, its custom - I don't know why they want things in that order - they just do. 🙂

If I could simply sort it when it came out the database that would be awesome - it would save me many headaches - but I don't see a way of doing that without changing the some sort of "sort order" list in the db.

???

7khat

Well - the users don't see the actualy database. Just the printed output that your php script produces.
Assuming you are using mysql, just put your sort stuff in the SORT field.

SELECT names FROM table ORDER BY dob
or
SELECT names FROM table ORDER BY dob DESC

(DESC = descending)

or
SELECT names FROM table ORDER BY dob DESC, names DESC

(sorts by dob descending order THEN dob descending).

If your sort is too complex for ORDER BY then when your results are in the array after you pull then off the database, do a sort() on the array. Or define your own sort method and do a usort().

smook

Thanks for the info 7KHAT.

I'm not sure if I'm being completely clear. I'm quite familiar with the order by and even array sorting. I guess what I am looking for is storing sort orders...

What I've been trying to explain is that I HAVE a sort column - and I store the number of each item.

My problem is:
1) everytime i want to move Item 1 below Item 6 I have to do 2 writes to the database and reload my page so that the user can see its done.

2) If I add a record to the database I have to move all items down and add the new record to the top (i.e. add one to all sort orders and insert a record with 1 on the sort order).

this proves problematic if more than one person is on the system at once.  Mainly because if I move item 1 below item 6 and you're logged in at the same time and move item 6 above item 4 - we will not get the expected results.

One thing I've been thinking about is writing a single sort order in another table that simply orders the record ids (2,3,4,6,1,7,10,9). Then having javascript pop and push items into an array back and forth. When they are happy with the items they do one single write to the db column. Although there seem to be complication and problems with this too.

any other ideas? thansk everyone for their help

Lars_Berg

It would be possible to use one update query when moving things in the sort order column

e.g. to move from sort position 6 to 2 this query can be used

update t set sortOrder = 
case when sortOrder = 6 then 2
        when sortOrder between 2 and 5 then sortOrder + 1 end
    where sortOrder between 2 and 6

smook

Thanks Lars!!! I did not know you could do a case statement in sql. that's fantastic. anyway of magically combining an insert and update? 🙂

Have you ever done a custom sort such as this? I'm wondering if I'm even approaching it in the best way possible.