#340 DataTables

I have been using datatables since it first came out and rely on its features for all my statistical production sites. Even though I love watching all of the railscasts produced here, I don't find this one particularly rewarding. I find my own use of streamlined helpers to be enough to work with and extend any of the datatables features.

One noticeable trend I'm finding others do, which I will not, is to become too dependent on gems that aren't necessary. jQuery UI, datatables, and a host of other jQuery related feature code works great right out of the box. Some gem authors update their code and keep it up to date, and others fall off and forget their code after time. Becoming too dependent on gems for features that are clearly outlined and easily usable out of the box seems counter-productive to me.

These are just my thoughts.

Looks pretty nice. Have you tried using will_filter gem? It does similar things, and it also allows you to build powerful queries as well as save filters and share them across multiple users. You can build an entire admin tool in just 5 lines of code.

https://github.com/berk/will_filter

A note to the readers: If you try to run the sample app using the episode code from github, you will get an error when you click on the "product name" link. This is because the show view is referencing the category model but there is none in the current code base. So just delete the offending lines of code that references the @product.category.name field.

I made a gem to do this a while back -- cuts out most of the heavy lifting: https://github.com/jasondew/data_table

Why do you create a Class when you can create the json in the respond_to block? For example:

respond_to do |format|
  format.html
  format.json do
  render :json=>{
    "iTotalRecords"=>@products.count,
    "iTotalDisplayRecords"=>@products.count,
    "aaData"=>@users.as_json(
      :only=>[:name, :category, :released_on, :price]
    )
  }
  end
end

I am using the mongoid data table gem that has a one stop search functionality https://github.com/potatosalad/mongoid-data_table

Anyone have any tips for testing this thing? If you test the ProductsDatatable class on it's own (rather than testing the results of the products/index controller action) how can you instantiate a view_context on initialization? Or is it best to just rely on mocking/stubbing everything that would normally exist in the context of a normal request?

Looking back to the Presenters from Scratch screencast looks like it might be something like this?

class ProductDatatableTest < ActionView::TestCase
  test "something or other" do
    datatable = ProductDatatable.new(view)
    # but, how to set the params for the view?
  end
end

to these u cant live without bootstrap: http://datatables.net/blog/Twitter_Bootstrap_2

Hi Ryan

Can you cover other features of data table in rails cast? features like add, delete, edit.

One thing I noted when trying to implement datatables with server-side processing is that the SQL queries on search aren't as fuzzy as the default datatables search function. If you're going server-side, definitely check out other search solutions.

I'd like to use datatables but I'm using an api where not all columns are sortable and it has a weird way of passing the column argument, rather than column and direction i have to pass columnIsdesc=true or false or not pass it.

How would I go about that?

If you use Kaminari instead of will_paginate, just replace total_entries by total_count and per_page(per_page) by per(per_page) in the class ProductsDatatable

Hi Ryan

Please please do an episode on CRUD in datatable and export features of it.

Just a tip -- don't put it in your assets group in the gemfile or it won't work on production when deploying to Heroku.

Great screencast! However how can I pass a data set to json? Say in my controller index I have @products = Product.where(:status => "active") to initialize my view. In my view I've got a select tag to filter active, pending and inactive products. How can I pass the set of pending or inactive products to json?

