RAILS 4 + DATATABLES 1.10 + AJAX

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.