Supercharging Padrino admin with Datatables, will_paginate, and RABL
One of the best things about Padrino is the drop-in
admin generator (inspired by Django’s) that makes building simple, robust apps
that need to work, run, and be admin-ed by real people a pleasure to work with.
At some point though, the natural limits of the default generator will need to
be extended as you return huge numbers of records. This is one battle-tested
approach we used to be able to paginate and search across 7000+ records via ajax
and DataTables setup with will_paginate when the
default Padrino admin hit its limits.
Pre-reqs
This solution has been running for 3+ years and is currently confirmed to be working with a padrino setup running 0.14.1.1, active record 5.0.3, oj 3.0.11 (for faster json rendering), will_paginate 3.1.5, and rabl 0.13.1. We’re using puma and postgres in production and running this on heroku though the setup is robust enough it should work anywhere.
The Problem
Basically, our app tracks amphibian species’ programs globally for a major conservation organization. We wanted to increase the responsiveness of the app by using ajax calls to return rapid search result in a nice sortable and filterable format that was lightning fast and extensible to help drive the user experience (and often people would be using the app in low-bandwidth regions globally so we wanted to prevent full page loads.). Full page reloads disoriented users slightly, whereas a nice quick filtering ability was clearly a fan favourite.
Enter DataTables
We were already using Datatables, in other, smaller views since for view that returned smaller number of records, it provided a quick, filterable, sortable interface for people to manipulate the data in the returned results set (and often users wanted to play with all the results at once.) in a nice, paginated (configurable between 10, 20 and 50 records) way that was a hit with our global user base.
DataTables is effectively a client-side jQuery plugin that takes all the table data rendered in a view by padrino and turns that into a table you can filter, slice and dice to get to the info you want. It’s fast, robust, and still under active development though alternatives have cropped up in the intervening three years since we first implemented it and it was the most mature choice.
It’s impractical to load and render over 7000 species in a single view and then have datatables attempt to manipulate it. The idea was to fake as if the entire table were loaded by firing off a search, pagination, and sort order to the database as an ajax request, replace that in the background on return and make it look as if the entire database result were actually loaded. It’s a nice bit of sleight of hand that plenty of sites use, but the trick was having it work so that you also had access to the admin abilities (and security of the Padrino admin backend.).
The infrastructural pieces
So, the simple idea was have the Search parameter fire off a search request to the database, have the view respond to a json request and then render that json request in a format that would make DataTables happy to render the parameterized “slice” of the database we wanted.
There were three extra pieces you needed to make this work: something to paginate the database results (ie. give me 10 records for what would be page 20 of 700), something to speed up the json rendering, and then a template system (like haml or erb) to speedily render the json to the view for DataTables.
will_paginate
Being a long time Rails abuser, I’ve been using mislav’s will-paginate gem for
just about every ActiveRecord pagination issue for just about forever. It’s
simple to understand, drops-in painlessly as a gem to Rails or Sinatra, and is
fast, robust, and very actively maintained (just checking now, the last update
was 8 days ago.). This was a no brainer.
oj
OJ is a fast json parser and object marshaller. JSON parsing on ruby was historically a lot slower than it is natively today, so picking oj as a way to quickly parse and return responses seemed like an obvious drop-in optimization at the time we started using it. Again, actively maintained and has now hit 3.x, and they used to make a lot more comparisons about how much faster they were than the native json gem in ruby, but I’m v ery happy with its performance after a number of years.
RABL
Nesquena’s RABL (Ruby API Builder Language) “is a Rails and Padrino ruby templating system for generating JSON, XML, MessagePack, PList and BSON.” Effecitvely, it’s role was to take the data returned from the ajax json returned and put it into a format that was going to make DataTables happy (which has its own particular syntax in places.). While some might think of this as a just a nice to have, it ended up providing a much more structured and DRY approach to rendering the information needed.
Get DataTables Running
Assuming you’ve got a working padrino installation that is pulling data out of
and putting it into your database, let’s start with simply getting Datatables
working for you. We’ll use the example of our Species model which has over
7200 species of amphibians in it as a practical example of something we used
this on directly.
Unless there is a very good reason, I advise just using the CDN version of datatables available and adding it in that way. First of all, it’s much better for global users, and that js can be parallel loaded with other javascripts.
To make it look nice, and because I used bootstrap as the default theme for the admin backend (hey, it was all the rage 3 years ago), I’ve also got that js and css thrown in. In the layout for whatever your equivalent to the species index file is you want to add the following down near the bottom (I should point out I use haml for views, but you can puzzle out the same for erb or your templating language of choice. Note that application.haml is the default layout view if you fire off the admin generator.).
# /admin/views/layouts/application.haml
...
%script{:src => "https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"}
%script{:src => "https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"}
%script{:src => "https://cdn.datatables.net/1.10.13/js/jquery.dataTables.min.js"}
%script{:src => "https://cdn.datatables.net/1.10.13/js/dataTables.bootstrap.min.js"}
=javascript_include_tag 'application'
Even on a simple view now, all you need to do is provide an identifier for DataTables to know its a table it should do its magic on, and a javascript trigger in your application.js.
Just to show how this needs to work on a simpler model, before we get to the ajax versions, it looks a bit like this on one of your simpler, smaller models, like Accounts.
# /admin/views/accounts/index.haml
.tabs-content
%table.table.datatable#accounts.table-condensed
%thead
%tr
%th.header Full Name
%th.header Organization
%th.header Email
- if @current_permission.allow?('accounts', 'new', current_account)
%th.header Security
- else
%th.header
%th.header.list-row-action-header
%tbody
-@accounts.each do |account|
%tr.list-row
The key thing to note here is the %table.table.datatable#accounts which lets DataTable know its a table it should grab hold of, and then the #accounts which provides the id for us to provide direct information in our javascript for it.
From having this in the view, it’s just a matter of adding in the jquery directive in your application.js to trigger it on page load. Note that there is a little “magic incantation” code to get DataTables working and initialized, but once you’ve done that it’s a simple matter to trigger the #accounts Datatable above. We’ve used accounts since that is the base admin model that running the padrino generator gives you.
// /public/admin/javascripts/application.js
!function($) {
'use strict';
$(function() {
// Datatables server-side and options
// General options
$.extend( true, $.fn.dataTable.defaults, {
"sDom": "<'row'<'span6'l><'span6'f>r>t<'row'<'span6'i><'span6'p>>",
// "sPaginationType": "full numbers",
"oLanguage": {
"sLengthMenu": "_MENU_ records per page",
"sSearch": ""
}
});
// Default to add placeholder into the Datatables Search box
$('.dataTables_filter input').attr("placeholder", "Search");
// Accounts Datatable
$('#accounts').dataTable({
"aoColumnDefs": [
{ 'bSortable': true, 'aTargets': [ 0 ] },
{ 'bSortable': true, 'aTargets': [ 1 ] },
{ 'bSortable': true, 'aTargets': [ 2 ] },
{ 'bSortable': true, 'aTargets': [ 3 ] },
{ 'bSortable': false, 'aTargets': [ 4 ] }
]
});
});
}(window.jQuery);
OK, so we could have made this even simpler by putting in $('#accounts').dataTable(); but the issue would be that this would also allow you to sort the default admin column (the last one) that Padrino puts on the end of the row, which is undesired behaviour.
As an example, this is the haml for our accounts view in our app.
.container.subnav-fixed-top
=[:error, :warning, :success, :notice].map { |type| flash_tag(type, :class => "alert alert-#{type} fade in", :bootstrap => true) }.join.html_safe
%ul.nav.nav-tabs
%li.active=link_to tag_icon(:list, pat(:list)), url(:accounts, :index)
- if @current_permission.allow?('accounts', 'new', @accounts)
%li=link_to tag_icon(:plus, pat(:new)), url(:accounts, :new)
.tabs-content
%table.table.datatable#accounts.table-condensed
%thead
%tr
%th.header Full Name
%th.header Organization
%th.header Email
- if @current_permission.allow?('accounts', 'new', current_account)
%th.header Security
- else
%th.header
%th.header.list-row-action-header
%tbody
-@accounts.each do |account|
%tr.list-row
%td.list-column=account.full_name
%td.list-column
- if account.org.nil?
Problem: Account unlinked to Org
- else
=link_to account.org.name, url(:orgs, :show, :id => account.org_id)
%td.list-column=mail_to account.email
- if @current_permission.allow?('accounts', 'create', account)
%td.list-column=account.role
- else
%td.list-column
%td.list-column.list-row-action
.list-row-action-wrapper
- if @current_permission.allow?('accounts', 'edit', account)
=link_to tag_icon(:edit), :href => url(:accounts, :edit, :id => account.id), :rel => :tooltip, :title => "#{pat(:edit)} account", :class => 'list-row-action-wrapper-link'
- if @current_permission.allow?('account', 'create', account)
=link_to tag_icon(:bullhorn), url("#confirm-send-#{account.id}"), :class => 'list-row-action-wrapper-link', 'data-toggle' => 'modal', :rel => 'tooltip', :title => "Send an account invite"
- if @current_permission.allow?('account', 'create', account)
=link_to tag_icon('unlock-alt'), url("#confirm-reset-#{account.id}"), :class => 'list-row-action-wrapper-link', 'data-toggle' => 'modal', :rel => 'tooltip', :title => "Send a password reset"
- if @current_permission.allow?('account', 'destroy', account)
=link_to tag_icon("trash-o"), url("#delete-confirm-#{account.id}"), :class => 'list-row-action-wrapper-link', 'data-toggle' => 'modal', :rel => 'tooltip', :title => "Delete account"
(additional modal code etc removed for brevity)
(if you’re wondering about the general datatables block above that, that exists merely to make pagination look a little nicer and make the bootstrap theme work better. Leave it in if you’re following along and want a recipe rather than delving into some of the quirkier things in datatables and its formatting. YMMV.)
The above javascript tells Datatables that the first 4 columns (number 0-3) are columns that DataTables can sort the entire table on, but that the 4th column is not sortable. This becomes important when we get to the Species table since when we render that last column, we do not want people trying to sort the table on it (or this one for that matter.).
The nice thing about this with Datatables is that if you have small tables that render reasonably quickly, all the data gets loaded into the page and Datatables takes care of paginating it for you (with a nice default of 10 records generally) and will also filter the data if you type something into the Search field. It’s fast, efficient, and neat for the small tables use-case.
Now let’s see what we need to do when things get a little more complex where you simply cannot have the entire table render into the page.
Extending Datatables with server side search, pagination and ajax
For this more complex case, I’ll use the real-life example of our 7200+ species table which tracks all the amphibian species worldwide. All you really need to know about Species (ie. the associations are irrelevant to our example) is that it is a big table that we can’t render all the species in the view and then have Datatables sort out everything client side.
So, let’s first understand what we need to do here. We need to get the datatables client to send json requests that the controllers in our Padrino app controller know how to respond to a json requestt, send that information back to the datatables client, and have it render the results. That means, we need to have the server side understand how to handle pagination, sorting, searching/filtering and have the javascript directives in Datatables in the app.js make Datatables understand what to do (as well as understand where it needs to source the data from.). Sounds tricky, but once you’ve got the pattern down, it’s comprehensible and extensible across all your models.
Paginating the results
Make sure you’ve got will_paginate installed in your Gemfile and re-run bundle install in order to get it into the Gemfile.lock. At time of writing, will-paginate was at version 3.1.5. While we’re at it, let’s also install oj (a faster json parser) and rabl which will allow us to do more omcplex templating of the json we want to send back (not needed in all case, but it let’s you render the json back using templates much like haml, which will make your life way easier.)
# Gemfile
# Component requirements
gem 'will_paginate', '~>3.0'
gem 'oj'
gem 'rabl'
As the controller is where most of the magic of responding to the json requsst from Datatables happens, there are not a lot of changes you need to make to the model. However, as Datatables will no longer be handling the filtering/searching from all the records you have, you need to create a search function for your controller to lean on in the model.
# /models/species.rb
class Species < ActiveRecord::Base
has_many :programs
has_many :orgs, :through => :programs
has_many :assessments
has_many :orgs, :through => :assessments
# Validations
validates_presence_of :family, :order, :genus, :species
validates_uniqueness_of :name
before_validation :derive_species_name
def derive_species_name
self.name = self.genus + ' ' + self.species
end
def self.search(search)
if search
where 'LOWER(name) LIKE ? OR LOWER(has_phylogenetic_study) LIKE ?',
"%#{search.downcase}%", "%#{search.downcase}%"
else
Species.all
end
end
end
Pretty simply, self.search takes the incoming search parameter and then does a straight up SQL query on the DB to find any of the results that may be appropriate, take that subset and return them. If there is no search parameter, it returns the all Species. In both cases, the returned data from search is then further processed by the pagination in the controller.
So, let’s look at that now and how it affects our index list view of “all” Species:
# admin/controllers/species.rb
Aark::Admin.controllers :species do
...
get :index, :provides => [:html, :json] do
@title = "Species"
case content_type
when :html
render 'species/index'
when :json
@species = Species.search(params[:sSearch])
.paginate(:page => page, :per_page => per_page)
.order("#{species_sort_column} #{sort_direction}")
.includes([:assessments, :programs])
.references([:assessments, :programs])
if params[:sSearch].present?
@species = @species.search(params[:sSearch])
end
@species
partial 'species/datatable'
end
end
...
end
So, this is where most of the magic happens server-side. When the controller needs to respond to the request for json from the ajax call, it modifies the request for the Species model with three modifiers all passed from Datatables in the json parameters send with the request.
- Search parameters coming from Datatables sSearch parameter (basically, the text in the Search box)
- pagination which Datatables figures out from all records as which page it needs of how many per page (ie. page 10 of 10 records per page each to display records 100-110.).
- ordering which includes the sort direction (ie. ascending/descending) and which column is being sorted
From those things, the controller queries the Species model, and then sends the resulting data to the partial ‘species/datables’ which is a rabl template which formats the json heading to Datatables before it gets it.
Let’s take a quick look at that now, so it becomes obvious why we want to use it. In the case of our Species view, it looks a little like this:
# admin/views/species/_datatable.rabl
node do
{
'sEcho' => params[:sEcho].to_i || -1,
'iTotalRecords' => @totals ||= Species.all.count,
'iTotalDisplayRecords' => @filtered_count ||= @species.count
}
end
node :aaData do
@species.map do |species|
{
'0' => link_to(species.name, url(:species, :show, :id => species.id)),
'1' => species.in_captivity? ? 'Yes' : 'No',
'2' => species.has_phylogenetic_study,
'3' => species.programs? ? species.range_country_programs : 'None',
'4' => species.programs? ? species.non_range_country_programs : 'None',
'5' => species.new_program_required?,
'6' => species.programs? ? species.total_programs : 'None',
'7' => species_actions_list(species),
'DT_RowClass' => "list-row"
}
end
end
Without this nice formatting, we’d have to massage the raw json returned into a format that would make Datatables happy for the index view. As you can see, this allows us to send a nicely formatted json string back to Datatables in a format it can parse and display quickly including let it understand the total records and which one it is supposed to display as well as rendering code for us to allow links to be clickable and our species_actions_list which gives the final row of actions to be able to take on the actual species in each row.
For those of you wondering, species_actions_list is simply a helper which renders another partial. It made sense to extract all the helpers out of the system to keep things DRY.
# admin/helpers/datatables_helper.rb
Aark::Admin.helpers do
def species_actions_list(species)
render_partial 'species/row_actions', :locals => { :species => species }
end
end
But, effectively, it simply renders the following, which you could render directly from that rabl template though personally, I think this is a good pattern to follow as once you have a few models it can get very messy.
# admin/views/species/_row_actions.haml
.list-row-action-wrapper
- if @current_permission.allow?('programs', 'new', current_account)
= link_to tag_icon(:plus), :href => url(:programs, :new, :species_id => species.id), :rel => :tooltip, :title => "Add new program for species", :class => 'list-row-action-wrapper-link'
= link_to tag_icon(:eye), url(:species, :show, :id => species.id), :rel => :tooltip, :title => "#{pat(:show)} species", :class => 'list-row-action-wrapper-link'
- if @current_permission.allow?('species', 'edit', species)
= link_to tag_icon(:edit), url(:species, :edit, :id => species.id), :rel => :tooltip, :title => "#{pat(:edit)} species", :class => 'list-row-action-wrapper-link'
And seriously, that’s all it is (note the tag_icon is an included helper in the padrino admin and just refers to the font-awesome name of a font to render it for you. This is the code the padrino admin already uses.)
Alright, that’s the backend all sorted out. Not too tricky once you know how it works, but now we have to hook it up to the frontend so it all works together seamlessly. It’s surprisingly easy to hook up the datatable in ther index view.
# admin/views/species/index.haml
.container.subnav-fixed-top
=[:error, :warning, :success, :notice].map { |type| flash_tag(type, :class => "alert alert-#{type} fade in", :bootstrap => true) }.join.html_safe
%ul.nav.nav-tabs
%li.active=link_to tag_icon(:list, pat(:list)), url(:species, :index)
- if @current_permission.allow?('species', 'new', @species)
%li=link_to tag_icon(:plus, pat(:new)), url(:species, :new)
.tabs-content
%table.table.datatable#species.table-condensed{'data-source' => url(:species, :index), :format => :json}
%thead
%tr
%th.header Species
%th.header In Captivity?
%th.header Phylogenetic Study?
%th.header Range Country Programs
%th.header Non-Range Country Programs
%th.header New Program Needed?
%th.header All Programs
%th.header.list-row-action-header
The key part to notice in there is the %table.table.datatable#species.table-condensed{'data-source' => url(:species, :index), :format => :json}. If you remember the index view of the accounts, there was no data-source directive on the table. This tells datatables that it should be asking for changes to the table via json to the controller species/index which we just modified above. Easy-peasy.
Now, we just need to change our javascript in application.js in order to bring everything full circle.
Modify the application.js we’ve already created by inserting the following:
// public/admin/javascripts/application.js
// Species Datatable
$('#species').dataTable({
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": $('#species').data('source'),
// "oSearch": {"sSearch": "A"}, // sets the initial search in datatable
"aoColumns": [
{ "sClass": "list-column" },
{ "sClass": "list-column" },
{ "sClass": "list-column" },
{ "sClass": "list-column" },
{ "sClass": "list-column" },
{ "sClass": "list-column" },
{ "sClass": "list-column" },
{ "sClass": "list-column list-row-action" }
],
"aoColumnDefs": [
{ 'bSortable': true, 'aTargets': [ 0 ] },
{ 'bSortable': false, 'aTargets': [ 1 ] },
{ 'bSortable': true, 'aTargets': [ 2 ] },
{ 'bSortable': false, 'aTargets': [ 3 ] },
{ 'bSortable': false, 'aTargets': [ 4 ] },
{ 'bSortable': false, 'aTargets': [ 5 ] },
{ 'bSortable': false, 'aTargets': [ 6 ] },
{ 'bSortable': false, 'aTargets': [ 7 ] }
]
});
OK, a little more tricky to follow, but this is what is happening. The datatable gets initialized from the id on the haml view #species. We pass along the directives to let it know that it needs to do server side processing (rather than client side) and that it should use the data-source specified in the haml view (ie. "sAjaxSource": $('#species').data('source')). Then we pass along the classes for each of the columns (.list-column and the .list-row-action for our last row which we’ve already gone over.).
The other thing that gets passed along here in the json settings is which columns are sortable and which are not. Because a considerable number of the species rows are not things you can actually sort out (or would be nonsensical if you did allow it), we pass along a false to those columns that don’t make sense to provide sorting on.
Wrap up
And believe it or not, if you’ve done everything properly, you should be all set and have an ajax-enabled, server-side provided, and datatables rendered Species index view which is lightning fast and also provides in-datatable searching (by getting the search terms via ajax and returning them to the view.). It looks a little something like this if it’s all working properly: