Continuing to work with jqGrid, this time we will implement its Filter/Search functionality.

jqGrid provides an inbuilt filter component, which can be inbuilt into its navigation grid (navgrid), or from a custom pager/toolbar button, or from an external button on your page. This opens in the form of a window (similar to cfwindow):

Check Out the Demo Here

So first, we modify the JS from part I to include some more features. Lets see the code,

view plain print about
1<script>
2    $(document).ready(function()
3        {
4            $("#list").jqGrid(
5            {
6                url:'Users2.cfc?method=getUsers', //CFC that will return the users
7                datatype: 'json', //We specify that the datatype we will be using will be JSON
8                colNames:['ID','First Name','Last Name', 'Display Name','User Name','User Accounting Code','Phone'], //Column Names
9                //The Column Model to define the data. Note you can make columns non sortable, specify width, alignment, etc.
10                colModel :[
11                    {name:'id',index:'id', width:50, sorttype:"int",editrules:{integer:true}},
12                    {name:'FirstName',index:'FirstName', width:150, sorttype:"text"},
13                    {name:'LastName',index:'LastName', width:150, align:"left",sorttype:"text"},
14                    {name:'DisplayName',index:'DisplayName', width:150, align:"left",sorttype:"text"},
15                    {name:'UserName',index:'UserName', width:150,align:"left",sorttype:"text"},
16                    {name:'UserAccountingCode',index:'UserAccountingCode', width:150, sortable:false},
17                    {name:'Phone',index:'Phone', width:175, sortable:false,search:false}
18                ],
19                pager: $('#pager'), //The div we have specified, tells jqGrid where to put the pager
20                rowNum:20, //Number of records we want to show per page
21                rowList:[20,30,40,50], //Row List, to allow user to select how many rows they want to see per page
22                sortorder: "asc", //Default sort order
23                sortname: "ID", //Default sort column
24                viewrecords: true, //Shows the nice message on the pager
25                imgpath: '/Test/themes/basic/images', //Image path for prev/next etc images
26                caption: 'Users', //Grid Name
27                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
28                mtype:'GET',
29                recordtext:'Total Records', //On the demo you will notice "7 Total Records" - The Total Reocrds text comes from here
30                pgtext:'/', //You notice the 1/3, you can change the /. You can make it say 1 of 3
31                editurl:"Users2.cfc?method=getUsers", //Not used right now.
32                toolbar:[true,"top"], //Shows the toolbar at the top. We will use it to display user feedback                 
33                //Things to do when grid is finished loading
34                loadComplete:function(){
35                        //We get the Userdata for the grid.
36                        var recorddata = $("#list").getUserData();
37                        //If the msg type is error, we do some CSS and change text color to red, otherwise its blue
38                        if(recorddata.TYPE == "Error"){
39                            $("#t_list").css("color","red")
40                        }else{
41                            $("#t_list").css("color","blue")
42                        }
43                        //Display the message in the toolbar
44                        $("#t_list").html(recorddata.MSG)
45                                                
46                    },
47                //The JSON reader. This defines what the JSON data returned from the CFC should look like
48                jsonReader: {
49                    root: "ROWS", //our data
50 page: "PAGE", //current page
51 total: "TOTAL", //total pages
52 records:"RECORDS", //total records
53                    userdata:"USERDATA", //Userdata we will pass back for feedback
54                    cell: "", //Not Used
55                    id: "0" //Will default to frist column
56                    }
57                }
58            );            
59            //We define global settings for the search grid. This makes the caption for the window to "Filter",
60            //The submit button is called "Search", width set to 400 for better viewing,
61            //and we checkInput based on the Edit Rules specified in colModel
62            $.extend($.jgrid.search,{caption:"Filter",Find:'Search',width:'400',checkInput:true});
63            //Here we use the navGrid to display the Search button with label Filter, and disable the add/edit/delete buttons.
64            $("#list").navGrid("#pager",{edit:false,add:false,del:false,searchtext:"Filter"});
65            
66            
67        }
68    );
69    </script>

