Rails and datatables, lazy loading and using a single controller

Datatables are a fantastic way of displaying your data in a table, with lots of formatting and processing options. Datatables is open source code, and turn up all over the web. You can find the current release at datatables.net, an example of a datatable in operation is on that front page.

Whilst datatables are very cool, integrating them into Rails isn’t quite as easy as you might want. I’ve been following the instructions on the railscast on the topic, which is also predictably well written and clear, and gets you started well. Having said that, it has some elements that don’t quite fit what I wanted for my framework:

  1. It does either client side datatables, with the data sourced from re-processing your index page, or server-side datatables, but nothing in-between.  This has some performance impacts (discussed in a later post)
  2. It doesn’t use some of the additional elements I wanted, such as column re-ordering and editing in place
  3. It doesn’t use the filter classes, which I’d also like to use
  4. The way it integrated into the controller didn’t suit my view of how it should look (although noting I’m not exactly a ruby expert, what I think is elegant might not fit other people’s view of the same)

So this post covers the way that I integrated datatables into my framework, drawing heavily from other resources on the web as I go.

Firstly, I needed the base install and configuration. Here I am assuming a rails object called “Books”, with some subsidiary tables that it references such as book_weight, book_type etc. Refer to this post for more information on the table structure.

Within the Gemfile I included:

  gem 'jquery-datatables-rails'

and then ran bundle install. Note that I didn’t include the github references that the railscast suggests, I reckon that railscast is just a bit out of date, it seems to work fine without.

Then within app/assets/javascripts/application.js, I added:

//= require jquery
//= require jquery_ujs
//= require dataTables/jquery.dataTables
//= require dataTables/extras/ColReorder.js

Note that I’ve included ColReorder.js, which was hard to find, and that I haven’t included the jquery-ui, which is only needed if you’re using flash styles. At the current time my framework uses twitter bootstrap, and it’s not clear to me that the styles that Datatables uses as standard work well with that.

Next, in app/assets/stylesheets/application.css.scss I added:

 *= require dataTables/jquery.dataTables

Then, we get on to integrating with the index view itself. What we’re planning to do is to have the initial html get return only a blank table, which your browser can go ahead and render. Behind the scenes we’ll use AJAX to go and get the data, and then populate the data into the page that’s already there. The logic here is that users feel better about the performance of your application if they can see something come up, and are OK to wait a bit as the rest of the data appears. In my local environment I’m getting about 1s for the initial screen pop, then another 2 seconds for the 1,000 rows of data. I think that’s OK, obviously it’d be faster with less data, and I think most of the application uses I can see would have less data. But it should behave reasonably gracefully with larger amounts of data, I probably wouldn’t do it this way beyond about 10,000 rows, or if each row is overly large.

So, we edit the index view to return a blank table app/views/books/index.html.erb. The really key things are to get the id field in here, it has to match what we put in the javascript next, and to get the data-source right, as that’s the URL that will be called to get the json data once the page has displayed:

  <h1>Listing Books</h1>
  <div>   
    <table id = "books" data-source="<%= books_url(:format => "json") %>">
      <thead>
      </thead>
      <tbody>
      </tbody>
    </table>
    <p>&nbsp</p>
    <%= link_to 'New Book', new_book_path() %>
  </div>

Next, we edit the app/assets/javascripts/books.js.coffee. We're doing a lot in here, so we'll show it then explain it:

jQuery -> 
    $('#books').dataTable
          sDom: 'Rlfrtip'
          bProcessing: true
          aoColumns: [
            { sTitle: "Title", mData: "name"}
            { sTitle: "Book Type", mData: "book_type_name"}
            { sTitle: "Book Weight", mData: "book_weight_name"}
            { sTitle: "Book Description", mData: "book_description"}
            { sTitle: "Book Type Id", mData: "book_type_id", bVisible: false, bSearchable: false}
            { sTitle: "Book Weight Id", mData: "book_weight_id", bVisible: false, bSearchable: false}
#            { sTitle: "Create User Id", mData: "create_user_id", bVisible: false  }
            { sTitle: "Create User", mData: "create_user_name", bVisible: false  }
            { sTitle: "Created At", mData: "created_at", bVisible: false, bSearchable: false }
            { sTitle: "Id", mData: "id", bVisible: false, bSearchable: false }
            { sTitle: "Lock Version", mData: "lock_version", bVisible: false, bSearchable: false}
#            { sTitle: "Update User Id", mData: "update_user_id", bVisible: false }
            { sTitle: "Update User", mData: "update_user_name", bVisible: false }
            { sTitle: "Updated At", mData: "updated_at", bVisible: false, bSearchable: false} ]
          bDeferRender: true
          sAjaxSource: $('#books').data('source')
          sPaginationType: "full_numbers"
          bStateSave: true

