Sorting Lists with JQuery UI, AJAX and ColdFusion
Someone asked me the other day about an old post I did about Sortable list with jQuery and Coldfusion. jQuery UI has been released since I wrote that post so I thought I might as well post an updated version with some additional functionality. Before I continue, I should point out that although I've included some basic security in the demo, requests from the browser can be spoofed so you need to be diligent about security.
What this bit of code does, is allow you to sort a list, the new order is saved automatically using a bit of AJAX. You can also make items go to the top of the list (which is handy if you have a long list which gives you scroll bars in the browser), and also delete items from the list.
Sortable.cfc
<cfcomponent output="false" hint="I respond to AJAX requests">
<!---
--------------------------------------------
Remote Methods
--------------------------------------------
--->
<cffunction name="updateOrder" output="false" access="remote">
<cfargument name="orderedList" required="true" type="string">
<cfargument name="key" required="true" type="string">
<cfset var ndx = "">
<cfset var id = 0>
<cfset var position = 0>
<cfset var updatedids = "">
<cfset var result = {result="false"}>
<!--- do a basic security check --->
<cfif isAllowed( arguments.key )>
<!--- Prevent race conditions --->
<cflock name="updateOrder" timeout="60">
<cftransaction>
<cfloop list="#arguments.orderedList#" index="ndx">
<cfset id = Val( ListLast( ndx, "_" ) )>
<cfset position = position+1>
<cfset updatedids = ListAppend( updatedids, id )>
<cfquery datasource="mydsn">
update myTable set
order = <cfqueryparam value="#position#" cfsqltype="cf_sql_integer">
where
id = <cfqueryparam value="#id#" cfsqltype="cf_sql_integer">
</cfquery>
</cfloop>
<!--- delete any items not in the list --->
<cfquery datasource="mydsn">
delete from myTable
where id not in ( <cfqueryparam value="#updatedids#" cfsqltype="cf_sql_integer" list="true"> )
</cfquery>
</cftransaction>
</cflock>
<cfset result = {result="true", neworder=updatedids, position=position }>
</cfif>
<cfreturn result>
</cffunction>
<!---
--------------------------------------------
Private Methods
--------------------------------------------
--->
<cffunction name="isAllowed" output="false" access="private" returntype="boolean">
<cfargument name="key" required="true">
<cfset var result = false>
<!--- check that request is coming from the same browser that created the session --->
<cfif IsDefined( "session.ajaxkey" ) AND ( session.ajaxkey eq arguments.key )>
<cfset result = true>
</cfif>
<cfreturn result>
</cffunction>
</cfcomponent>
sortingdemo.cfm
<html>
<head>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.5/jquery-ui.min.js"></script>
<style type="text/css">
#sortable { list-style-type: none; margin: 0; padding: 0; width: 60%; }
#sortable li { margin: 0 3px 3px 3px; padding: 0.4em; padding-left: 1.5em; font-size: 1.4em; height: 18px; width:400px; border:1px solid gray; background-color:lightgray;}
#sortable li span { float:right; margin-left:2px; padding:0px; border:1px solid blue; background-color:lightblue;}
</style>
<script type="text/javascript">
$(function() {
$sortable = $("#sortable").sortable({
update: function(event, ui){
updateOrder();
}
}).disableSelection();
$('<span>delete</top>').css('float','right').appendTo( $('li', $sortable) ).click(function(){
$this = $(this);
$this.parent().remove();
updateOrder();
});
$('<span>top</top>').css('float','right').appendTo( $('li', $sortable) ).click(function(){
$this = $(this);
$li = $this.parent().detach();
$li.prependTo($sortable);
updateOrder();
});
function updateOrder(){
$.getJSON(
'http://yourserver/sortable.cfc?wsdl',
{
method : 'updateOrder',
key: '<cfoutput>#session.ajaxkey#</cfoutput>',
orderedList: $sortable.sortable('toArray').toString(),
returnformat: 'json',
queryformat: 'column'
},
callback
);
}
});
function callback(json){
if ( json.RESULT )
{
$('#serverresult').text( "Order updated to: " + json.NEWORDER );
}
else
{
$('#serverresult').text( "Something went wrong!" );
}
}
</script>
</head>
<body>
<div class="demo">
<ul id="sortable">
<li id="id_1">Item 1</li>
<li id="id_2">Item 2</li>
<li id="id_3">Item 3</li>
<li id="id_4">Item 4</li>
<li id="id_5">Item 5</li>
<li id="id_6">Item 6</li>
<li id="id_7">Item 7</li>
</ul>
<div id="serverresult">
</div>
</div>
</body>
</html>
Application.cfc
<cfcomponent output="false">
<cfset this.name = Hash( GetCurrentTemplatePath() )>
<cfset this.sessionManagement = true>
<cffunction name="onSessionStart" returnType="void" output="false">
<!--- we'll use this key to provide some basic protection for the AJAX webservice --->
<cfset session.ajaxkey = CreateUUID()>
</cffunction>
</cfcomponent>
I hope it's useful
- Posted in:
- jQuery
- ColdFusion
I am using the sortable interface to let users sort a list of search categories to create a list value (eg. 2,3,1,4) and setting that list value as the users preference for our search results page (order that the search categories are returned). The "delete" function would be great for me if it worked more like a disable/enable button. Do you have any advice on how I could disable/enable list items instead of deleting them. Thanks in advance for any help.
And again thanks for the great post!
Steve
Comment by SteveC – October 06, 2010
If it's the latter, then I'd do a separate AJAX call for the "delete" link which passes the id back to the server.
Comment by John Whish – October 13, 2010
Thanks for the response.
I like how delete removes the selected item from the list and writes that list version to the database. I want to maintain that functionality but I don't want the item to be removed from the users screen, so instead of deleting it from view it just becomes disabled so that the user could decide later to reenable it. Then when they reenable the item it will also be added back into the list that is being posted to the database.
thanks again
Steve
Comment by SteveC – October 13, 2010
Sortables has an option to connect two lists together, so you could just create another "deleted" list and then drag and drop between the two lists. Here's a quick amend to the original code I posted to get you started:
<html>
<head>
<script type="text/javascript" src="ajax.googleapis.com/ajax/libs/jquery/1.4.3/jquery.min.js"></script>
ul { list-style-type: none; margin: 0; padding: 0; width: 60%; }
ul li { margin: 0 3px 3px 3px; padding: 0.4em; padding-left: 1.5em; font-size: 1.4em; height: 18px; width:400px; border:1px solid gray; background-color:lightgray;}
ul li span { float:right; margin-left:2px; padding:0px; border:1px solid blue; background-color:lightblue;}
</style>
<script type="text/javascript">
$(function() {
$deleted = $('#deleted').sortable({connectWith: '#mylist'});
$mylist = $("#mylist").sortable({
connectWith: '#deleted',
update: function(event, ui){
updateOrder();
}
}).disableSelection();
$('<span>top</top>').css('float','right').appendTo( $('li', $mylist) ).click(function(){
$this = $(this);
$li = $this.parent().detach();
$li.prependTo($mylist);
updateOrder();
});
function updateOrder(){
$.getJSON(
'yourserver/sortable.cfc?wsdl',
method : 'updateOrder',
key: '<cfoutput>#session.ajaxkey#</cfoutput>',
orderedList: $mylist.sortable('toArray').toString(),
returnformat: 'json',
queryformat: 'column'
},
callback
);
}
});
function callback(json){
if ( json.RESULT ){
$('#serverresult').text( "Order updated to: " + json.NEWORDER );
}
else{
$('#serverresult').text( "Something went wrong!" );
}
}
</script>
</head>
<body>
<div class="demo">
<h2>My items</h2>
<ul id="mylist">
<li id="id_1">Item 1</li>
<li id="id_2">Item 2</li>
<li id="id_3">Item 3</li>
<li id="id_4">Item 4</li>
<li id="id_5">Item 5</li>
<li id="id_6">Item 6</li>
<li id="id_7">Item 7</li>
</ul>
<h2>Deleted items</h2>
<ul id="deleted">
<li>Drag deleted items here</li>
</ul>
<div id="serverresult">
</div>
</div>
</body>
</html>
Comment by John Whish – October 18, 2010
Comment by SteveC – October 18, 2010
There is a code error that needs to be fixed for this tutorial to work. In the CFC in the delete query, there is an error in the CFQUERYPARAM tag. In the tag list parameters, add list="true" and then remove type="list" and then it works perfectly. But really, awesome tutorial!
Comment by Josh – January 05, 2011
Comment by John Whish – January 05, 2011
I can't get the code above to update my SQL DB. I have a table of photos with the following fields:
photo_id
property_id
sequence
It looks like it updates on the screen but when I refresh the page, it doesn't keep the new order.
I'm pulling my hair out trying to figure it out.
Any help would be appreciated.
Thanks,
Tony
Comment by Tony – March 19, 2011
Comment by Tom – July 08, 2011
I have a little problem.
Your code gives an application.cfc.
I work with application.cfm yet. I was truing to understand how to convert my .cfm into a .cfc but I coudn't find any good place explaining how to do it.
Is there anyway to translate the component you have in application.cfc in order to make it work in my application.cfm?
Thanks again!!
Comment by Dani – November 09, 2011
The Applicaiton.cfc is only setting a session variable. With Application.cfm you should be able to do:
<cflock scope="Session"
timeout="10" type="Exclusive">
<cfif !IsDefined("session.ajaxkey")>
<!--- key to provide some basic protection for the AJAX webservice --->
<cfset session.ajaxkey = CreateUUID()>
</cfif>
</cflock>
Comment by John Whish – November 10, 2011
There's something that I'm not able to find.
When I drag one of the <li> elements, it's not updating the database.
I'm not seeing either the serverresult message that should appear if something went wrong (or right).
I have changed the sortable.cfc code based on my table, etc, but something for sure is wrong in what I'm doing.
This is what my loop looks like:
<div class="demo">
<cfoutput>
<ul id="sortable">
<cfloop query="slider">
<li id="id_#id#"><img src="../slider/#locImage#" style="border: 1px solid ##222222; width:150px" /> #id#</li>
</cfloop>
</ul>
</cfoutput>
<div id="serverresult">
</div>
</div>
And here how my update and delete look like:
<cflock name="updateOrder" timeout="60">
<cftransaction>
<cfloop list="#arguments.orderedList#" index="ndx">
<cfset id = Val( ListLast( ndx, "_" ) )>
<cfset position = position+1>
<cfset updatedids = ListAppend( updatedids, id )>
<cfquery datasource="#ds#">
update slider
set mediaOrder = <cfqueryparam value="#position#" cfsqltype="cf_sql_integer">
where id = <cfqueryparam value="#id#" cfsqltype="cf_sql_integer">
</cfquery>
</cfloop>
<!--- delete any items not in the list --->
<cfquery datasource="#ds#">
delete from slider
where id not in ( <cfqueryparam value="#updatedids#" cfsqltype="cf_sql_integer" list="true"> )
</cfquery>
</cftransaction>
</cflock>
Do you see something wrong that it should be different?
Thank you so much for your time.
Comment by Dani – November 10, 2011
This information is excellent. I'm trying to do something slightly different but with the same results.
I'm pulling a list of name from the db and creating a list. I'm then using the jquery sortable and connectWith to move the names from the main list to spots in the second list (think assigning people to rooms). I then need to save the state in the db as the master room list.
I think with your sortable.cfc code, I'll be able to work thru saving the state to the db. The problem I am having is updating the sortable function to contain connectWith. Below is the connectWith code.
$(function() {
$( "#sortable1, #sortable2" ).sortable({
connectWith: ".connectedSortable"
}).disableSelection();
});
I know I need to update the following fuction but everything I have tried breaks the entire thing. This is the start of your function... I am guessing the connectWith code will need to be incorporated within this section:
$(function() {
$sortable = $("#sortable").sortable({
update: function(event, ui){
updateOrder();
}
}).disableSelection();
Any assistance you can provide would be much appreciated.
Kim
Comment by Kim – November 20, 2011
I just tried to implement this code. Unfortunately I am working with Application.cfm
I generated AjaxKey the same way as discussed in Dani's comments. But the key I am getting in ajax call is different than the one in SESSION.ajaxkey
It seems on my ajax call Application.cfm generates a new ajaxKey.
Anyone knows this problem? Please help!!
Thanks
Regards,
Adil
Comment by Adi – January 24, 2012
Not sure how you are getting a different key. The session is unique to the user/browser and will timeout, so make sure that you've not got something cached.
In Application.cfm, you need to make sure that you're checking that session.ajaxkey doesn't exists before you create a new one.
Comment by John Whish – January 24, 2012
Nothing is being cashed. I've printed both the keys. In case of ajax call my argument.ajaxkey remains same but it generates a new ajaxKey for each ajax call.
I faced the same issue when I was using Application.cfc in some other project. My session variables were not visible in Ajax calls. I created an Application.cfc file in my cfc folder. This new Application.cfc contained nothing but included the main Application.cfc. Doing thing I was able to view my session variables in my Ajax calls.
But I don't know how to do the same fix in case of Application.cfm
Comment by Adi – January 24, 2012
Comment by John Whish – January 28, 2012
I am using the default session management of Coldfusion
Comment by Adi – January 29, 2012
Comment by John Whish – January 29, 2012
Yes the code is online but I've signed an NDA. I asked the owner but he didn't allow to share anything. May be he's extra conscious about his IDEA.
For now I've excluded the 'isAllowed' method and the code is working. When I found a solution I'll put 'isAllowed' method again.
I appreciate you helping nature and quick responses.
Thanks
Regards,
Adil Malik
Comment by Adi – February 01, 2012