The first major change was specifying the EditRules for the ID column, editrules:{integer:true}. This is used for both jqGrid's inbuilt edit functionality and the filter. This means, if the user enters alphabetical characters for an ID search, the call to our CFC won't happen. We are now also using the toolbar to display user data. Userdata is customdata that we need to display or use that is not a part of the standard grid JSON return.

view plain print about
1loadComplete:function(){
2
3//We get the Userdata for the grid.
4var recorddata = $("#list").getUserData();
5//If the msg type is error, we do some CSS and change text color to red, otherwise its blue
6if(recorddata.TYPE == "Error"){
7    $("#t_list").css("color","red")
8}else{
9 $("#t_list").css("color","blue")
10}
11//Display the message in the toolbar
12$("#t_list").html(recorddata.MSG)
13
14}

Over here on loadComplete, we get the UserData for the grid. In JSON, the userdata return looks like userData = {TYPE:here,MSG:text}. We then use some jQuery methods to set the toolbar CSS and its html message. Doing this is going to allow us to display errors when they occur during the Filter.

The way the search paramters are passed to the CFC are, there is a _search parameter set to true when user uses the filter. There is the searchField, searchOper and searchString. The searchField is the field we are searching on (ID, FirstName, etc), the searchString is the search value. The search Operator is the operand, and can be:

eq - equal ( = )
ne - not equal ( <> )
lt - less than ( < )
le - less than or equal to ( <= )
gt - greater than ( > )
ge - greater than or equal to ( >= )
bw - begins with ( LIKE val% )
ew - ends with (LIKE %val )
cn - contain (LIKE %val% )

So, first I wrote a function in the CFC to build the search string.

view plain print about
1<cffunction name="buildSearchString" access="private" hint="Returns the Search Opeator based on Short Form Value">
2<cfargument name="searchField" required="no" default="" hint="Field to perform Search on">
3<cfargument name="searchOper" required="no" default="" hint="Search Operator Short Form">
4<cfargument name="searchString" required="no" default="" hint="Search Text">
5
6    <cfset var searchVal = "">
7    
8    <cfscript>
9    switch(Arguments.searchOper)
10    {
11    case "eq":
12        searchVal = "#Arguments.searchField# = '#Arguments.searchString#'";
13        break;
14    case "ne":
15        searchVal = "#Arguments.searchField# <> '#Arguments.searchString#'";
16        break;
17    case "lt":
18        searchVal = "#Arguments.searchField# < '#Arguments.searchString#'";
19        break;
20    case "le":
21        searchVal = "#Arguments.searchField# <= '#Arguments.searchString#'";
22        break;
23    case "gt":
24        searchVal = "#Arguments.searchField# > '#Arguments.searchString#'";
25        break;
26    case "ge":
27        searchVal = "#Arguments.searchField# >= '#Arguments.searchString#'";
28        break;
29    case "bw":
30        searchVal = "#Arguments.searchField# LIKE '#Arguments.searchString#%'";
31        break;
32    case "ew":
33    //Purposefully breaking ends with operator (no leading ')
34        searchVal = "#Arguments.searchField# LIKE %#Arguments.searchString#'";
35        break;
36    case "cn":
37        searchVal = "#Arguments.searchField# LIKE '%#Arguments.searchString#%'";
38        break;
39}    
40            
41</cfscript>
42            
43<cfreturn searchVal>
44    
45</cffunction>

