I am trying to build tables that have custom sort orders... i.e. the user can move where they want any row to appear.
I've been trying to implement this in the same way that phpMyadmin allows you to add columns to a table (if you're familiar with this) or even how MS-Excel allows you to move worksheets...
At the end of every row I have a pulldown that consists of a TOP element and then an AFTER element for every row in the table. So if you use the pulldown in row2 you could move it to the TOP of the table, to After Row 3, After Row 4 etc etc...
The way I've achieved this is to create a column in the db table called sort order which is just an integer counter of each row. If I want to MOVE row2 to the top, I can just switch row1 becomes row2 and vice versa.
If I want to move row2 after row5 (for example) ... I move everyone from row2 to row5 back one... (row2,3,4, 5 become 1,2,3 and 4) ...then renumber row2 to have sort order 5. like so:
row1 1
row2 2
row3 3
row4 4
row5 5
row6 6
then...
row1 1
row2 1
row3 2
row4 3
row5 4
row6 6
then...
row1 1
row3 2
row4 3
row5 4
row2 5
row6 6 etc...
Works ok... however... it takes 2 database writes which really bothers me. It also is a pain in the butt because I have to keep track everytime I add a new element... need to bump everything down +1 and then insert the new element with a 1 in the sort order so the new elements stay on top. This means more database hits for everyone one that is NEEDED.
Does anyone have a better way of doing this custom ordering? Ideally, I'd like to ONLY affect the one row ... I was thinking maybe there was an algorithm or some math - like if row2 were to move past row 5 it would just become sort order 5.1 or something. problem is what if i want to then move row3 between row5 and row2... does its sort order become 5.05 ?
thoughts are greatly appreciated...
sm
