Install

datatables.net install by simply select packages and download but it will be 3M in one file. I think it is better to use yarn and install all dependencies that you need

yarn add datatables.net-bs4

include it (both js and style in one line :)

# app/javascripts/packs/application.js

import 'datatables.net-bs4'

# app/javascript/turbolinks.load.js
document.addEventListener('turbolinks:load', () => {
  $('table').DataTable({
    dom: 'Bfrtip',
      buttons: [
        'copy', 'csv', 'excel', 'pdf', 'print'
      ]
  });
})

To check which datatable version you are using you can use

$table.DataTable.version
$table.DataTable.fnVersionCheck('1.10.12')

You can use jquery-datatables-rails gem. Add gem to Gemfile and run rails g jquery:datatables:install. If you have Uncaught TypeError: Cannot read property 'mData' of undefined than you need to replace <th colspan="3"></th> with three `<th></th> <th></th>

`

Data

Initial data could be inside (DOM) or you can use ajax call to load all items. That is client side processing and it works for less than 10 000 rows. For greater than 100 000 you should use server side processing. But problem is delay that occurs (at leat on rails) when it need to render 1000 rows, so we have to use server side processing. In that case we need to implement searching and ordering on server.

When using ajax you need to point where data array is in JSON object (dataSrc: '' if we return array, or dataSrc: 'data' if we return object with data array). We need to point where each column is on array item. If item is object we can use their keys columns: [ { data: 'name' }, { data: 'price' } ]. If item is also array we can use their index columns: [ { data: 0 }, { data: 1 } ]. We can also omit columns definition than it will use first column, first data from array, second column second data …

Server side processing

Server side processing is enabled with serverSide: true and use data or ajax options. Note that in this case any existing data will be discarded (you can not mix).

There was a forum question to add data manually deferred loading client side Best option is defer loading https://datatables.net/reference/option/deferLoading Just add option deferLoading: <%= @users.count %> than initially data from the page will be shown, than on any search, reorder or page, server side request will be made. Use array notation when there was a filter on first serverside rendering. Note that you can not use data-order and data-search on initial page (error is like datatables warning requested unknown parameter '[ojbect Object]' for row 0 column 7). Note that when deferred loading is enabled, you can not use stateSave: true option (which will remember sort and search inside localstorage).

If you have long column names and a lot of columns (for example 10), than it could be that url is too long, you can see in console 414 (Request-URI Too Large). Than you should switch to method POST and since post is used to create items, you need to use another route search_products_path format: :json

Request send a lot of params:

  • "draw"=>"1" sequence counter so we know which one is last
  • "start"=>"0", "length"=>"10" for pagination
  • "search"=>{"value"=>"323", "regex"=>"false"} global search for all coulmns with searchable true
  • "order"=>{"0"=>{"column"=>"0", "dir"=>"asc"}} for ordering
  • "columns"=>{"0"=>{"data"=>"name", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}} name and other properties of columns

Server need to returns:

  • draw should be echo
  • recordsTotal total number before filtering
  • recordsFiltered total number after filtering
  • data array with data used in ajax option

Example

# Gemfile
# for pagination
gem 'will_paginate'

# app/assets/javascripts/products.coffee
jQuery ->
  $('#products').dataTable(
    ajax:
      url: $('#products').data('source')
      dataSrc: 'data'
      error: (xhr, message, error) ->
        flash_alert("Please refresh the page. #{error}")
    columns: [
      { data: 'name' }
      { data: 'price' }
      { data: 'description' }
    ]
    deferLoading: $('#products').data('total')
    serverSide: true
  )

# app/datatables/products_datatable.rb
# http://railscasts.com/episodes/340-datatables?autoplay=true
class ProductsDatatable
  delegate :params, :page, :per_page, to: :@view
  def initialize(view)
    @view = view
  end

  def as_json(options = {})
    {
      draw: params[:draw].to_i,
      recordsTotal: Product.count,
      recordsFiltered: products.total_entries,
      data: data,
    }
  end

  private

  def data
    products.map do |product|
      product.slice :name, :price, :description
    end
  end

  def products
    @products ||= fetch_products
  end

  def fetch_products
    products = Product.order("#{sort_column } #{sort_direction}")
    products = products.page(page).per_page(per_page)
    if params[:search][:value].present?
      # ILIKE is case insensitive LIKE
      sql = "name ILIKE :search OR description ILIKE :search"
      # you can reference association but than references is needed
      # sql += " OR customers.name ILIKE :search"
      # sql += " OR DATE_FORMAT(updated_at, '%d-%b-%Y') ILIKE :search"
      products = products
        .where(sql, search: "%#{params[:search][:value]}%")
        # .include(:customer)
        # .references(:customer)
    end
    products
  end

  def page
    params[:start].to_i / per_page + 1
  end

  def per_page
    params[:length].to_i > 0 ? params[:lenght].to_i : 10
  end

  def sort_column
    columns = %w[name price description]
    columns[params[:order]["0"][:column].to_i]
  end

  def sort_direction
    params[:order]["0"][:dir] == "desc" ? "desc" : "asc"
  end
