So, this weekend I started looking at jQuery. I have seen many people around the blogosphere use it and it is surprisingly lightweight, and doesn't require a lot of code writing. There is also another problem, the ColdFusion cfgrid implementation slows down as the number of records increase. So, specifically I looked at jqGrid plugin for jQuery to implement a datagrid.

I took my previous work on a simple User List grid and started the process of replicating it using jQuery. Thankfully, I was not the first one to think of something like this, so I had a head start thanks Hal Helms



Demo Currently Unavailable

So, first lets look at the CFM page, then we will take a look at the CFC. First step is to include all the CSS and JS files we need for this.

view plain print about
1<link rel="stylesheet" type="text/css" media="screen" href="themes/basic/grid.css" />
2<link rel="stylesheet" type="text/css" media="screen" href="themes/jqModal.css" />    
3<script src="jquery.js" type="text/javascript"></script>
4<InvalidTag src="jquery.jqGrid.js" type="text/javascript"></script>
5<InvalidTag src="js/jqModal.js" type="text/javascript"></script>
6<InvalidTag src="js/jqDnR.js" type="text/javascript"></script>

We also specify, a table (our grid), a div for the pager. There is also a search div in this code, I will be implementing the search feature for the grid at a later date.

view plain print about
1<table id="list" class="scroll" cellpadding="0" cellspacing="0"></table>
2<div id="pager" class="scroll" style="text-align:center;"></div>
3<div id="mysearch"></div>

Now, lets look at the meat, the JS code on the page.

view plain print about
1$(document).ready(function()
2        {
3            $("#list").jqGrid(
4            {
5                url:'Users.cfc?method=getUsers', //CFC that will return the users
6                datatype: 'json', //We specify that the datatype we will be using will be JSON
7                colNames:['ID','FirstName','LastName', 'DisplayName','UserName','UserAccountingCode','Phone'], //Column Names
8                //The Column Model to define the data. Note you can make columns non sortable, specify width, alignment, etc.
9                colModel :[
10                    {name:'id',index:'id', width:50, sorttype:"int"},
11                    {name:'FirstName',index:'FirstName', width:150, sorttype:"string"},
12                    {name:'LastName',index:'LastName', width:150, align:"left",sorttype:"string"},
13                    {name:'DisplayName',index:'DisplayName', width:150, align:"left",sorttype:"string"},
14                    {name:'UserName',index:'UserName', width:150,align:"left",sorttype:"string"},
15                    {name:'UserAccountingCode',index:'UserAccountingCode', width:150, sortable:false},
16                    {name:'Phone',index:'Phone', width:150, sortable:false}
17                ],
18                pager: $('#pager'), //The div we have specified, tells jqGrid where to put the pager
19                rowNum:4, //Number of records we want to show per page
20                rowList:[4,8,12], //Row List, to allow user to select how many rows they want to see per page
21                sortorder: "asc", //Default sort order
22                sortname: "ID", //Default sort column
23                viewrecords: true, //Shows the nice message on the pager
24                imgpath: '/PLite/Test/themes/basic/images', //Image path for prev/next etc images
25                caption: 'Users', //Grid Name
26                height:'auto', //I like auto, so there is no blank space between. Using a fixed height can mean either a scrollbar or a blank space before the pager
27                recordtext:'Total Records', //On the demo you will notice "7 Total Records" - The Total Reocrds text comes from here
28                pgtext:'/',//You notice the 1/3, you can change the /. You can make it say 1 of 3
29                editurl:"Users.cfc?method=getUsers",//Not used right now.
30                toolbar:[true,"top"],//Shows the toolbar at the top. I will decide if I need to put anything in there later.
31                //The JSON reader. This defines what the JSON data returned from the CFC should look like
32                jsonReader: {
33                    root: "ROWS", //our data
34                    page: "PAGE", //current page
35                    total: "TOTAL", //total pages
36                    records:"RECORDS", //total records
37                    cell: "", //not used
38                    id: "0" //will default first column as ID
39                    }
40                }
41            );            
42            
43        }
44    );

I have specified comments in the code, hopefully that explains enough. the jqGrid site also offers excellent documentation on the properties and data.

The next step is to write the getUsers function, and make it return data in the expected JSON format and also work with sort/pagination.

view plain print about
1<cffunction name="getUsers" access="remote" returnformat="json">
2        <cfargument name="page" required="no" default="1" hint="Page user is on">
3     <cfargument name="rows" required="no" default="10" hint="Number of Rows to display per page">
4     <cfargument name="sidx" required="no" default="" hint="Sort Column">
5     <cfargument name="sord" required="no" default="ASC" hint="Sort Order">
6    
7        <cfset var arrUsers = ArrayNew(1)>
8        
9        
10        <cfquery name="selUsers" datasource="RIADemo">
11            SELECT
12                ID, FirstName, LastName, DisplayName, UserName, UserAccountingCode, Phone
13            FROM
14                Users
15            <!--- Sorting Here --->
16            <cfif Arguments.sidx NEQ "">    
17                ORDER BY #Arguments.sidx# #Arguments.sord#
18            <cfelse>
19                ORDER BY ID #Arguments.sord#
20            </cfif>
21            
22            
23        </cfquery>
24        
25        <!--- Calculate the Start Position for the loop query.
26        So, if you are on 1st page and want to display 4 rows per page, for first page you start at: (1-1)*4+1 = 1.
27        If you go to page 2, you start at (2-)1*4+1 = 5 --->
28        <cfset start = ((arguments.page-1)*arguments.rows)+1>
29        
30        <!--- Calculate the end row for the query. So on the first page you go from row 1 to row 4. --->
31        <cfset end = (start-1) + arguments.rows>
32        
33        <!--- When building the array --->
34        <cfset i = 1>
35        
36        <cfloop query="selUsers" startrow="#start#" endrow="#end#">
37            <!--- Array that will be passed back needed by jqGrid JSON implementation --->        
38            <cfset arrUsers[i] = [#ID#,#FirstName#,#LastName#,#DisplayName#,#UserName#,#UserAccountingCode#,#Phone#]>
39            <cfset i = i + 1>            
40        </cfloop>
41        
42        <!--- Calculate the Total Number of Pages for your records. --->
43        <cfset totalPages = Ceiling(selUsers.recordcount/arguments.rows)>
44        
45        <!--- The JSON return.
46            Total - Total Number of Pages we will have calculated above
47            Page - Current page user is on
48            Records - Total number of records
49            rows = our data
50        --->
51 <cfset stcReturn = {total=#totalPages#,page=#Arguments.page#,records=#selUsers.recordcount#,rows=arrUsers}>
52        
53    <cfreturn stcReturn>
54        
55    </cffunction>

Here the getUsers function first as needed retrieves the records from the server. Adding the sort is similar to cfgrid. Next we have to implement the pagination feature. This requires us to calculate which row of the query to start at and till when to go. So we play around with the information we have and determine that. Then we loop through the query and build the array that will be our data records.

Next we build the JSON data we are returning and return it. Nothing complex.

To note here is that we are leaving the cell specification in JSON reader blank, cell: "", //not used. If we had specified something there, we would have to change how the return data looks.

Currently, I have not found anything that would require the cell to be explicitly set, maybe the more complex jqGrid features require it.

So, this was a basic grid with sorting and pagination. Next step would be to use its in-built filter functionality. It will also have to be another Blog Entry.

Feel free to let me know, if there is a better way to handle the query to JSON data conversion.