#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. ^_^