More jQuery DataTables: Showing Row Details

Aaron commented on my last blog post that he’d like to see an example of how to click on a row in a jQuery DataTable to show that row’s details in a jQuery UI dialog. In this post I’ve created a sample Grails application which does just that. Some of the things you’ll learn how to do are:

• Hide columns in a DataTable
• Add a click event handler to each row in a DataTable
• Use AJAX to retrieve data from the server
• Display that data in a jQuery UI modal dialog

You can either download the source code from GitHub, or create your own Grails app and follow along.

Get Ready for jQuery

In a new Grails app, install the jquery, jquery-ui, and jquery-datatables plugins

$ grails install-plugin jquery
$ grails install-plugin jquery-ui
$ grails install-plugin jquery-datatables

Then add a couple of tags to your grails-app/views/layouts/main.gsp layout to make jquery and jquery-ui resources available to all of your views:

<g:javascript library="jquery" plugin="jquery" />
<jqui:resources />

Lastly, add the following to grails-app/conf/Config.groovy:

grails.views.javascript.library="jquery"

Data Driven

We need some data to display in the jQuery DataTable, so I created a Person domain class:

class Person {
   String firstName
   String lastName
   Date birthDate

   Date dateCreated
   Date lastUpdated

   static constraints = {
      birthDate(nullable:true)
   }
}

Then, in grails-app/conf/BootStrap.groovy, I created some people with names that I’m certain my ten-year-old nephew would giggle at:

private def createPerson(properties) {
   def person = Person.findByFirstNameAndLastName(
                   properties.firstName,
                   properties.lastName)
   if ( ! person ) {
      person = new Person(properties)
      if ( ! person.save() ) {
         println "Unable to save person: ${person.errors}"
      }
   }
}

def init = { servletContext ->
   createPerson([firstName: 'Ben', lastName: 'Dover',
      birthDate: Date.parse('yyyy-MM-dd', '1982-03-04')])
   createPerson([firstName: 'Seymour', lastName: 'Butts',
      birthDate: Date.parse('yyyy-MM-dd', '1983-04-03')])
   createPerson([firstName: 'Hugh', lastName: 'Jass',
      birthDate: Date.parse('yyyy-MM-dd', '1984-05-02')])
   createPerson([firstName: 'Dixie', lastName: 'Normus',
      birthDate: Date.parse('yyyy-MM-dd', '1985-06-01')])
}

Serve Up Some Data

In my PeopleController I added an action to serve up the data to a jQuery DataTables table. The DataTable will have bServerSide and sAjaxSource properties set so that it fetches its data via an AJAX call to the action on my PeopleController.

def dataTablesData = {
   def propertiesToRender = ['id', 'firstName', 'lastName', 'birthDate']

   def dataToRender = [:]
   dataToRender.sEcho = params.sEcho
   dataToRender.aaData=[]                // Array of people.

   dataToRender.iTotalRecords = Person.count()
   dataToRender.iTotalDisplayRecords = dataToRender.iTotalRecords

   // Create the query, possibly with a search filters. We only search
   // String properties in this example.
   def filters = []
   filters << "p.firstName like :filter"
   filters << "p.lastName like :filter"

   def filter = filters.join(" OR ")
   def query = new StringBuilder("from Person as p")
   if ( params.sSearch ) {
      query.append(" where (${filter})")
   }

   def sortProperty = propertiesToRender[params.iSortCol_0 as int]
   def sortDir = params.sSortDir_0?.equalsIgnoreCase('asc') ? 'asc' : 'desc'
   query.append(" order by p.${sortProperty} ${sortDir}")

   // Execute the query
   def people = []
   if ( params.sSearch ) {
      // Revise the number of total display records after applying the filter
      def countQuery = new StringBuilder("select count(*) from Person as p where (${filter})")
      def result = Person.executeQuery(countQuery.toString(),
         [filter: "%${params.sSearch}%"])
      if ( result ) {
         dataToRender.iTotalDisplayRecords = result[0]
      }
      people = Person.findAll(query.toString(),
         [filter: "%${params.sSearch}%"],
         [max: params.iDisplayLength as int, offset: params.iDisplayStart as int])
   } else {
   people = Person.findAll(query.toString(),
      [max: params.iDisplayLength as int, offset: params.iDisplayStart as int])
   }

   // Process the response
   people?.each { person ->
      def record = []
      propertiesToRender.each { record << person."${it}" }
      dataToRender.aaData << record
   }

   render dataToRender as JSON
}

Something to Look At

My grails-app/views/people/index.gsp view is where the magic happens. This is where i created my jQuery DataTables table. This is just the first cut at creating the table; we’ll be modifying it to support more features later on.

Note the use of the <jqDT:resources /> tag. This is conveniently provided by Lim Chee Kin’s jquery-datatables Grails plugin, and simplifies adding DataTables resources to our app.