end

# app/controllers/products_controller.rb
class ProductsController < ApplicationController
  def index
    @products = Product.all.limit 10
  end

  def search
    render json: ProductsDatatable.new(view_context)
  end
end

<%# app/views/products/index.html.erb %>
<table id="products" data-source="<%= products_path format: :json %>" data-total="<%= Product.count %>">
  <thead>
    <tr>
      <th>Name</th>
      <th>Price</th>
      <th>Description</th>
    </tr>
  </thead>

  <tbody>
    <% @products.each do |product| %>
      <tr>
        <td><%= product.name %></td>
        <td><%= product.price %></td>
        <td><%= product.description %></td>
      </tr>
    <% end %>
  </tbody>
</table>

If you have nested models, you can order by customers.name asc and search customers.name like '%a%', but you should use references since includes works only with hash params: includes(:customer).where("customers.name like '%d%').references(:customer).

Using data from datatables

Columns are defined using

  • columns option which define all columns by its index (zero based). Note that all columns need to be defined
  • columnDefs targets key which can use: number (index or negative index), string (class name without dot on th element) targets: 'username' for <th class="username">. Note that we do not need to define all columns, just those that we need to set up some property columnDefs: [ { targets: '_all', visible: false } ]

Columns can be selected column-selector by:

  • its index
  • jQuery selector
  • columns.name (names should be defined).

Export

export to csv, pdf

  • you can configure that only visible columns will print
$('#clicks-table').DataTable({
  dom: 'B',
  buttons: [
    {
      extend: 'pdf',
      exportOptions: {
        columns: ':not(.non-for-export),:visible',
      }
    },
  ]
});

If you have special simbols, you can try to enable bom: true option.

Export server side datatable https://diogenesggblog.wordpress.com/2017/02/02/how-to-export-all-rows-from-datatables-using-server-side-ajax/

DOM

dom control buttons: l for lenght change menu, B buttons, t table, p pagination, r processing display, f filtering. You can add class move-up for info and length il.

<%# app/views/clicks/index.html.erb
$('#clicks-table').DataTable({
  dom: 'Bfrtp<"move-up"il>',
  processing: true,
  oLanguage: {
    // loader
    sProcessing: '<i class="fa fa-spinner fa-spin" style="font-size:24px"></i> Processing...'
  },
});

// app/assets/stylesheets/datatable.scss
@media(min-width:$screen-sm) {
  .move-up {
    margin-top: -50px;
    position: relative;
    z-index: -1;
  }
}

Seach (or filter is used for filtering out)

For search you can use html data attribute <td data-search="Novembar">2.2.2012</td>

  • when using data-search then original text is not used, so it is better to merge original text to data-search
  • when you are using <a href="some-text"> that some-text will be matched also
  • default search(input, regex, smart) is regex=false, smart=true, that means any order of strings and matching substring. For example my name is matched with my n a m m e e e e. You can also use double quotes "my name".
  • it is usefull to add param search term and using regex, for example dule|mile. Note that you HAVE TO disable smart search and use second param (false).
  table.search("<%= params[:search_term] %>", true, false).draw();

or you can search specific columns.

Select column

link

  table.columns( '.select-filter' ).every( function () {
    var that = this;

    // Create the select list and search operation
    var select = $('<select />')
      .appendTo(
        this.footer()
      )
      .on( 'change', function () {
        that
          .search( $(this).val() )
          .draw();
      } );

    // Get the search data for the first column and add to the select list
    this
      .cache( 'search' )
      .sort()
      .unique()
      .each( function ( d ) {
          select.append( $('<option value="'+d+'">'+d+'</option>') );
      });
  });

Order (sort)

  • disable ordering with orderable property or as data attribute on th element: <th data-orderable="false">
  • read about usage columns to define sort based on another column. Also hide some <td class="hidden"><%= index %></td> column both in css and in datatable hidden_columns
  "aoColumnDefs": [
    { "aDataSort": [1], "aTargets": [0] },
    { "bVisible": false, "bSearchable": false, "aTargets": [1] },
  ],
  • orthogonal data can be defined using html 5 attributes for ordering data-sort is <td data-order="<%= post.created_at.to_datetime.to_i %>">2 Novemeber</td>.

  • you can also achieve sorting if you have ajax data

    # app/views/users/index.json.erb
json.my_date do
  json.display user.my_date.to_s
  json.timestamp user.my_date.to_datetime.to_i
end
json.amount do
  json.display humanized_money_with_symbol user.amount
  json.cents user.amount.to_s
end
json.user do
  json.display user.name
  json.username user.username
end

# app/views/users/index.html.erb
columns: [
  { data: { _: 'my_date.display', sort: 'my_date.timestamp' } },
  { data: { _: 'amount.display', sort: 'amount.cents' } },
  { data: { _: 'user.display', filter: 'user.username' } },
  ]
  • ultimate date time sorting datetime input
  • select page length with lengthMenu option

Responsive

There are a lot of tools for responsive:

responsive: true # enable responsive styles
scrollX: true # enable horizontal scroll, problem on big screens because it does
              # not occupy 100% width. if false, than only data will scroll but
              # header will still be fixed
scrollY: "55vh" # 55% of the vertical height of the browser window. Usefull
                # since user can see footer navigation links
scrollCollapse: true # if there are not data (when filtering) than table body
                     # will collapse
autoWidth: false # disable auto calculation for column width so on big screens
                 # it occupy 100% width

Note that bootstrap tooltip won’t work since it is inside overflow-y: scroll; element (or overflow: hidden) so you need to change where it is attached $.fn.tooltip.Constructor.DEFAULTS.container = 'body'. Only <select> element is natively shown over borders. You can use trick position:fixed

Without horizontal scrolling you can hide some columns by column priority and expand or you can show details in modal

  • you can use fixed columns so they always stays fixed, but all other columns can be scrolled
  • or you can use fixed header https://datatables.net/extensions/fixedheader/
  • user can select which columns to see colvis note: retired

Custom types

type detection is automati for numeric, date and string, so default sorting works fine. If you want to do custom searching than you need to use $.fn.dataTableExt.afnFiltering.push or newer $.fn.dataTable.ext.search Note that is used on global search. If we add manually, than we need to remove also.This codepen show how to add and remove filter

Tips

Rails datatables helpers

https://www.datatables.net/download/npm since it provides both amd and commonjs there is a error

Uncaught TypeError: Cannot set property '$' of undefined

https://datatables.net/forums/discussion/32542/datatables-and-webpack https://datatables.net/forums/discussion/comment/125532/#Comment_125532

For webpack you need to not invoke after require or disable amd

# config/webpack/environment.js
environment.config.set('amd', false)

https://datatables.net/manual/server-side

Ajax datatable rails

https://github.com/ajahongir/ajax-datatables-rails-v-0-4-0-how-to/blob/master/app/datatables/city_datatable.rb Play around in ~/rails/temp/rails_6.0.0/ branch ajax_datatables_rails. For this gem you need to define view_columns, by default orderable and searchable are true, and cond is :like (:start_with, :end_with, :string_in,… )

# app/datatables.rb
class UserDatatable < AjaxDatatablesRails::ActiveRecord
  def view_columns
    @view_columns ||= {
      name: { source: "User.name", cond: :like },
      body: { source: 'Company.body', formatter: ->(o) { o.upcase }},
      comments: { source: 'Comment.body', cond: custom_filter },
    }
  end

  def get_raw_records
    User.all
  end

  def custom_filter
    ->(column, formated) {
      r = column.table[column.field].eq(column.search.value.to_i + 1)
      e = ::Arel::Nodes::SqlLiteral.new(column.field.to_s).matches("#{ column.search.value }%")
      e
    }
  end

  def data
    records.map do |record|
      {
        name: record.name,
        body: record.body,
        comments: record.comments.map(&:body).join,
        DT_RowId: record.id,
      }
    end
  end
end

Pros: easy to understand and it is using Arel

Cons: missing server side rendering, it only produce JSON. I need search by one column or another column (not all enabled columns). Difficult to understand how to filter by custom generated columns.

Gem uses following files:

  • config.rb: it include ActiveSupport::Configurable and set to @config
  • base.rb: it is parent class of AjaxDatatablesRails::ActiveRecord so we initialize with params, and create @datatable instance. It responds to .as_json (records_total_count->fetchrecords->UserDatatable#get_raw_records, records_filtered_count->ActiveRecord#filter_records-> ActiveRecord#build_conditions_for_datatable, UserDatatable#data-> set @records on retrieve_records(fetch, filter_records, sort_records, paginate_records).
  • datatable/datatable.rb: initialize with AjaxDatatablesRails::ActiveRecord as @datatable. Use columns to create Datatable::Column from params
  • datatable/column.rb: provides casted_column that creates Arel::Nodes::NamedFunction.new ‘CAST’, [User[:name].as(‘VARCHAR’)]
  • orm/active_record.rb: build_conditions_for_datatable creates arel criteria for each column and reduce using :or, and for each search_for (split by space) and join using :and. Areal criteria is in datatable/column/search.rb#search_query either regex_search or casted_column.matches '%'

Effective datatables

https://github.com/code-and-effect/effective_datatables play around on /home/orlovic/rails/temp/rails_5.2.3 branch effective_datatables

Pros: show/hide columns, bulk actions (check boxes are not perserved between pages and filters) it is in ajax, and first page is reloaded, autodetect columns.

Cons is that I need to learn to much DSL, and it is too magic (for example it provides edit/show/delete buttons, for association it searches all it’s columns). Does not support Search all columns from one input field. Filters are not perfomed using GET params but in cookies (so I can not copy url and have same results). No test files, that’s wierd.