OK, let’s work through these one by one.

  • jQuery -> $('books').dataTable: This tells the datatables code that we have a datatable on our page called books. It looks at the index.html.erb to find that table (hence why this name must match)
  • sDom: "Rlfrtip": this tells datatables where we’d like each of the widgets to turn up within the page – this logic lets you, for example, put the information section (showing 1-100 of 2000 records) at the top or the bottom by moving around the items. Refer the documentation.
  • bProcessing: true: this tells it to put up a “processing” message whilst the data is being fetched
  • aoColumns: This block is telling datatables what columns we want on our datatable, and what the associated elements are in the JSON response so as to populate them
  • { sTitle: "Book Type", mData: "book_type_name"}: This is telling datatables that we want a column whose title on the screen is “Book Type”, and whose data can be found in the JSON in the element called “book_type_name”
  • { sTitle: "Created At", mData: "created_at", bVisible: false, bSearchable: false }: This is telling datatables that we have an element called “Created At” on the screen, and that can be found in the “created_at” element in the JSON. We don’t want this item visible by default (so the column is hidden), and when the user uses the search widget at the top, we don’t want it looking in this field either
  • In general I’ve put the hidden columns to the right, and columns that hold ids that aren’t likely to be useful to the user (so other than the book id) I’ve marked as not searchable, so as not to confuse the user when they get a match on a column that they maybe didn’t expect.
  • bDeferRender: true:This tells datatables to only render the actual page we’re seeing, and defer the rendering for all the other pages of data until someone actually clicks on them. It makes the UI feel a little more snappy in theory (although to be honest I couldn’t tell the difference, maybe my computer isn’t slow enough, or maybe the rendering isn’t complex enough yet)
  • sAjaxSource: $('#risks').data('source'): This tells the datatable to use the data source that was set in the index.html.erb to get the JSON data
  • sPaginationType: "full_numbers": This changes the pagination display at bottom to show a few pages either side, rather than just previous and next
  • bStateSave: true: This tells datatables to save the column orders and potentially other stuff when you leave the page. When you come back next time, it should be as you left it. I think it’s using a cookie for this.
  • I’ve commented out a couple of lines – these are the columns that I excluded in the controller below, I’m still thinking about whether I want them or not, so haven’t deleted them

Next, we need to add code to the app/controllers/books.rb to both serve up a nil response for the initial html get, and then to provide the full set of books when the JSON fetch is called. The index code should look as follows (and note the previous post on this blog that talked about how we’re fetching the data in some detail):

  def index
    @books = nil     # the html version now returns nil, don't want rails getting without us asking it to

    respond_to do |format|
      format.html  # index.html.erb 
      format.json {

  @books = Books.all.joins(:book_type, 
                           'LEFT OUTER JOIN book_weights on books.book_weight_id = book_weights.id',
                           'LEFT OUTER JOIN users as create_user on books.create_user_id = create_user.id',
                           'LEFT OUTER JOIN users as update_user on books.update_user_id = update_user.id')\
                    .select('books.*, 
                             book_types.name as book_type_name,
                             book_weights.name as book_weight_name,
                             create_user.name as create_user_name,
                             update_user.name as update_user_name') 
        render :content_type => 'application/json',
               :text => '{"aaData": ' + 
                        @books.to_json(:except => [:create_user_id, :update_user_id]) + '}' }         
    end
  end

So, to discuss what this is doing. The joins and select statements are pushing all the fetch into a single SQL query, as discussed in the previous blog post. We’re then taking the results of that and sending it back in JSON format, in order to do that we’re manually formatting in the format that datatables expects, which is broadly like:

  {"aaData": [{"Title1":"Value","Title2":"Value2"},{"Title1":"Value","Title2":"Value2"}]}

In theory, if you’ve done all this, you should be able to go to your index page, and now have a data table presenting in there, with moveable columns, filtering, sorting and delayed population.

If you’re having problems with your datatable, you can debug it with a nifty web tool. Basically you go to debug.datatables.net, and bookmark the link near the top right. You go to your page that has a datatable on it that isn’t working properly, and then hit the bookmark you just created in your browser. This uploads information about your datatable, and gives you a pretty display of exactly what’s going on.  You can also (if you’re using firefox) go to the developer tools tab, and use the console or other tool to see what’s going on.

Advertisements

4 thoughts on “Rails and datatables, lazy loading and using a single controller

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s