At this point we've created a basic grid, filled it with data, refined the display of our columns and added event handlers to handle multiselect options. We've used custom cell formatters, used a custom datatype function, and even added and populated a toolbar in the process. Now let's start looking at some things that aren't necessarily jqGrid specific, but incorporate them for use in our grid. How about a search?

jqGrid includes some things for doing data search, that will automatically build modal windows and stuff. But sometimes you want to format things your own way, or incorporate jqGrid for use within an existing interface. One of the advantages for us, using the datatype function, is that we can preprocess our postdata prior to the ajax request.

First, let's add a simple form to our example:

index.html - Simple Search

view plain print about
1<form action="" name="searchForm" id="searchForm">
2    <fieldset title="Search">
3        <label for="title">Title</label><br />
4        <input type="text" name="title" size="100" /><br />
5        <div class="col">
6            <label for="from">From</label><br />
7            <input type="text" name="from" class="addDatePicker" />
8        </div>
9        <div class="col">
10            <label for="to">To</label><br />
11            <input type="text" name="to" class="addDatePicker" />
12        </div><br clear="all" />
13 To search for entries on a specific date, use the <em>To</em> field only.
14        <input type="submit" value="Search" name="searchBtn" id="searchBtn" /><br clear="all" />
15    </fieldset>
16</form>
17<div id="gridBlock">
18    ...

Looking at our demo grid, I only truly have two fields to search on: Title and Release Date. So, we provide for searching against our Title, and for searching within a date range. To top off our simple search form, let's turn those from and to fields into JQueryUI DatePicker fields.

jqGridDemo.js - DatePicker initialization

view plain print about
1$('input.addDatePicker').datepicker({
2    showOn: 'button',
3    buttonImage: '/resources/images/icons/calendar.png',
4    buttonImageOnly: true,
5    dateFormat: 'mm/dd/yy'
6});

This adds a DatePicker element to any input with the addDatePicker class. At some point you'll want to add some range validation (client and server side).

For the moment the action attribute is blank. That's OK, for us, as we're going to provide a custom submit handler in our script:

jqGridDemo.js - Search Form Submit Handler

view plain print about
1$('form#searchForm').submit(function(ev){
2    ev.preventDefault();
3    // something will go here
4    return false;
5});

Next, we're going to think about how we're using our 'search'. Basically, this is a filter we're going to apply through our query: a list of fields and values to narrow our result set by set criteria. We can param all our fields in our remote method (and should), so to avoid confusion we're going to 'scrub' our form prior to making our ajax request. Basically, we don't need anything that's empty, or with a value of '0'.

jqGridDemo.js - scrubSearch

view plain print about
1var scrubSearch = function(){
2    var frm = $('form#searchForm').serializeJSON();
3    for(var i in frm){
4        var val = frm[i];
5        // if value has no length, remove the key
6        if(val.length === 0){
7            delete frm[i];
8        }
9        if(!isNaN(val-0) && parseInt(val) === 0){
10            delete frm[i];
11        }
12    }
13    return frm;
14};

"Wait a minute, Cutter. JQuery doesn't have a serializeJSON method." No, it doesn't. JQuery has serializeArray, for pulling form values out, but doesn't have a nice method for pulling that into object notation, which is easier to work with. Arjen Oosterkamp posted this little plugin, in the comments of the serializeArray documentation. While good, the one fault I had was that it didn't turn repeating field name (like checkbox usage) values into a list, which is standard behavior on a form submit, so I modified his work to accommodate.

jqGridDemo.js - serializeJSON

view plain print about
1// Before your document ready statement
2(function( $ ){
3    $.fn.serializeJSON=function() {
4        var json = {};
5        jQuery.map($(this).serializeArray(), function(n, i){
6            (json[n['name']] === undefined) ? json[n['name']] = n['value'] : json[n['name']] += ',' + n['value'];
7        });
8        return json;
9    };
10})( jQuery );

By applying this method to our form, we get a nice object that we can then loop through for value comparisons. We return the object after we've removed any keys with empty or 0 values. Since form values are strings by default, we have to work a little magic for finding numeric values (JQuery 1.7 includes the new $.isNumeric() method, but we aren't using 1.7 in our examples).

