Datatables
Contents |
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 echorecordsTotal
total number before filteringrecordsFiltered
total number after filteringdata
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 propertycolumnDefs: [ { targets: '_all', visible: false } ]
Columns can be selected column-selector by:
- its index
- jQuery selector
- columns.name (names should be defined).
Export
- 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 todata-search
- when you are using
<a href="some-text">
thatsome-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 examplemy name
is matched withmy 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
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
- multiselect with ctrl or shift
- drag and drop reorder
-
when you have a lot of columns (x-axis scrollbar is shown) than you can use fixedColumns which are always shown
-
another nice bootstrap-table
- similar tool for react https://github.com/gregnb/mui-datatables and it supports server side also https://github.com/gregnb/mui-datatables/blob/master/examples/serverside-pagination/index.js
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 withparams
, 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
. Usecolumns
to createDatatable::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 eitherregex_search
orcasted_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.