Important
Datatables recently released version 1.10 (which includes a new API and features) and deprecated version 1.9.
This gem is targeted at Datatables version 1.10 and up.
Datatables is a nifty jquery plugin that adds the ability to paginate, sort, and search your html tables. When dealing with large tables (more than a couple hundred rows) however, we run into performance issues. These can be fixed by using server-side pagination, but this breaks some datatables functionality.
ajax-datatables-rails is a wrapper around datatable's ajax methods that allow
synchronization with server-side pagination in a rails app. It was inspired by
this Railscast. I needed to
implement a similar solution in a couple projects I was working on, so I
extracted a solution into a gem.
Currently AjaxDatatablesRails only supports ActiveRecord as ORM for
performing database queries.
Adding support for Sequel, Mongoid and MongoMapper is a planned feature
for this gem. If you'd be interested in contributing to speed development,
please open an issue
and get in touch.
Add these lines to your application's Gemfile:
gem 'jquery-datatables-rails'
gem 'ajax-datatables-rails'
And then execute:
$ bundle
The jquery-datatables-rails gem is listed as a convenience, to ease adding
jQuery dataTables to your Rails project. You can always add the plugin assets
manually via the assets pipeline. If you decide to use the
jquery-datatables-rails gem, please refer to its installation instructions
here.
The following examples assume that we are setting up ajax-datatables-rails for
an index page of users from a User model, and that we are using postgresql as
our db, because you should be using it, if not, please refer to the
Searching on non text-based columns
entry in the Additional Notes section.
Run the following command:
$ rails generate datatable User
This will generate a file named user_datatable.rb in app/datatables.
Open the file and customize in the functions as directed by the comments.
Take a look here for an explanation about the generator syntax.
You should always start by the single source of truth, which is your html view. Suppose we need to render a users table and display: first name, last name, and bio for each user.
Something like this:
| First Name | Last Name | Brief Bio |
|---|---|---|
| John | Doe | Is your default user everywhere |
| Jane | Doe | Is John's wife |
| James | Doe | Is John's brother and best friend |
- Set up an html
<table>with a<thead>and<tbody> - Add in your table headers if desired
- Don't add any rows to the body of the table, datatables does this automatically
- Add a data attribute to the
<table> 8000 ;tag with the url of the JSON feed, in our case is theusers_pathas we're pointing to theUsersController#indexaction
<table id="users-table", data-source="<%= users_path(format: :json) %>">
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Brief Bio</th>
</tr>
</thead>
<tbody>
</tbody>
</table>def view_columns
# Declare strings in this format: ModelName.column_name
# or in aliased_join_table.column_name format
@view_columns ||= []
end- In this method, add a list of the model(s) columns mapped to the data you need to present. In this case:
first_name,last_nameandbio.
This gives us:
def view_columns
@view_columns ||= %w(User.first_name User.last_name User.bio)
# this is equal to:
# @view_columns ||= ['User.first_name', 'User.last_name', 'User.bio']
end- See here for notes about the
view_columnssettings (if using something different frompostgre). - Read these notes about
considerations for the
view_columnsmethod.
def data
records.map do |record|
[
# comma separated list of the values for each cell of a table row
# example: record.attribute,
]
end
endThis method builds a 2d array that is used by datatables to construct the html table. Insert the values you want on each column.
def data
records.map do |record|
[
record.first_name,
record.last_name,
record.bio
]
end
endSee here if you need to use view helpers in the
returned 2d array, like link_to, mail_to, resource_path, etc.
def get_raw_records
# insert query here
endThis is where your query goes.
def get_raw_records
# suppose we need all User records
# Rails 4+
User.all
# Rails 3.x
# User.scoped
endObviously, you can construct your query as required for the use case the
datatable is used. Example: User.active.with_recent_messages.
IMPORTANT: Make sure to return an
ActiveRecord::Relationobject as the end product of this method.Why? Because the result from this method, will be chained (for now) to
ActiveRecordmethods for sorting, filtering and pagination.
The previous example has only one single model. But what about if you have some associated nested models and in a report you want to show fields from these tables.
Take an example that has an Event, Course, Coursetype, Allocation, Teacher, Contact, Competency and CompetencyType models. We want to have a datatables
report which has the following column:
'coursetypes.name',
'courses.name',
'events.title',
'events.event_start',
'events.event_end',
'contacts.full_name',
'competency_types.name',
'events.status'We want to sort and search on all columns of the list. The related definition would be:
def view_columns
@view_columns ||= [
'Coursetype.name',
'Course.name',
'Event.title',
'Event.event_start',
'Event.event_end',
'Contact.last_name',
'CompetencyType.name',
'Event.status'
]
end
def get_raw_records
Event.joins(
{ course: :coursetype },
{ allocations: {
teacher: [:contact, {competencies: :competency_type}]
}
}).distinct
endSome comments for the above code:
-
In the
get_raw_recordsmethod we have quite a complex query having one to many and may to many associations using the joins ActiveRecord method. The joins will generate INNER JOIN relations in the SQL query. In this case, we do not include all event in the report if we have events which is not associated with any model record from the relation. -
To have all event records in the list we should use the
.includesmethod, which generate LEFT OUTER JOIN relation of the SQL query. IMPORTANT: Make sure to append.references(:related_model)with any associated model. That forces the eager loading of all the associated models by one SQL query, and the search condition for any column works fine. Otherwise the:recordsFiltered => filter_records(get_raw_records).count(:all)will generate 2 SQL queries (one for the Event model, and then another for the associated tables). The:recordsFiltered => filter_records(get_raw_records).count(:all)will use only the first one to return from the ActiveRecord::Relation object inget_raw_recordsand you will get an error message of Unknown column 'yourtable.yourfield' in 'where clause' in case the search field value is not empty.
So the query using the .includes() method is:
def get_raw_records
Event.includes(
{ course: :coursetype },
{ allocations: {
teacher: [:contact, { competencies: :competency_type }]
}
}
).references(:course).distinct
endSet the controller to respond to JSON
def index
respond_to do |format|
format.html
format.json { render json: UserDatatable.new(view_context) }
end
endDon't forget to make sure the proper route has been added to config/routes.rb.
Finally, the javascript to tie this all together. In the appropriate coffee file:
# users.coffee
$ ->
$('#users-table').dataTable
processing: true
serverSide: true
ajax: $('#users-table').data('source')
pagingType: 'full_numbers'
# optional, if you want full pagination controls.
# Check dataTables documentation to learn more about
# available options.or, if you're using plain javascript:
// users.js
jQuery(document).ready(function() {
$('#users-table').dataTable({
"processing": true,
"serverSide": true,
"ajax": $('#users-table').data('source'),
"pagingType": "full_numbers",
// optional, if you want full pagination controls.
// Check dataTables documentation to learn more about
// available options.
});
});Since version 0.3.0, we are implementing a pseudo code way of declaring
the array columns to use when querying the database.
Example. Suppose we have the following models: User, PurchaseOrder,
Purchase::LineItem and we need to have several columns from those models
available in our datatable to search and sort by.
# we use the ModelName.column_name notation to declare our columns
def view_columns
@view_columns ||= [
'User.first_name',
'User.last_name',
'PurchaseOrder.number',
'PurchaseOrder.created_at',
'Purchase::LineItem.quantity',
'Purchase::LineItem.unit_price',
'Purchase::LineItem.item_total'
]
endExample: what if the datatable is namespaced into an Admin module?
module Admin
class PurchasesDatatable < AjaxDatatablesRails::Base
end
endTaking the same models and columns, we would define it like this:
def view_columns
@view_columns ||= [
'::User.first_name',
'::User.last_name',
'::PurchaseOrder.number',
'::PurchaseOrder.created_at',
'::Purchase::LineItem.quantity',
'::Purchase::LineItem.unit_price',
'::Purchase::LineItem.item_total'
]
endPretty much like you would do it, if you were inside a namespaced controller.
It always comes the time when you need to add a non-string/non-text based
column to the @view_columns array, so you can perform searches against
these column types (example: numeric, date, time).
We recently added the ability to (automatically) typecast these column types
and have this scenario covered. Please note however, if you are using
something different from postgresql (with the :pg gem), like mysql or
sqlite, then you need to add an initializer in your application's
config/initializers directory.
If you don't perform this step (again, if using something different from
postgresql), your database will complain that it does not understand the
default typecast used to enable such searches.
You have two options to create this initializer:
- use the provided (and recommended) generator (and then just edit the file);
- create the file from scratch.
To use the generator, from the terminal execute:
$ bundle exec rails generate datatable:config
Doing so, will create the config/initializers/ajax_datatables_rails.rb file
with the following content:
AjaxDatatablesRails.configure do |config|
# available options for db_adapter are: :pg, :mysql, :mysql2, :sqlite, :sqlite3
# config.db_adapter = :pg
# available options for orm are: :active_record, :mongoid
# config.orm = :active_record
endUncomment the config.db_adapter line and set the corresponding value to your
database and gem. This is all you need.
Uncomment the config.orm line to set active_record or mongoid if
included in your project. It defaults to active_record.
If you want to make the file from scratch, just copy the above code block into
a file inside the config/initializers directory.
Sometimes you'll need to use view helper methods like link_to, h, mailto,
edit_resource_path in the returned JSON representation returned by the data
method.
To have these methods available to be used, this is the way to go:
class MyCustomDatatable < AjaxDatatablesRails::Base
# either define them one-by-one
def_delegator :@view, :link_to
def_delegator :@view, :h
def_delegator :@view, :mail_to
# or define them in one pass
def_delegators :@view, :link_to, :h, :mailto, :edit_resource_path, :other_method
# now, you'll have these methods available to be used anywhere
# example: mapping the 2d jsonified array returned.
def data
records.map do |record|
[
link_to(record.fname, edit_resource_path(record)),
mail_to(record.email),
# other attributes
]
end
end
endAn AjaxDatatablesRails::Base inherited class can accept an options hash at
initialization. This provides room for flexibility when required. Example:
class UnrespondedMessagesDatatable < AjaxDatatablesRails::Base
# customized methods here
end
datatable = UnrespondedMessagesDatatable.new(view_context,
{ :foo => { :bar => Baz.new }, :from => 1.month.ago }
)So, now inside your class code, you can use those options like this:
# let's see an example
def from
@from ||= options[:from].beginning_of_day
end
def to
@to ||= Date.today.end_of_day
end
def get_raw_records
Message.unresponded.where(received_at: from..to)
endAlso, a class that inherits from AjaxDatatablesRails::Base is not tied to an
existing model, module, constant or any type of class in your Rails app.
You can pass a name to your datatable class like this:
$ rails generate datatable users
# returns a users_datatable.rb file with a UsersDatatable class
$ rails generate datatable contact_messages
# returns a contact_messages_datatable.rb file with a ContactMessagesDatatable class
$ rails generate datatable UnrespondedMessages
# returns an unresponded_messages_datatable.rb file with an UnrespondedMessagesDatatable class
In the end, it's up to the developer which model(s), scope(s), relationship(s) (or else) to employ inside the datatable class to retrieve records from the database.
Tutorial for Integrating ajax-datatable-rails, on Rails 4.
IMPORTANT: this tutorial is deprecated on version 0.4.0, and applies to version 0.3.0 and below.
Part 2 The Datatables with ajax functionality
The complete project code for this tutorial series is available on github.
- Fork it
- Create your feature branch (
git checkout -b my-new-feature) - Commit your changes (
git commit -am 'Added some feature') - Push to the branch (
git push origin my-new-feature) - Create new Pull Request