Now, on first look you might think we're going to call this from within our form's submit handler, but that's not the case. Our example is using a basic html page, but you might be using a dynamic page where you may pre-populate your form fields with data passed in the initial page request, to pre-filter grid results for some reason or another. For this reason, you would want to have this method called immediately on page load, and on any additional request as well. For this, we'll go back to our populateGrid method.

jqGridDemo.js - populateGrid

view plain print about
1var populateGrid = function (postdata) {
2        $.ajax({
3            ...
4            data: $.extend(true, {}, postdata, {search: $.toJSON(scrubSearch())}),
5            ...
6    };

We used JQuery's $.extend() method to create a new data object, combining our postdata with a new object including search. JQuery (I wish I knew why) does not include anything for converting an object to a JSON string (only the reverse, with $.parseJSON()), so we must include another plugin library for making this possible. Luckily there's the jquery-json project on Google Code, which is heavily influenced by work done at JSON.org. All we need, to access the $.toJSON() method, is to add the file to our html head.

index.html - header script includes

view plain print about
1...
2<script type="text/javascript" src="/resources/scripts/jquery-plugins/jqgrid-4.3.1/js/jquery.jqGrid.min.js"></script>
3<script type="text/javascript" src="/resources/scripts/jquery-plugins/jquery-json/jquery.json-2.3.min.js"></script>
4<script type="text/javascript" src="/resources/scripts/custom/jqGridDemo.js"></script>
5...

By handling this, in this fashion, the form's contents are taken into account on every grid load request. This means that paging and sorting requests will have the filter criteria, until such time as the form is cleared. It also means that our form's submit handler becomes that much simpler.

jqGridDemo.js - Search Handler figure 2

view plain print about
1$('form#searchForm').submit(function(ev){
2    ev.preventDefault();
3    grid.trigger('reloadGrid');
4    return false;
5});

This is great! When you submit the form the grid's contents are now filtered by your search criteria. The only thing you have to do now is have a way to 'clear' your search criteria. You can easily do this by adding a reset button to the form, and binding to the reset event.

index.html - Search Form Buttons

view plain print about
1...
2<input type="submit" value="Search" name="searchBtn" class="searchFormBtns" />
3<input type="reset" value="Reset" class="searchFormBtns" /><br clear="all" />
4...

jqGridDemo.js - Search Form Reset Handler

view plain print about
1$('form#searchForm').bind('reset',function(ev){
2    setTimeout("$('#gridTest').trigger('reloadGrid');",1);
3});

Our binding needs us to give the browser a moment to 'clear' the field values, prior to us triggering a grid reload. Since setTimeout will have no concept of scope, we cannot use our grid variable here.

This all takes care of things, from an interface perspective, but we still need to handle these filter requests at the server. We're going to first change our remote method signature to accept our new search parameter, which is now included on every request as a JSON string.

Entries.cfc - getEntries Method Signature

view plain print about
1function GetEntries(numeric pageIndex = 1, numeric pageSize = 50, string sortCol = "ID", string sortDir = "desc", string search = "") {

We can then test the argument for value, and param all our search fields

Entries.cfc - getEntries figure 2

view plain print about
1function GetEntries(numeric pageIndex = 1, numeric pageSize = 50, string sortCol = "ID", string sortDir = "desc", string search = "") {
2    ...
3    if(Len(ARGUMENTS.search) AND IsJSON(ARGUMENTS.search)){
4        ARGUMENTS.search = DeserializeJSON(ARGUMENTS.search);
5    } else {
6        ARGUMENTS.search = {};
7    }
8
9    param name="ARGUMENTS.search.title" default="";
10    param name="ARGUMENTS.search.from" default="";
11    param name="ARGUMENTS.search.to" default="";
12    ...
13}

This now gives us some things to key off of for building our 'filtered' query. Parts of this are fairly simple: If the field exists, add the filter to the query, and the param to the query definition. The title is a good example.

Entries.cfc - getEntries figure 3

view plain print about
1...
2// Main data query
3LOCAL.sql = "SELECT    SQL_CALC_FOUND_ROWS id,
4                title,
5                posted,
6                views
7            FROM    tblblogentries
8            WHERE 0 = 0
9             ";
10if(Len(ARGUMENTS.search.title)){
11    LOCAL.sql &= " AND title LIKE :title
12     ";
13}
14LOCAL.sql &= "ORDER BY #LOCAL.orderby#
15             LIMIT    :start,:numRec";
16LOCAL.q = new Query(sql = LOCAL.sql);
17LOCAL.q.addParam(name = "start", value = (ARGUMENTS.pageIndex-1) * ARGUMENTS.pageSize, cfsqltype = "cf_sql_integer");
18LOCAL.q.addParam(name = "numRec", value = ARGUMENTS.pageSize, cfsqltype = "cf_sql_integer");
19if(Len(ARGUMENTS.search.title)){
20    LOCAL.q.addParam(name = "title", value = "%#ARGUMENTS.search.title#%", cfsqltype = "cf_sql_varchar");
21}
22...

The Release Date stuff is a bit more tricky, as you have to think about how dates are treated. First, you may not need to worry about working with the from and to fields at all. We can do a little pre-check right after our param statements.

Entries.cfc - getEntries figure 4

view plain print about
1...
2LOCAL.hasFrom = Len(ARGUMENTS.search.from) AND IsDate(ARGUMENTS.search.from);
3LOCAL.hasTo = Len(ARGUMENTS.search.to) AND IsDate(ARGUMENTS.search.to);
4...

This allows us to verify that a value was passed, and that the string is a valid date value. Next we have to consider our logic. We stated on our form that the user can search for a specific day by only filling the To field. This means that, at the least, we need to have value in the To field to filter Release Date.

Entries.cfc - getEntries figure 5

view plain print about
1// Main data query
2LOCAL.sql = "SELECT    SQL_CALC_FOUND_ROWS id,
3                title,
4                posted,
5                views
6            FROM    tblblogentries
7            WHERE 0 = 0
8             ";
9if(Len(ARGUMENTS.search.title)){
10    LOCAL.sql &= " AND title LIKE :title
11     ";
12}
13if(LOCAL.hasTo){
14    LOCAL.sql &= "AND posted BETWEEN :from
15     AND :to
16     ";
17}

What you see here is that we've added an AND BETWEEN clause to the query if to is passed in. But, why BETWEEN? The posted field, in our database, is a DATETIME datatype. So, for instance, you searched for entries posted on 08/01/2011 you would actually need to search the database for anything posted BETWEEN 00:00:00 and 23:59:59 of 08/01/2011. We need to handle either a range, or a single date. The basic sql is the same (AND BETWEEN statement), so what we have to concern ourselves with is how we create our from and to sql paramaters.

Entries.cfc - getEntries figure 6

view plain print about
1if(LOCAL.hasFrom AND LOCAL.hasTo){
2    LOCAL.q.addParam(name = "from", value = CreateODBCDateTime(ARGUMENTS.search.from), cfsqltype = "cf_sql_timestamp");
3    LOCAL.q.addParam(name = "to", value = CreateODBCDateTime(ARGUMENTS.search.to & "23:59:59"), cfsqltype = "cf_sql_timestamp");
4} else if (!LOCAL.hasFrom AND LOCAL.hasTo){
5    LOCAL.q.addParam(name = "from", value = CreateODBCDateTime(ARGUMENTS.search.to), cfsqltype = "cf_sql_timestamp");
6    LOCAL.q.addParam(name = "to", value = CreateODBCDateTime(ARGUMENTS.search.to & "23:59:59"), cfsqltype = "cf_sql_timestamp");
7}

CreateODBCDateTime will create a sql date object format that the JDBC connector will properly convert for it's db and datatype. Thing is, when given a date, without time, then the date defaults to {ts 'yyyy-mm-dd 00:00:00'}. For this reason, our to value must add on the time to the last second of that day, to properly search any entries through that day. The says if a from and to were passed, then filter between the beginning of from and the end of to, else if only to was passed, then filter from the beginning of to to the end of to.

The last thing to notice in these blocks of code is the concatenation of the LOCAL.sql string that builds our query. You may notice, in looking at these code samples, that there appears to be an additional line added to each. This is to accomodate a bug in ColdFusion's scripted sql parser, in dealing with whitespace when using sql parameters. If you look at this code with whitespace characters being shown, you will see the additional spaces required.

And that's it! Now, when you submit search criteria, your grid will be filtered on the information you've sent, and retain your criteria across paging criteria until you clear the search form.

That's it for now. In our next post we'll finally get around to those icons in the Action column. Example code for this post may be found in the Download link below.