DataTables (a table plug-in for jQuery) helps you add tables with dynamic content and behavior to your web applications. You get features like column sorting, content paging, filtering, etc. In this post I’ll show you an example of how to use DataTables to present data.
DataTables Screenshot
DataTables can obtain its data in many ways; from the DOM, a Javascript array, etc. For large data sets it’s probably best to use ajax to get just the data you want to display from the back end, rather than loading the entire data set into the browser. That’s what we’ll do here.
The source code for the view creates a table defining the columns, but no rows. Javascript runs on document load that creates the DataTable, sets some attributes, and defines the URL where the table will obtain its data.
<html>
<head>
<meta name="layout" content="main" />
<title>Launching Success Products</title>
<link rel='stylesheet' href='http://www.datatables.net//release-datatables/media/css/demo_table.css' />
<g:javascript library="DataTables-1.7.5/media/js/jquery.dataTables" />
<g:javascript>
function renderPriceWithCents(obj) {
var num = obj.aData[obj.iDataColumn]
return '$' + num.toFixed(2);
}
$(document).ready(function() {
$('#example').dataTable({
sScrollY: '70%',
bProcessing: true,
bServerSide: true,
sAjaxSource: '${request.contextPath + '/product/dataTablesSource'}' ,
bJQueryUI: true,
sPaginationType: "full_numbers",
aLengthMenu: [[100, 500, 1000, 5000, -1], [100, 500, 1000, 5000, "All"]],
iDisplayLength: 500,
aoColumnDefs: [{
fnRender: renderPriceWithCents,
aTargets: [2]
}]
});
});
</g:javascript>
</head>
<body>
<div id="demo">
<table cellpadding="0" cellspacing="0" border="0" id="example">
<thead>
<tr>
<th>Item Number</th>
<th>Title</th>
<th>Price</th>
<th>Link</th>
<th>Manufacturer</th>
</tr>
</thead>
<tbody></tbody>
<tfoot>
<tr>
<th>Item Number</th>
<th>Title</th>
<th>Price</th>
<th>Link</th>
<th>Manufacturer</th>
</tr>
</tfoot>
</table>
</div>
</body>
</html>
The controller action to generate the JSON needed by the table is…
/**
* Generates JSON needed for a DataTables table.
*/
def dataTablesSource = {
def propertiesToRender = ['itemNumber', 'title', 'price', 'link', 'manufacturer.name']
def filters = []
propertiesToRender.each { prop ->
filters << "p.${prop} like :filter"
}
def filter = filters.join(" OR ")
def dataToRender = [:]
dataToRender.sEcho = params.sEcho
dataToRender.aaData=[] // Array of products.
dataToRender.iTotalRecords = Product.countByEnabled(true)
dataToRender.iTotalDisplayRecords = dataToRender.iTotalRecords
def query = new StringBuilder("from Product as p where p.enabled=true")
if ( params.sSearch ) {
query.append(" and (${filter})")
}
def sortDir = params.sSortDir_0?.equalsIgnoreCase('asc') ? 'asc' : 'desc'
def sortProperty = propertiesToRender[params.iSortCol_0 as int]
query.append(" order by p.${sortProperty} ${sortDir}")
def products = []
if ( params.sSearch ) {
// Revise the number of total display records after applying the filter
def countQuery = new StringBuilder("select count(*) from Product as p where p.enabled=true")
if ( params.sSearch ) {
countQuery.append(" and (${filter})")
}
def result = Product.executeQuery(countQuery.toString(),
[filter: "%${params.sSearch}%"])
if ( result ) {
dataToRender.iTotalDisplayRecords = result[0]
}
products = Product.findAll(query.toString(),
[filter: "%${params.sSearch}%"],
[max: params.iDisplayLength as int, offset: params.iDisplayStart as int])
} else {
products = Product.findAll(query.toString(),
[max: params.iDisplayLength as int, offset: params.iDisplayStart as int])
}
products?.each { prod ->
dataToRender.aaData << [prod.itemNumber,
prod.title,
prod.price,
prod.link,
prod.manufacturer?.name ?: 'Unknown']
}
render dataToRender as JSON
}
Eric Turner
There must be a more efficient way to calculate iTotalDisplayRecords when a search filter has been provided (see line 29 of the controller action) than running the query just to get the size() of the resulting list! I’d love to see better ways to write this controller action.
Hi Eric,
Thanks for writing the post. For line 29, you may write custom “select count(*)” HQL and execute it with executeQuery dynamic method http://grails.org/doc/latest/ref/Domain%20Classes/executeQuery.html.
Best regards,
Chee Kin
I’m just starting with Grails and JQuery, so I’m still trying to understand some of the basics. Could you show your domain class for Product?
It’s pretty simple; just a typical domain class with some properties, constraints, an association to another class, etc.
package com.launchingsuccess import org.apache.commons.lang.builder.* class Product { int productId String itemNumber String title String description String price String link String condition Boolean enabled = false String updateError Date dateCreated Date lastUpdated static constraints = { productId(unique: true) itemNumber(unique: true, nullable: true) title(nullable: true) description(nullable: true, size:0..4000) price(nullable: true) link(url: true, nullable: true) condition(nullable: true) enabled(nullable: false) manufacturer(nullable: true) updateError(nullable: true, size:0..4000) } static mapping = { condition column: 'cond' } static belongsTo = [manufacturer: Manufacturer] boolean equals(o) { if ( ! o instanceof Product ) { return false } def eb = new EqualsBuilder().append(productId, o.productId) eb.append(itemNumber, o.itemNumber) return eb.isEquals() } int hashCode() { def hcb = new HashCodeBuilder(17,37).append(productId) hcb.append(itemNumber) return hcb.toHashCode() } }Just an update; one thing I don’t like about my implementation is that sorting by price doesn’t behave as you would expect. This is because my Product.price is just a String (which is fine for what I’m doing because I’m simply creating a Google Merchant Center feed file from catalog data) so DataTables sorts it in ascii-betical order.
I modified my Product domain class so that price is a BigDecimal, then added a render function to my DataTables definition to format the floating-point number. Here’s the revised DataTables creation:
function renderPriceWithCents(obj) { var num = obj.aData[obj.iDataColumn] return '$' + num.toFixed(2); } $(document).ready(function() { $('#example').dataTable({ sScrollY: '75%', bProcessing: true, bServerSide: true, sAjaxSource: '/LaunchingSuccessProductFeed/product/dataTablesSource', bJQueryUI: true, sPaginationType: "full_numbers", aLengthMenu: [[100, 500, 1000, 5000, -1], [100, 500, 1000, 5000, "All"]], iDisplayLength: 500, aoColumnDefs: [{ fnRender: renderPriceWithCents, aTargets: [2] }] }); } );Another update. I modified the count query to be more efficient, and updated the code in the blog post so that it’s the latest with all the improvements so far.
Hi Eric,
Thanks to update the code. Fyi, I had released a JQuery DataTables Grails plugin at http://grails.org/plugin/jquery-datatables few days ago.
Did you have any idea how to use the HQL like keyword for non-string property?
Best regards,
Chee Kin
I’ll take a look at the plugin!
As far as I know, the LIKE keyword in SQL (and HQL) only works with strings. You’ll need to create a string representation of your non-string properties.
Hi Eric,
I don’t think creating additional string properties is the way to go. Searchable plugin at http://grails.org/plugin/searchable might be the solution as it support search by domain properties.
Regards.
What do you think? Wish to hear from you again.
Hi Eric,
You can find out more about this topic in discussion at http://grails.1312388.n4.nabble.com/How-to-filter-all-domain-properties-using-like-keyword-included-non-string-property-td3320468.html
Regards,
Chee Kin
Great article, now how about showing how to load the contents of a row in the datatable in a jQuery Dialog window when the user clicks on a row?
Pingback: More jQuery DataTables: Showing Row Details | leapingmind
Thanks a lot…
Gonna tweet your link too… 
Glad you liked it! I’ve thought about writing up a blog post about how to do something similar using GSP adaptive tag libraries and the remote pagination plug-in, but haven’t quite gotten around to it yet.
Awesome blog! Is your theme custom made or did you download it from somewhere?
A design like yours with a few simple adjustements would really make my
blog stand out. Please let me know where you got your theme.
Kudos
Thanks! The theme is called Twenty Ten, and is one of the themes provided by WordPress.
Pingback: Articulo: Using jQuery DataTables In Grails | Las Neuras de Davioth
I’m not sure why but this weblog is loading extremely slow for me. Is anyone else having this problem or is it a problem on my end? I’ll check back
later on and see if the problem still exists.
An outstanding share! I have just forwarded this onto a co-worker who had been doing a
little homework on this. And he actually ordered me dinner due
to the fact that I discovered it for him… lol. So allow me to reword this.
… Thanks for the meal!! But yeah, thanks for spending time to talk about this subject here on your website.