DataTables is a great tool which you can use to easily manipulate with tables in your web apps by adding pagination, sorting and searching through JavaScript. But if you need to manipulate with a table which has thousands of records you need to use server side processing and that can be a little difficult to accomplish in the right way. There is a great railscast episode about this but it's out of date since DataTables changed a lot since then. There is also a gem that you can use to solve this problem but it doesn't support Oracle and I had a hard time configuring it in a way that I needed to, so I decided to do it by myself from scratch but based on code provided in Ryan Bytes' episode.
A PROBLEM
Let's say you need to create an index page with products listed in a table with search bar on top and you need to use submitted search parameters to search for records in your database by product code and product name. Btw, I used Spawner Data Generator to generate enough records so I can test server side processing properly.
General information
For this purpose we will be using rails 4 with HAML, CoffeScript, Zurb Foundation as front-end framework, kaminari for pagination and Font Awesome for icons, so you need to configure your gemfile to include all of these. Don't forget to run 'bundle install':
source 'https://rubygems.org'
gem 'rails', '4.0.4' # Bundle edge Rails instead: gem 'rails', github: 'rails/rails'
gem 'sass-rails', '~> 4.0.2' # Use SCSS for stylesheets
gem 'coffee-rails', '~> 4.0.0' # Use CoffeeScript for .js.coffee assets and views
gem 'jquery-rails' # Use jquery as the JavaScript library
gem 'jquery-ui-rails' # JQuery UI Library
gem 'jquery-datatables-rails' # Use datatables as default tables in the application
gem 'font-awesome-rails' # Icons
gem 'activerecord-oracle_enhanced-adapter', '~> 1.5.0' # Oracle database adapter
gem 'ruby-oci8' # Ruby interface for Oracle using OCI8 API
gem 'foundation-rails' # zurb foundation
gem 'simple_form' # Simple form builder
gem 'haml-rails' # Use haml processing in views
gem 'kaminari' # Pagination
gem 'turbolinks' # Turbolinks makes following links in your web application faster
gem 'jquery-turbolinks'
gem 'thin'
gem 'thin_service'
Also, don't forget to change your application.css and application.js files:
/*
*= require font-awesome
*= require_self
*= require foundation_and_overrides
*= require dataTables/jquery.dataTables.foundation
*= require_tree .
*/
//= require jquery
//= require jquery.turbolinks
//= require jquery_ujs
//= require foundation
//= require dataTables/jquery.dataTables
//= require dataTables/jquery.dataTables.foundation
//= require turbolinks
//= require_tree .
Models
Let's assume we have Category model (code, name) and Product model (code, name, description, category), so Product model has one 'belongs_to' association and we need to put category name in product table on our index page. I won't post code that represents these models since there would be too much code in this post and it is very easy to code this.
INDEX page, CONTROLLER, ROUTES, JAVASCRIPT
Now let's get to the point... In your index page you need to wrap the header row inside a thead element and the body section in a tbody element. Nothing more than that since you are going to render records after you get them from the server. I added one additional column here - ID of the record which we won't display but it is fun to add more tricks to the code and you can later use it to select a row from a table and do something with it. Additionally, there is a search field where user can enter some text to search by product code or name. Later we will see how we are going to use this.
.row
.medium-12.columns
= form_tag products_index_path, method: 'get' do
.row
.medium-4.columns
%b Product code:
= text_field_tag :product_code, params[:product_code]
.medium-4.columns
%b Product name:
= text_field_tag :product_name, params[:product_name]
.medium-4.columns
.row
.medium-2.columns
= button_tag type: 'submit', style: 'color:#FFFFFF;', class: 'button tiny expand' do
%i.fa.fa-search.fa-lg
.medium-10.columns
.row
.separator
%br
.medium-12.columns
%table#product_table
%thead
%tr
%th{style: 'display: none'} ID
%th Code
%th Name
%th Description...
%th Category
%th
%th
%th
%tbody
Another thing we need to do in this step is to add some code to the controller and to configure routes:
class ProductsController < ApplicationController
def index
end
def datatable_ajax
render json: ProductsDatatable.new(view_context)
end
Test::Application.routes.draw do
resources :products
get 'products_ajax/datatable_ajax', to: 'products#datatable_ajax'
end
As you can see, the index action is empty. I didn't want to put code for fetching records for DataTables here since index action simply isn't for that. In index action you want to do something with the page in general and data in the page, for example fetch some other data besides data for the table. For that reason, we created separate action just for that purpose and that action will return just json, nothing else. The last thing in this step is to create products.js.coffee file where we can tell DataTables all information about the table and server side processing:
$ ->
product_table = $('#product_table').DataTable
processing: true
serverSide: true
ajax:
url: '/products_ajax/datatable_ajax'
data: (d) ->
d.product_code = $('#product_code').val()
d.product_name = $('#product_name').val()
return
columns: [
{ width: "0%", className: "dont_show", searchable: false, orderable: false }
{ width: "15%" }
{ width: "35%", className: "row_config" }
{ width: "null", className: "row_config", searchable: false, orderable: false }
{ width: "null", className: "row_config", searchable: false, orderable: false }
{ width: "5%", className: "center", searchable: false, orderable: false }
{ width: "5%", className: "center", searchable: false, orderable: false }
{ width: "5%", className: "center", searchable: false, orderable: false }
]
order: [ [1,'asc'] ]
You can arrange your columns as you wish, I did it in this way. There is a class name for the first column since we don't want to display column with ID, so you need to add some CSS in here, for example in products.css.scss file. 'row_config' class name tells to each table cell that if content in that cell is too long, the content won't be broken in additional lines - the content will be displayed in that cell as is, with dots (...) if content is longer. In that way you keep your table well formatted.
.row_config {
max-width: 200px;
text-overflow: ellipsis;
white-space: nowrap;
overflow: hidden;
}
.dont_show {
display: none;
}
One more thing - we will send additional parameters via DataTable using this 'data' part, You can send as much additional parameters as you like. So, when you perform some action on the table, DataTables will collect other information from the page and send them to the url you defined.
AJAX PART
And finally, here is the main part of the application. In this section we will perform everything needed to fetch, prepare and display data in a proper way:
class ProductsDatatable
delegate :params, :fa_icon, :link_to, :products_path, :edit_products_path, to: :@view
def initialize(view)
@view = view
end
def as_json(options = {})
{
data: data,
recordsTotal: my_search.count,
recordsFiltered: sort_order_filter.count
}
end
private
def data
products = []
display_on_page.map do |record|
product = []
product << record.id
product << record.code
product << record.name
product << record.description
product << (record.category.present? ? record.category.name : '')
product << link_to(fa_icon('info-circle lg'), products_path(record), class: 'label success round')
product << link_to(fa_icon('edit lg'), edit_products_path(record), class: 'label secondary round')
product << link_to(fa_icon('trash-o lg'), products_path(record), method: :delete, data: { confirm: 'Are you sure?' }, class: 'label alert round')
products << product
end
products
end
def my_search
@filtered_products = Products.filter_product_code(params[:product_code]).filter_product_name(params[:product_name]).some_additional_scope.distinct.includes(:category)
end
def sort_order_filter
records = my_search.order("#{sort_column} #{sort_direction}")
if params[:search][:value].present?
records = records.where("PRODUCTS.CODE like :search or lower(PRODUCTS.NAME) like :search", search: "%#{params[:search][:value]}%")
end
records
end
def display_on_page
sort_order_filter.page(page).per(per_page)
end
def page
params[:start].to_i/per_page + 1
end
def per_page
params[:length].to_i > 0 ? params[:length].to_i : 10
end
def sort_column
columns = %w[not_orderable PRODUCTS.CODE lower(PRODUCTS.NAME) not_orderable not_orderable]
columns[params[:order][:'0'][:column].to_i]
end
def sort_direction
params[:order][:'0'][:dir] == "desc" ? "desc" : "asc"
end
end
This file is too long to explain every single detail, but you can use railscast episode as a guide. Also, you can notice that you need to define scopes in your Product model class and use it to filter data according to the parameters submitted by the user. The scope can be defined in this way for example:
scope :filter_product_name, -> (product_name) {where("lower(PRODUCTS.NAME) like :search", search: "%#{product_name.downcase}%")}
You maybe noticed that I use LOWER function a lot. It's Oracle's function you can use to down-case your string, so search can be performed regardless of the way user entered search parameter. You can also use this when you specify sort columns as shown above. I am not sure if you can customized your searches and sorts in this way using gem I mentioned in the beginning.
THE END
I hope this helps a little. It's pretty easy to configure your table with DataTables if you know the basics, but for beginners it can be really hard to collect all the pieces and make a functional solution.
RSS Feed
