Grails, jQuery & the jQuery Grid - Part Two

dave

Administrator
Staff member
In this second part of the tutorial I will expand on our basic jQuery grid covered in the first part of this tutorial and add a few essential elements.

Column Control

Let's take a quick look at the grid again:

View attachment 3

Firstly, we can see the big ID column on the far right hand side. Although we may need to know the record ID for each row, so that we can identify which record in our database each row relates to, we probably don't need to show it so the first thing to do is indicate that this column should be hidden.

Take a look at the column definition in list.gsp

Code:
{name:'id'}
and change it to

Code:
{name:'id',hidden:true}
That will take care of the id column. The data will still be available should we need it, but the column will no longer be visible.

Sorting Columns

You may have already notice the column headers can be clicked on to allow for sorting, ascending or descending, although nothing actually happens. All that happens now if you click is that the ajax call is adjusted to also specify the sort column and order but since we're not looking for that in the controller, the same data is returned so let's first change our controller to address this. Note: there is an intentional problem here which we'll encounter in a moment.

First let's look at our controller action again:

Code:
    // return JSON list of customers
    def jq_customer_list = {
      def customers = Customer.list()
      def jsonCells = customers.collect {
            [cell: [it.firstName,
                    it.lastName,
                    it.age,
                    it.emailAddress
                ], id: it.id]
        }
        def jsonData= [rows: jsonCells]
        render jsonData as JSON
    }
Our list of customers is being initially retrieved from the database by the Customer.list() command, and although we can pass in sort index and order parameters directly to the list() command, I want to use the Criteria class here instead. For a start if you want to further develop the function and apply filtering as I will in my next tutorial then it makes it very easy.

The two parameters that jQuery grid will pass automatically to request sorting and its order are sidx (Sort Index), and sord (Sort Order) we need to initially look for these and if not there, supply a default value. Add the following two lines to the top of the function:

Code:
def sortIndex = params.sidx ?: 'lastName'  // use lastName if not passed in params
def sortOrder  = params.sord ?: 'asc'    // ascending order unless passed in params
Now we need to switch to using the Hibernate Criteria class to retrieve our results, so replace

Code:
def customers = Customer.list()
with

Code:
def customers = Customer.createCriteria().list() {
    order(sortIndex, sortOrder).ignoreCase()
}
Now again, run your application and click on the firstName column. The data should sort by first name in ascending order. Click it again and it will sort in Descending order.

Now I mentioned an intentional problem. Click on the email address header. You will notice nothing happens, unless you are looking at your server output you will see an exception being thrown - "could not resolve property: email". I intentionally left this here to show you that you may have different column names in your grid column definition, to what you are actually using in your data model. The real field name is emailAddress, not email. So to fix this, you can either change your column name to emailAddress, but in case you have a reason to keep friendlier names in your grid column definition to what your real field names then you can specify the field name by other means. Looking back at your grid column definition, change the email line from:

Code:
{name:'email'},
to

Code:
{name:'email',index:'emailAddress'},
By specifying the index property, jQuery grid will use that when requesting sorting/filter etc and not just the default column name.

Running the application should now allow you to sort ascending and descending on all columns.

Pagination

Although in this tutorial, we only have 4 records I will still show you how to use pagination with the jQuery Grid which itself offers different options from the typical approach of using buttons to flick through from page to page of results to using a scrollable list which retrieves new records as you scroll through.

In the real world you may have a dataset with thousands of rows, and want to only retrieve 20/50/100 at a time but for the sake of this tutorial I will create the scenario we only want to ever hit the database for two records at a time. It should still show us we have a total of 4 records, but only show two at a time across a total of two pages.

At the moment we can see in our 'pager' section at the bottom of the grid, it already shows us we're on page 1 of 1, and are viewing 1-4 of 4 records. These numbers are based simply on the size of the json data we're passing back. What we need to do is detect these figures in our controller and pass them back in the json data.

Firstly though, lets change our grid definition so by default, we only request two records at a time.

Insert

Code:
rowNum:2,
just above the line

Code:
pager: jQuery('#customer_list_pager'),
We can also allow the user to change how many rows they want to view at once, e.g. 1 row, 2 rows, 3 or 4 rows, so also add this line:

Code:
rowList:[1,2,3,4],
If you run the application now, you will see the immediate effect, the drop down list is available in the pager and we only see the number of rows we specify. Although we cannot move forward or backward through the data so let's change the controller. In each ajax call, the jQuery grid is passing the current page and number of rows requested which we need to interrogate. We also need to change the jsonData array we pass back to include the total records available, and the current page. Only then will we able to navigate the data. Change the function to the one below:

Code:
// return JSON list of customers
    def jq_customer_list = {
      def sortIndex = params.sidx ?: 'lastName'
      def sortOrder  = params.sord ?: 'asc'

      def maxRows = Integer.valueOf(params.rows)
      def currentPage = Integer.valueOf(params.page) ?: 1

      def rowOffset = currentPage == 1 ? 0 : (currentPage - 1) * maxRows

      def customers = Customer.createCriteria().list(max:maxRows, offset:rowOffset) {
            order(sortIndex, sortOrder).ignoreCase()
      }
      def totalRows = customers.totalCount
      def numberOfPages = Math.ceil(totalRows / maxRows)

      def jsonCells = customers.collect {
            [cell: [it.firstName,
                    it.lastName,
                    it.age,
                    it.emailAddress
                ], id: it.id]
        }
        def jsonData= [rows: jsonCells,page:currentPage,records:totalRows,total:numberOfPages]
        render jsonData as JSON
    }
Now run the application, you should see two rows, but the record counter will now display 1 - 2 of 4 and you should be able to navigate forwards and backwards. This works perfectly well alongside the sortable columns.

And not a single page refresh in site. Once again the project can be downloaded below.

In the next part of the tutorial, I will introduce column filtering.
 
Top