Rip's Domain

Sorting.. is there a better way?

Posted in ColdFusion, SQL by rip747 on July 11, 2007

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?

About these ads

6 Responses

Subscribe to comments with RSS.

  1. Ben Nadel said, on July 11, 2007 at 11:34 am

    Right now, I actually do something similar, sort of. I don’t ever mess with finding the sort position of one item. Instead, I pass in the list of item IDs as I want them sorting. Then, I just loop over them and set the sort of the given ID to be the list position of the given ID.

    The limitations with are:

    1. You have to pass in the entire list of IDs when you want to update the sort.

    2. You update the entire list each time.

    #2 is not such a huge limitation as I see it. Think about moving an item from the first sort to the last sort order. In that case, you would have to update every item in the list (decrement each sort value by one). So, even if you try to optimize that part, you are still dealing with possible full table updates.

    Besides, if we are working with IDs here, they are probably indexed as the pkeys on the table, and the look-ups are gonna be crazy fast.

    #1 is the real limitation of this method. You have to pass in the entire ID list. But, again, I am not sure how much of a limitation this really is. Yes, if you had 10,000 items that needed to be updated, it might hurt…. but let’s take a step back: if you have to maintain a list of 10,000 manually sorted items, I don’t think Performance is gonna be your main headache.

    If you are gonna be using jQuery to rock this stuff, I am sure it is either built-in or available via a plug-in to get an array or list of all attribute values… something like:

    $( “input.listitem” ).valueList()

    … or something like that that would return a comma list of all the value attributes of the matching inputs. Then, take that list value and pass that in as your sort list:

    UPDATE [table] SET sort = #i# WHERE id = #ListGetAt( URL.idlist, i )#;

    That’s my 2 cents. Sorry I can’t be of more help, but sorting is not something that I have found a huge need to optimize. I am certainly willing to learn right along with you :)

  2. Ben Nadel said, on July 11, 2007 at 11:35 am

    Ooops, it stripped out my tags:

    [cfquery]
    [cfloop index=”i” from=”1″ to=”#ListLen( URL.idlist )#”]
    UPDATE [table] SET sort = #i# WHERE id = #ListGetAt( URL.idlist, i )#;
    [/cfloop]
    [/cfquery]

  3. Ben Nadel said, on July 11, 2007 at 3:11 pm

    Tony, I thought I would play around with jQuery to get a delimited list of input values:

    http://www.bennadel.com/index.cfm?dax=blog:824.view

    I love jQuery :)

  4. Mario Rodrigues said, on July 12, 2007 at 4:54 pm

    The way I solve this is to find where an item is moving from and where it’s moving to.

    For example, let’s say you wanted to take the 5th item and move it to 2.

    I would only need two update statments.

    The first one to move the item.

    UPDATE [table]
    SET sort = #NEWPLACE#
    WHERE id = #id#

    and to move the items that are between the new place (2) and my old place (5)

    UPDATE [table]
    SET sort = sort+-1
    WHERE place BETWEEN #NEWPLACE# AND #OLDPLACE#

  5. Mario Rodrigues said, on July 12, 2007 at 4:55 pm

    My <cfif statement didn’t make it, but basically I had an IF statement on the second query (if NEWPLACE is less than OLDPLACE + otherwise -)

  6. Joshua Giese said, on October 19, 2007 at 1:04 pm

    Thanks for the tip mario, I have been looking for a solution to sorting items like this for some time


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: