I am working on a jqGrid that will be used to view users. I'm trying to make this grid as flexible and robust as possible as it may be included in several places - it will be used as a standalone page to view/search through all users, it will be included on a specific company's page filtered by that company to show all the users within that company, and it will be used as a result of a "quick search" available on the site (input a single search term, hit "quick search", and it will pull up any user with a meaningful field that matches the search term). So I need to accommodate 3 options:
I have a ColdFusion cfc function that accepts the jqGrid params (page, sidx, etc), the column filter params, and the quick search param. If the quick search is passed in it invokes a quick search stored procedure; if it doesn't it invokes a separate filter stored procedure passing the column filters. In either case it returns the current page's results in the proper format for the grid.
I am having a hard time navigating the jqGrid docs to figure out the best way to accomplish this. I'm not sure how to initially load the grid up with no results, or how to pass in the filters or the quick search term from the URL before loading the grid so we can pull up a predefined search. I am able to accomplish it with the following logic:
loadComplete, (if quickSearch was not passed in and this is the initialLoad) load all the filters into the filter toolbar, reset the URL programmatically to plain myFunctionURL, and invoke triggerToolbarThis seems really hacky. What is a cleaner way to accomplish this workflow?
EDIT: Here's some sample code. Keep in mind that some of the logic is in pseudocode so may not be syntactically correct. The grid looks something like this:
$(function(){
var initialLoad = true;
var quickSearch = getUrlParam("quickSearch");
var basicGridURL = 'myCfc.cfc?method=myFunction';
var gridURL = basicGridURL;
if(len(quickSearch)){
gridURL = gridURL + '&quickSearch=' + quickSearch;
} else{
gridURL = gridURL + '&initialLoad=' + initialLoad;
}
$("#users").jqGrid({
datatype: 'json',
url: gridURL,
gridview: true,
colModel: [
{name: 'lastname', label: 'Last Name'},
{name: 'firstname', label: 'First Name'},
... more column code here ...
],
height:'auto',
autowidth:true,
caption:'Users',
rowNum:20,
rowList:[10,20,50],
sortorder:'asc',
sortname: 'lastname',
ignoreCase: true, // case-insensitive filtering
pager: '#pager',
jsonReader: {
root: "ROWS", //our data
page: "PAGE", //current page
total: "TOTAL", //total pages
records:"RECORDS", //total records
cell: "", //not used
id: "0" //will default first column as ID
},
// after loading, fill the filter toolbar with any filters passed in through the URL and trigger search
loadComplete: function(){
// load filterParams from URL and cpnvert to object
filterParams = $.parseJSON(getUrlParam(filterParams));
// if this is the first load of the table and there were filterParams passed in
if(initialLoad and filterParams.length){
// it seems to need a timeout to allow everything to load properly before triggering
setTimeout(function () {
// loop through filter toolbar elements and set any passed-in filters. Example for a single element:
$('#gs_lastname').val(filterParams.lastName);
// set URL to basic function without any extra params
$("#users").jqGrid('setGridParam',{url:basicGridURL});
// trigger search
$('#users')[0].triggerToolbar();
}, 100);
initialLoad = false;
}
}
});
$("#users").jqGrid("filterToolbar", {searchOnEnter: true});
});
</script>
<table id="users"><tr><td></td></tr></table>
<div id="pager"></div>
The cfc function looks something like this: component {
/* Arguments passed in by jqGrid:
* 1. page - page user is on
* 2. rows - number of rows to display per page
* 3. sidx - sort column
* 4. sord - sort order */
remote String function getUsersForGrid(numeric page, numeric rows, String sidx, String sord, initialLoad="false",
String firstName='', String lastName='' ... additional filter params here ...
String quickSearchTerm) returnformat="JSON" {
// if this is the first load, return no data
if(initialLoad){
return SerializeJSON({total=0, page=0, records=0, rows=[]});
}
// if quickSearchTerm is passed in, run quickSearch; otherwise run regular filtered search
if(isDefined("arguments.quickSearchTerm")){
users = ... invoke stored proc here ...
} else{
// invoke stored proc with filters - get all users that match this search
users = ... invoke stored proc here ...
}
// sanitize the ordering inputs (if it doesn't match a valid value it will throw exception)
param name="sidx" type="regex" pattern="(?i)^(?:firstName|lastName|companyname|cabgroupid|status|email|state|username)$";
param name="sord" type="regex" pattern="(?i)^(?:asc|desc)$";
// qoq to order by dynamic variables
users = new Query(
dbType="query",
users = users,
sql = "SELECT * FROM users ORDER BY " & sidx & " " & sord
).execute().getResult();
// array to hold all users in current page
var arrUsers = ArrayNew(1);
// calculate start row
var start = ((arguments.page-1) * arguments.rows) + 1;
// calculate end row
var end = (start - 1) + arguments.rows;
end = (end > users.recordCount) ? users.recordCount : end;
var totalPages = Ceiling(users.recordcount / arguments.rows);
for(currRow = start; currRow <= end; currRow++){
currUser = [users["lastname"][currRow],
users["firstname"][currRow],
... additional columns here ...;
ArrayAppend(arrUsers, currUser);
}
return SerializeJSON({total=totalPages, page=arguments.page, records=users.recordcount, rows=arrUsers});
}
}
First of all, you ask the following question: "how to initially load the grid up with no results". The answer is very easy. jqGrid don't makes any request to the url on creating the grid if datatype has the value "local". Thus you can just use datatype: "local" during creating the grid and to change the value of datatype to datatype: "json" only before the first reloading grid from the server.
Seconds, you don't specify and mtype parameter. Thus jqGrid use default mtype: "GET". In other words, it makes HTTP GET request to the url. In the case it's not recommended to append manually any parameter to url. If you remind the call of $.ajax you knows that one can specify data parameter as object. jQuery.ajax calls internally jQuery.param which build the parameters and which encode the value using encodeURIComponent. To be exactly you should do the same. Thus the usage of object {quickSearch: quickSearch} or beter {quickSearch: function () { return getUrlParam("quickSearch"); }} as the data value can build the required URL parameter for you. The parameter postData of the grid will be forwarded to jQuery.ajax. Thus I suggest you to use
postData: {
quickSearch: function () {
return getUrlParam("quickSearch");
}
}
You don't posted and HTML fragments used on the page. It's still unclear, where quickSearch control (probably some <input> element) exist on the page. I don't see any requirement to use filterToolbar in your case. The code which you posed don't contains analysis of parameters, which sent filterToolbar. Thus the existing of the filter toolbar can bring only misunderstandings. Instead of that you can create one external input element of to place the input element in the top-toolbar of jqGrid. See the demos from the answer. The demos use toolbar: [true, "top"] parameter which creates empty div on top of jqGrid. Then one uses $('#t_' + $.jgrid.jqID($grid[0].id)).append() to append or to move custom searching element(s) inside of the toolbar.
I would recommend you additionally to read the answer which shows how to use postData defined as function. What you really need to do is defining event handler which allows the user to start searching. For example you can bind keyup/ or keydown event handler to catch what the user press Enter key in the searching control. Alternatively you can add "start Searching" button near the <input> control. In any way, you should set datatype to "json" (using setGridParam for example) and trigger reloadGrid inside of the event handler. It will force jqGrid to make new request to the server using the current value from the custom searching control.
One more remark. You use SELECT * FROM users on the server. I personally prefer to have IDENTITY (AUTOINCREMENT) id column in every table of my database (see here). It creates automatically immutable integer id for every row in the table of the database. The usage of the value instead of lastName have many advantages. It could exist multiple people with the same last name. Moreover deleting one user with the lastName and creating another one with the same lastName should be not interpreted as updating the previous item. Usage of IDENTITY (AUTOINCREMENT) id don't have the problem. I personally prefer don't use array format of data (see currUser = [users["lastname"][currRow],... in your server code). It could be more difficult to handle the input value, if you would consider of include new column at the beginning of the grid (for example the column having formatter: "actions" for simplify editing the row) or if you would allow the user to change the order of columns (by using columnChooser or sortable: true option of jqGrid which allows the user to change the order of the columns by drag & drop of column headers). The usage of named properties seems to increase the size of transferred data. It's true, but the usage of gzip compression of JSON response reduce the problem and finally the flexibility in managing of the server and client code is much more important aspect of the problem.