I purposefully introduce an error in the "ends with" operator. So if you do the Filter with that, you will see feedback going to the user indicating an error. Now, I also have to modify the getUsers method to run the search and also return the error message if there is an issue with the search.

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<cfargument name="nd" required="no" default="0">
7<cfargument name="_search" required="no" default="false" hint="Whether search is performed by user on data grid">
8<cfargument name="searchField" required="no" default="" hint="Field to perform Search on">
9<cfargument name="searchOper" required="no" default="" hint="Search Operator Short Form">
10<cfargument name="searchString" required="no" default="" hint="Search Text">
11        
12    <cfset var arrUsers = ArrayNew(1)>
13    <cfset var strMsg = "">
14    <cfset var strMsgType = "Success">
15    <cfset var records = "">
16    <cfset var blnSearch = false>
17    <cfset var strSearch = "">        
18        
19    <cfif Arguments._search>
20        <!--- Check search query to verify we will have no errors. --->
21        <cfset strSearch = buildSearchString(Arguments.searchField,Arguments.searchOper,Arguments.searchString)>
22        <cfset blnSearch = true>
23        <cftry>
24            <cfquery name="qryCheck" datasource="RIADemo" result="res">
25                SELECT
26                    TOP 1 ID
27                FROM
28                    Users
29                WHERE
30                    #PreserveSingleQuotes(strSearch)#
31            </cfquery>
32            <cfcatch type="any">
33                <!--- Had an error, we won't run the search --->
34                <cfset blnSearch = false>                    
35                <cfset strMsgType = "Error">
36                <cfset strMsg = "There was an issue with the Search. An Error Report has been submitted to Support.">                    
37            </cfcatch>        
38        </cftry>
39    </cfif>
40        
41    <cfquery name="selUsers" datasource="RIADemo" result="res">
42        SELECT
43            ID, FirstName, LastName, DisplayName, UserName, UserAccountingCode, Phone
44        FROM
45            Users
46        <!--- Run search based on flag --->
47        <cfif blnSearch>
48            WHERE
49                #PreserveSingleQuotes(strSearch)#
50        </cfif>
51        ORDER BY #sidx# #sord#                
52    </cfquery>
53        
54    <cfset records = selUsers>
55        
56        
57    <!--- Calculate the Start Position for the loop query.
58    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.
59    If you go to page 2, you start at (2-)1*4+1 = 5 --->
60    <cfset start = ((arguments.page-1)*arguments.rows)+1>
61    
62    <!--- Calculate the end row for the query. So on the first page you go from row 1 to row 4. --->
63    <cfset end = (start-1) + arguments.rows>
64        
65    <!--- When building the array --->
66    <cfset i = 1>
67        
68    <cfloop query="records" startrow="#start#" endrow="#end#">
69        <!--- Array that will be passed back needed by jqGrid JSON implementation --->        
70        <cfset arrUsers[i] = [#ID#,#FirstName#,#LastName#,#DisplayName#,#UserName#,#UserAccountingCode#,#Phone#]>
71        <cfset i = i + 1>            
72    </cfloop>
73        
74    <!--- Calculate the Total Number of Pages for your records. --->
75    <cfset totalPages = Ceiling(records.recordcount/arguments.rows)>
76        
77    <!--- If we have been succesful, we will return the Total number of records we counted --->
78    <cfif strMsgType EQ "Success">
79        <cfset strMsg = "Retreived #records.recordcount# Records.">
80    </cfif>
81        
82    <!--- Set te user data. The JSON format expected back is, USERDATA : {type:value,msg:text} --->
83    <cfset userdata = {type="#strMsgType#",msg="#strMsg#"}>
84        
85    <!--- The JSON return --->
86    <cfset strReturn = {total=#totalPages#,page=#Arguments.page#,records=#records.recordcount#,rows=arrUsers,userdata=#userdata#}>
87        
88    <cfreturn strReturn>
89        
90</cffunction>

The code has comments, so hopefully things are clear. But basically, the first thing we do, if the _search parameter is true, is run the SQL with one row/column selection for the search criteria. If an error is returned, we set the blnSearch flag to false, otherwise it is set to true. So, when we run our main user selection query, based on the flag we know whether to run the search or not. In addition, if we do enounter an error, we set the msgtype to error and pass in a message to be displayed to the user.

The full sourcecode is available Here.

Update: Setting the width of the Filter/Search bar to auto, causes an error the first time the button is clicked on IE7.