Secondly, how do I properly link to an edit path in ProductsDatatable? It gives me a no method error if I do link_to("edit", edit_product_path(product)

Thanks!

data-source for index action is
data-source="<%= products_url(format: "json")

how to code same for something like show action(member action) ??

Does anyone have a working example with Bootstrap and Ajax on Github that I could look at? I think I'm close to getting this to work, but the datatable isn't fetching the json data for some reason. I suspect it's a problem with my javascript, but I can't find the problem.

I initialize my datatables in assets/javascripts/table.js like this:

$('.datatable').dataTable({
  "sDom": "<'row'<'span3'l><'span3'f>r>t<'row'<'span3'i><'span3'p>>",
  "sPaginationType": "bootstrap",
  "bJQueryUI": true,
  "bProcessing": true,
  "bServerSide": true,
  "sAjaxSource": $('#ppm_list').data('source')
});

and in my view I have this:

<table id="ppm_list" class="datatable table table-striped table-bordered table-condensed" data-source="<%= products_url(format: 'json') %>" style="table-layout: fixed" >

but the table is empty.

How can I add a delete button column using the json/ajax approach? Here's what I'm trying to replicate:

<%= link_to 'Delete', product, method: :delete, class: "btn btn-mini btn-danger", confirm: "You sure?", title: product.product_number %>

Nice example, has anyone tried this with associated tables?
i.e. product table has a category_id
e.g. so the search will result in the searching category.name or product.name...

To use multicolumn sorting, I did this:

  def sort_helper
    columns = %w[name category released_on price]
    sort = "#{sort_column} #{sort_direction}"
    (params[:iSortingCols].to_i-1).times do |i|
      sort << ", #{columns[params["iSortCol_#{i+1}"].to_i]} #{params["sSortDir_#{i+1}"] == "desc" ? "desc" : "asc"}"
    end
    sort
  end
  def fetch_products
      products = Product.order("#{sort_helper}")
      products = products.page(page).per_page(per_page)
      if params[:sSearch].present?
        products = products.where("name like :search or category like :search", search: "%#{params[:sSearch]}%")
      end
      products
    end

Getting error in view:Extract from log:
Highway Count at list: 229
Rendered highways/list.html.erb within layouts/application (107.9ms)
Completed 500 Internal Server Error in 164ms

ActionView::Template::Error (undefined method `highways_url' for #<#Class:0x98d7c4c:0x98d66a8>):
5:

6: <%= link_to("Add New Highway", {:action => 'new'}, :class => 'action new') %>
7:

8: ">

highways.js.coffee:
jQuery ->
$('#highways').dataTable
sPaginationType: "full_numbers"
bJQueryUI: true
bProcessing: true
bServerSide: true
sAjaxSource: $('#highways').data('source')

Rails:3.2.1.
What other information can I supply?

Thanks

Partially solved. Have included a specific route. No data in my table - will look further into that.

Thanks

In the ASCIIcast version, when you clear out the tbody section, you have two opening tbody tags. I think you want one opening and one closing. Thanks for the great examples!

Thanks for the great examples. I have a question. One table I wish to show has 3 links to associated tables, and I want to use those associated fields as args for sorting and searching. I cannot work out the syntax in the Ruby class; if it's possible.

When I look at the log I can see 4 queries for every row, the first for the main table and another 3 for each link. Seems very inefficient.

I have this code in the model:

class Neighbour < ActiveRecord::Base

belongs_to :locality, :class_name => "Locality"
belongs_to :neighbour, :class_name => "Locality"
belongs_to :highway, :class_name => "Highway"

default_scope joins(:locality, highway).order('localities.name')

end

In straight SQL I would achieve this with one query and let the database optimise the method. How would I do this to supply a view of data to the dataTable?

Thanks

This is just amazing...I found the dataTables plugin a while ago and was trying to figure out all out for a few hours. So I come to railscasts to see if Ryan had anything similar, and lo and behold...he's got an episode on the exact plugin I'm using. This has happened so many times it scary - I'm trying to figure out how to use something and Ryan has an episode on exactly what I'm doing. Railscasts is well worth every dime! Thanks for your awesome work, Ryan.

hi,

i can not use symbol '€', anyone can help me ?

I wasn't aware that

gem 'jquery-datatables-rails', github: 'rweng/jquery-datatables-rails'

was valid bundler syntax, I thought it was :git. Is there a difference between the two? I can't exactly google the issue due to white noise.

Great info, thank you.

It would have helped a novice like me to know that I needed to restart the rails server before "When we reload the page now our table looks quite different."

Also, I saw two places where products.js is mentioned without the .coffee

Has anyone successfully integrated individual column filtering (preferably with multiple columns) with a Rails back end?

Thanks a lot.

Hi there,

I am playing with data table following this guide.
But I feel the code in CoffeeScript was loaded before the html/haml code. So the jQuery select didn't find the .

Nothing happened after I load the page.
But when I type $('#provider-data-table').dataTable() on console, I can see the table. It is the same code in the CoffeeScript.

Could you please help me?

Thanks,
Shanbo

I have been messing around with this today but for some reason I still can't figure out where the following functions are defined and my rails server keeps telling me they are undefined:
Products.page(page).per_page(per_page) and products.total_entries.

Any help would be great.

how can I combine edit, show, destroy button to one column.

Thanks Ryan!! this was exceptionally useful. The fact that you took the time to figure out how to this gem run server side was just great.

It would be good to see a follow up casts using custom css formatting and other interesting tricks. (ie how to handle timeouts, ignoring searching on specific columns).

But what is really useful in the casts are the gems we never heard of used in ways we did not realize were possible. ie charting was a great example

Hi

I followed a railscast episode and got datatables up and running, beautifully along with the nice jquery-ui as in the front page.

The main issue I'm having though is that I suck at CoffeeScript/JS ~_~

I'm trying get the datatables to reload every 1 second but I don’t see any request coming into my webserver for refreshes and hence, no refreshes on the web page itself.

Here's my code:

app/assets/javascripts/comments.js.coffee

jQuery ->
  $('#comments_id').dataTable
    sPaginationType: "full_numbers"
    bJQueryUI: true
    bProcessing: true
  setInterval('$("#comments_id").dataTable().fnDraw()', 1000);

And here is the code that gets generated on the client side:

(function() {

  jQuery(function() {
    $('#comments_id').dataTable({
      sPaginationType: "full_numbers",
      bJQueryUI: true,
      bProcessing: true
    });
    return setInterval('$("#comments_id").dataTable().fnDraw()', 1000);
  });

}).call(this);

Help will be appreciated. ^_^

Hey this is Mina Koltaa

I've just started Railes recently and I have a question about the datatable
I need to list the following

Username email role registered_at

The problem lies in role part cause when you click on the change role button it pops in HTML radio buttons to make you change the role
with datatable i need to retrive this json and customize to html each time i respond to the client side now i have the JSON in the row cell

can anyone help ???!!

Hi,

I have a problem with this solution, with the search.
I quickly typing a few numbers into search field, then I get the error on the server-side:

[2013-01-28 13:59:30] ERROR Errno::ECONNRESET: Connection reset by peer
/home/lena/.rvm/rubies/ruby-1.9.2-p180/lib/ruby/1.9.1/webrick/httpserver.rb:56:in eof?'
/home/lena/.rvm/rubies/ruby-1.9.2-p180/lib/ruby/1.9.1/webrick/httpserver.rb:56:inrun'
/home/lena/.rvm/rubies/ruby-1.9.2-p180/lib/ruby/1.9.1/webrick/server.rb:183:in `block in start_thread'

Please help me...

Hello
I have a dataTable with nested models. I use server side processing and all works fine (search on multiple columns, sorting).

Now I need to add multiple row selection and send selected objects ids to my controller.

But I can't implement the multiple row selection. I followed the example on "http://datatables.net/release-datatables/examples/server_side/select_rows.html". I changed my users.coffee.js file to users.js to not have to mess with CoffeeScript.

Now when the page loads I have an error "reference error : aSelected is not defined" in the "fnRowCallBack" function, I see the datas in the table and the 'processing indicator'...

Any help on this would be very appreciated
Cheers

By the way, the example provided in dataTables.net uses jQuery.live function which is obsolete since version 1.7, I changed it to '.on'

usersdatatable.rb

def data
    users.map do |user|
      [
      link_to(user.name, user),
      h(user.email),
      link_to("delete", user, method: :delete, data: { confirm: 'Are you sure?' }),
      # check_box_tag('user[]', user.id)
      ]
    end
  end

• it don't working When i add check_box_tag. Please help me!

Hello

It appears that the JSON array returned is not what is expected by DataTables.

Let say for a model User where mapping returns user.id, user.last_name, user.first_name, user.email...

the returned JSON is

aaData: [[1, DOE, John, doe@example.com,…],[7, BAXTER, Bill, baxter@example.com],[15, BROWN, Bob, brown@example.com],...]

wich is a one dimension array, and the 2 dimensions arraay expected by DataTables (for the aaData part) should be for the same set of data

aaData: [{"id":1, "last_name":DOE, "first_name":John, "email":doe@example.com,…},{"id":7, "last_name":BAXTER, "first_name":Bill, "email":baxter@example.com],{"id":15, "last_name":BROWN, "first_name":Bob, "email":brown@example.com},...]

So if it displays well in your example it throw errors when filtering server-side with an error "JSON data from server could not be parsed. This is caused by a JSON formatting error"

Is there anybody who knows how to return the well formatted JSON ?
Any help would be appreciate

in Ryan's example, how can I pass a querystring parameter to the datatable class? so if the url is /logs it displays all logs but if it is /logs?product_id=1, it will only display logs for the product with id = 1

Hey I'm trying to get rid of striping in my table, and I'm not exactly sure how to do that? Any help would be appreciated!!

I'd like to pass a parameter to the datatable.rb... specifically, the @user = User.find(params[:id]). Side note, I'd also like to be able to use current_user in the new class. I haven't tried it, so I'm not sure if that will even work.

Thoughts?

I'm getting this weird errror: "NameError (uninitialized constant ReportsController::ReportsDatatable):
app/controllers/reports_controller.rb:20:in `block (2 levels) in index'"

The weird thing is sometimes when I load the table I get this error, but other times I do not. Also everytime I try to go to the next page this error occurs.

Any ideas?

To anyone trying to access instance variables from within the datatable controller, pass the instance variable to a method within a helper. For example:

# application_helper.rb
module ApplicationHelper
  def user_name
    @user.name
  end
end

# datatables/my_datatable.rb
class MyDatatable
   ...
   def hello_method
     "Hello, #{@view.user_name}"
   end
end

Any progress in how to sort column data from associated tables using server side processing?

class Workshop < ActiveRecord::Base
  belongs_to :location, :class_name => 'Location'
end

class Location < ActiveRecord::Base
  has_many :workshops
  attr_accessible :name,
end

Records are associated by id ( workshop.id = location_id)

On the Workshop datatable class, the information displayed is the location_name

class WorkshopsDatatable

 def data
    workshops.map do |workshop|
      {
        id:           workshop.id,
        location:     workshop.location_name,
      }
    end
end

I would like to be able to sort the location column by location_name and not by location_id.

My datatable is always "Processing..." and never show the data, any idea?

I have really gotten a ton out of Ryan's work. It's helped me immensely learning Rails and I still know nothing.

I put together a small instructional guide for those who want to use dataTables with Twitter Bootstrap. My environment is Rails 4, RC1.

Anyway, hope this helps. As it's long, I'll link it, but I can post it here if that's more appropriate.

http://www.hamcois.com/articles/3

The following puts each record into a dataTable cell instead of a row:

def data
    User.current.employee.workgroups.map do |workgroup|
      workgroup.workorders.wonotclosed.map do |workorder|
        [
        link_to(workorder.wonum, workorder),
        h(workorder.parent.try(:wonum)),
        h(workorder.description),
        h(workorder.worequest.try(:subject)),
        h(workorder.client.try(:client_name)),
        h(workorder.woasset.try(:assetnum)),
        h(workorder.type.try(:typecode)),
        h(workorder.priority),
        h(workorder.super.try(:employee_full_name)),
        h(workorder.workgroup.try(:group_name)),
        h(workorder.employee.try(:employee_full_name)),
        h(workorder.wostatus.try(:statuscode))
        ]
      end
    end
  end

Any way to fix?

No way to let it work!
I really followed step by step the ASCIIcast (first part: client side) working on the Ryan's store-before app from github (master).

I used gem "jquery-datatables-rails", "~> 1.11.2" with ruby 1.9.3 and rails (3.2.1, 3.2.3) on windows.

I checked links to files in the html head page source, every link point to its file.

Now I don't know where to look for debugging again!
Maybe there is a trick I missed?

Great cast.
I don't really like to override the .json mime type to always respond with the datatable-formatted JSON
I suggest aliasing the "text/json" mime type to a new type called something like "datatable"
This leaves .json still functioning as it would

Mime::Type.register_alias "text/json", :datatable

respond_to do |format|
  format.html
  format.json       { render json: @products }
  format.datatable  { render json: ProductsDatatable.new(view_context) }
end

data-source="<%= products_url(format: "datatable") %>"

In case it might help someone, in Rails 4.0.0/Ruby 2.0, I was getting this error:
private method 'h' called for #<#Class:0x00000003dcd130:0x000000037ef538>

Instead of delegating h to the view_context, I called it directly like this:

def data
  products.map do |product|
    [
      link_to(product.name, product),
      ERB::Util.h(product.category),
      ERB::Util.h(product.released_on.strftime("%B %e, %Y")),
      number_to_currency(product.price)
    ]
  end

In case it might help someone, I switch from creating a class in app/datatables to use Jbuilder.

This gave me the huge advantage to use cache. I tried delegating the cache method in my class but with no success...

Here is an example:

def index
  @products = Products.all.page(1).per(50)
end

json.sEcho params[:sEcho].to_i
json.iTotalRecords @products.total_count
json.iTotalDisplayRecords @products.total_count
json.set! :aaData do
  json.array! @products, partial: 'show', as: :product
end

json.cache! [product] do
  json.id product.id
  json.name product.name
end

hope this helps (Im using kaminari)

After bundle installing the gems, and adding

jQuery ->
  $('task_sessions_table').dataTable()

in the appropriate coffee file (app/assets/javascripts/), just like Ryan did, I try to reload the page and get the error.

couldn't find file 'dataTables/jquery.dataTables'
(in /Users/Jonathan/code/gp-taskmanagement/app/assets/stylesheets/application.css.scss:7)

Please help!

I followed the tutorial and I'm able to show data, but I have a couple of issues probably about app/assets/javascripts/products.js.coffee file:

jQuery -> 
  $('#products').dataTable( {
    "sDom": "<'row'<'span3'l><'span9'f>r>t<'row'<'span8'i><'span12'p>>"
    "sPaginationType": "bootstrap",
    "bProcessing": true,
    "bServerSide": true 
    "sAjaxSource": $('#products').data('source')
  } );

1) I don't understand the "sAjaxSource": $('#products').data('source') param. If I try to remove this row nothing changes. Why ?

2) bootstrap layout doesn't work. Below my code:

Table def

<table id="products" class="datatable table table-striped table-bordered" data-source="<%= products_url(format: "json") %>">```

``` app/assets/javascripts/application.js
//= require jquery
//= require jquery.turbolinks
//= require jquery_ujs
//= require bootstrap
//= require twitter/bootstrap
//= require dataTables/jquery.dataTables
//= require dataTables/jquery.dataTables.bootstrap3
//= require products
//= require turbolinks

 *= require_self
 *= require jquery.ui.core
 *= require jquery.ui.theme
 *= require dataTables/jquery.dataTables.bootstrap3
 *= require dataTables/extras/TableTools
 *= require dataTables/extras/TableTools_JUI
 *= require_tree .

Any idea is very appreciated. Thanks

I've implemented this, but the search seems to be case sensitive. I've tried to use the bCaseInsensitive = true in the js, but that doesn't change it. Not sure what in the datatables.rb is making it case sensitive. Appears to also be that datatables is case insensitive by default, so i'm guessing this code is overriding something.

thanks in advance.

jake

Hi all

I have a products table with server side response.

But I am getting issue with sorting for comments_count. Because comments_count is not a field in products table .

I am getting PGSql error for undefined column comments_count.

But comments_count is the total number of counts for each product record.

def sort_column
columns = %w[name created_at user_id id category_name comments_count]
columns[params[:iSortCol_0].to_i]
end

How can I solve to order by comments_count ?

Have anybody tried to test this with Capybara?

I keep getting JSON invalid when testing it (using AJAX approach). Works fine in development though....

I know that this is a old post and no one has posted here in 9 month but I having problem with searching numbers any one has a example. thanks

HI folks

do you know if there is a datatable which allows you to see child rows (collapsible) and which has CRUD links - like with scaffolding? I could really use one such table like that right now ;)

chrs

Ben