<html>
   <head>
      <meta name="layout" content="main" />
      <title>People</title>
      <jqDT:resources />
      <g:javascript>
         $(document).ready(function() {
            $('#people').dataTable({
               sScrollY: '70%',
               bProcessing: true,
               bServerSide: true,
               sAjaxSource: '${request.contextPath + '/people/dataTablesData'}' ,
               sPaginationType: "full_numbers",
               aLengthMenu: [[100, 500, 1000, 5000, -1], [100, 500, 1000, 5000, "All"]],
               iDisplayLength: 500
            });
         });
      </g:javascript>
   </head>

   <body>
      <table id="people">
         <thead>
            <tr>
               <th>Id</th>
               <th>First Name</th>
               <th>Last Name</th>
               <th>Birth Date</th>
            </tr>
         </thead>
         <tbody></tbody>
         <tfoot>
            <tr>
               <th>Id</th>
               <th>First Name</th>
               <th>Last Name</th>
               <th>Birth Date</th>
            </tr>
         </tfoot>
      </table>
   </body>
</html>

Also notice that one of the columns is the database primary key for each person; later we’ll hide that column and use it via an AJAX call to a controller action to fetch the data for that row.

Start the Grails app and verify that you can see the list of people at http://localhost:8080/grails-jquery-datatables-example/people/index/. Play around with sorting columns, searching, etc to make sure everything is working properly.

Data for One

When we click on a row in the jQuery DataTables table, nothing happens (yet). We’re going to want to fetch data for the row, so I added another action to my PeopleController that will render that person’s data in a JSON representation.

// Don't forget to add imports for the JSON and XML converters.
def show = {
   def person = Person.get(params.id as int)

   if ( ! person ) {
      render(text: "No person with id ${params.id} found", status: 404)
   } else {
      withFormat {
         json {
            if ( params.callback ) {
               render(contentType: 'application/json',
                  text: "${params.callback}(${person as JSON})")
            } else {
               render person as JSON
            }
         }
         xml {
            render person as XML
         }
      }
   }
}

Make It RESTful

I want to access the people resource RESTfully, so I modified grails-app/conf/UrlMappings.groovy. This will allow me to access people as follows:

• GET /people – Lists all people using the default representation (XML)
• GET /people.json – Lists all people in a JSON representation
• GET /people.xml – Lists all people in an XML representation
• GET /people/1 – Get a specific person using the default representation (XML)
• GET /people/1.json – Get a specific person in a JSON representation
• GET /people/1.xml – Get a specific person in an XML representation
• GET /people/index – Show the index view that has our jQuery DataTables table
• GET /people/dataTablesData – Get paginated data in JSON for the jQuery DataTables table

class UrlMappings {
   static mappings = {
      "/people/index/"(controller:"people") {
         action=[GET:"index"]
      }
      "/people/dataTablesData/"(controller:"people") {
         action=[GET:"dataTablesData"]
      }
      "/people"(controller:"people") {
         action=[GET:"list"]
      }
      "/people/$id"(controller:"people") {
         action=[GET:"show"]
      }

      "/$controller/$action?/$id?"{
         constraints {
            // apply constraints here
         }
      }

      "/"(view:"/index")
      "500"(view:'/error')
   }
}

Hide the Id Column

Nobody really cares about actually seeing the database id for each Person. Fortunately DataTables makes it easy to hide columns. Back in grails-app/views/people/index.gsp add an aoColumns property to your call to .dataTable():

aoColumns: [
   /* Id */         {bVisible: false},
   /* First Name */ null,
   /* Last Name */  null,
   /* Birth Date */ null
]

Clickin’ Away

Add a row click hander to each row that gets the row’s id, and makes an AJAX call to the PeopleController show action to get the data for the row that was clicked on. Back in grails-app/views/people/index.gsp add the fnRowCallback property to your call to .dataTable()

fnRowCallback: function(nRow, aData, iDisplayIndex) {
   $(nRow).click(function(){
      var id = aData[0];
      $.ajax({
         dataType: 'json',
         url: '${request.contextPath + '/people/'}' + id + '.json',
         success: function(data, status, xhr) {
            // TODO: we'll modify this next to create and display
            // a jQuery dialog using the data
            alert(JSON.stringify(data, null));
         },
         error: function(xhr, status, err) { },
         complete: function(xhr, status) { }
      });
   });
   return nRow;
}

The fnRowCallback is invoked on each row when the row is rendered, so it’s an ideal place to add a callback to the row.

Show Me the Person

Use the JSON response from the PeopleController show action to create and display a jQuery dialog. Modify the AJAX success callback function:

function(data, status, xhr) {
   var dl = $('<dl></dl>')
      .append('<dt>Id:</dt><dd>' + data.id +'</dd>')
      .append('<dt>First name:</dt><dd>' + data.firstName +'</dd>')
      .append('<dt>Last name:</dt><dd>' + data.lastName +'</dd>')
      .append('<dt>Birth date:</dt><dd>' + data.birthDate + '</dd>')
      .append('<dt>Date created:</dt><dd>' + data.dateCreated + '</dd>')
      .append('<dt>Last updated:</dt><dd>' + data.lastUpdated + '</dd>');

   $('<div></div>')
      .append(dl)
      .dialog({
         modal: true
      });
}

That’s it! Feel free to create and style the dialog how ever you want. If you want to be really slick, you could cache the JSON response using HTML5′s localStorage (if your browser supports it) so that repeated clicks on a row don’t cause a call to the show action every time.

About these ads