In this part of the tutorial, I will build upon our existing grid from part 2 in which we added support for sorting and pagination and add to it in place filtering.
There are different ways to accomplish filtering of data, a basic search form could be provided in which the user fills out the fields, then clicks submit to trigger the search. However in this example I want to provide the user to ability to search very very quickly with the least number of user interactions and this is one particular feature that the jQuery grid offers us that I like.
The grid can be configured to allow a search box to appear above every column allowing the user to very quickly enter a value and hit return triggering the search. The grid will then re-request data using the filter parameters entered and update the presented very rapidly.
It even offers more flexibility in that you aren't just restricted to textboxes, you could have a dropdown selection also. This is particular useful when you have limited fixed values, for example if you're presenting orders and each order can have a status of New/In Progress/Complete. A drop down select box can show those values allowing the user to quickly only show orders with a particular status.
So the first step for us to provide filtering is to adjust the grid definition itself.
At the end of the grid definition, we need to instruct the grid to display the filter toolbar.
Add
between the two "});" lines
The autosearch parameter informs the grid to perform the search when the user presses the enter/return key.
Running your application now you should see an adjustment to the column header with the addition of the filter toolbar :
View attachment 5
Now all we need to do is adjust our controller so that we detect the filter parameters and use these when retrieving data from the database.
First though you will need to spend a little time considering the 'types' of searching you will perform on each field. i.e. take the firstName field for example, do you want to retrieve records where the firstname field is exactly what the user enters, or simply contains what the user enters, or begins, or ends with the value the user enters? Also should it be case sensitive or insensitive?
As we are already using the Criteria class to retrieve our data, adding filter criterion is very simple, locate the list() method in our controller and replace
with
Further information on the Grails criteria engine can be viewed at http://www.grails.org/Hibernate+Criteria+Builder
Running the application now, you should be able to now filter your records according to what you enter in the filter toolbar when you hit return.
In the next tutorial I will cover addition/editing and deletion of data within the grid.
There are different ways to accomplish filtering of data, a basic search form could be provided in which the user fills out the fields, then clicks submit to trigger the search. However in this example I want to provide the user to ability to search very very quickly with the least number of user interactions and this is one particular feature that the jQuery grid offers us that I like.
The grid can be configured to allow a search box to appear above every column allowing the user to very quickly enter a value and hit return triggering the search. The grid will then re-request data using the filter parameters entered and update the presented very rapidly.
It even offers more flexibility in that you aren't just restricted to textboxes, you could have a dropdown selection also. This is particular useful when you have limited fixed values, for example if you're presenting orders and each order can have a status of New/In Progress/Complete. A drop down select box can show those values allowing the user to quickly only show orders with a particular status.
So the first step for us to provide filtering is to adjust the grid definition itself.
At the end of the grid definition, we need to instruct the grid to display the filter toolbar.
Add
Code:
$("#customer_list").jqGrid('filterToolbar',{autosearch:true});The autosearch parameter informs the grid to perform the search when the user presses the enter/return key.
Running your application now you should see an adjustment to the column header with the addition of the filter toolbar :
View attachment 5
Now all we need to do is adjust our controller so that we detect the filter parameters and use these when retrieving data from the database.
First though you will need to spend a little time considering the 'types' of searching you will perform on each field. i.e. take the firstName field for example, do you want to retrieve records where the firstname field is exactly what the user enters, or simply contains what the user enters, or begins, or ends with the value the user enters? Also should it be case sensitive or insensitive?
As we are already using the Criteria class to retrieve our data, adding filter criterion is very simple, locate the list() method in our controller and replace
Code:
def customers = Customer.createCriteria().list(max:maxRows, offset:rowOffset) {
order(sortIndex, sortOrder).ignoreCase()
}
Code:
def customers = Customer.createCriteria().list(max:maxRows, offset:rowOffset) {
// first name case insensitive where the field begins with the search term
if (params.firstName)
ilike('firstName',params.firstName + '%')
// last name case insensitive where the field begins with the search term
if (params.lastName)
ilike('lastName',params.lastName + '%')
// age search where the age Equals the search term
if (params.age)
eq('age', Integer.valueOf(params.age))
// email case insensitive where the field contains search term
if (params.emailAddress)
ilike('emailAddress','%' + params.emailAddress + '%')
// set the order and direction
order(sortIndex, sortOrder).ignoreCase()
}Running the application now, you should be able to now filter your records according to what you enter in the filter toolbar when you hit return.
In the next tutorial I will cover addition/editing and deletion of data within the grid.
