Sorting.. is there a better way?

calling Ben Nadel

Something has always bothered me about the way I do sorting in my applications. What I mean by sorting is when let’s say you have 5 books and want the end user to be able to control which order they display on their website. A short example:

in the database they have:

book1
book2
book3
book4
book5

but on their website they want it to display as:

book3
book2
book1
book4
book5

Now the way I’ve approached this in the past is by creating a sortorder column in the table with an int datatype (I always use MSSQL as my database, so your’s could be different). Now I can store the order they want and retrieve it later. So my table would look something like this:

book1 3
book2 2
book3 1
book4 4
book5 5

So that isn’t too hard. When I want to retrieve the data, I just do: SELECT title FROM booktable ORDER BY sortorder. Everyone with me so far?

Ok, so here is where my uncertainty lies: When the user updates the order, what is the best way to update the order in the database? This is where I need some advice, so let me go with some sudo code and explain myself.

What I would normally do is have an up and down arrow next to each book. When the click on one of the arrows, the request is made and the following code fires off:

<!— query the database and get the list of books ordered by the sortorder —>
<cfquery name=”books”>
SELECT pk FROM booktable ORDER BY sortorder
</cfquery

<!— create a list out of the records —>
<cfset oldorderlist = ValueList(books.pk)
<!— get the current position of the book —>
<cfset currentposition = ListFindNoCase(oldorderlist, url.bookid)>
<!— remove the book from the list —>
<cfset oldorderlist = ListDeleteAt(oldorderlist, currentposition)>
<!— the length of our list —>
<cfset thelistlen = ListLen(oldorderlist)>

<!— moving the position of the book. —>
<cfif url.move EQ “up”>
<cfset newposition = currentposition – 1>
<cfif newposition LT 1>
<cfset newposition = 1>
</cfif>
<cfelse>
<cfset newposition = currentposition + 1>
<cfif newposition GT ListLen(oldorderlist)>
<cfset newposition = thelistlen + 1>
</cfif>
</cfif>

<!— add to list —>
<cfif newposition GT thelistlen>
<cfset neworderlist = ListAppend(oldorderlist, url.bookid)>
<cfelse>
<cfset neworderlist = ListInsertAt(oldorderlist, newposition, url.bookid)>
</cfif>

<!— loop through the list and update the database —>
<cfset counter = 1>

<cfloop index=”i” list=”#neworderlist#”>

<cfquery>
UPDATE booktable
SET sortorder = <cfqueryparam value=”#counter#” cfsqltype=”CF_SQL_INTEGER”>
WHERE pk_tblLibrary = <cfqueryparam value=”#i#” cfsqltype=”CF_SQL_INTEGER”>
</cfquery>

<cfset counter = counter + 1>

</cfloop>

So that’s my code. As you can see this has some REEEEEEAL limitations. First, you can only move up and down and not inset into a random position. The second is that it updates all the records within the table. That’s fine if you have 20 records, but if you have 10,000 the database is going to choke.

Reason I ask this question is because I want to start jerking around with jQuery and the Interface plugin to do drag and drop sortables. It’s pretty easy to do, just I need to figure out a more efficient way of handle this since a person can move a record into any poisition.

Any ideas out there?