Vladimir Evdokimov

My portfolio and blog, mostly about Ruby on Rails

Filtering jQuery DataTable using multiselect

Hi. Today I want to share my experience on how to add custom filter to jquery datatable.

When you initialize a DataTable

products.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
  <table id="products" data-source="/admin/products.json">
    <thead>
      <tr>
        <th></th>
        <th>Name</th>
        <th>Categories</th>
        <th>Price &euro;</th>
        <th>Details</th>
        <th>Edit</th>
        <th>Delete</th>
      </tr>
    </thead>
    <tbody>
      <tr class="odd">...</tr>
      <tr class="even">...</tr>
      ... etc.
products.js.coffee
1
2
3
4
5
6
7
$ ->
  $("#products").datatable
    sPaginationType: "full_numbers"
    iDisplayLength: 50
    bProcessing: true
    bServerSide: true
    sAjaxSource: $("#products").data('source')

If you use JS, convert CoffeeScript here

you get search field from the box:

Ok. Now we have products table and each product has many categories.

Yeah, we could adjust logic in back-end and filter results while user is typing in standard search box. But I’d better use a multiselect field for categories.

The solution is to implement fnServerData() method:

products.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$ ->
  $("#products").datatable
    sPaginationType: "full_numbers"
    iDisplayLength: 50
    bProcessing: true
    bServerSide: true
    sAjaxSource: $("#products").data('source')
    fnServerData: ( sSource, aoData, fnCallback ) ->
      # categories filter
      if $("#categories").val()
        aoData.push
          name: "sCategories"
          value: $("#categories").val().join(",")
      # end of categories filter
      $.getJSON sSource, aoData, (json) ->
        fnCallback json

Then process request in rails. I’ve set up datatables as explained in #340 railscasts

products_datatable.rb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#...
  def fetch_products
    products = Product.includes(:categories).page(page).per_page(per_page)
    if params[:sSearch].present?
      products = products.where("products.name like :search", search: "%#{params[:sSearch]}%")
    end
    if params[:sCategories].present?
      products = products.where("categories.id" => categories_extract).order("categories.lft ASC")
    end
    products.order("#{sort_column} #{sort_direction}")
  end

  # get self and all subcategories ids to return entire branch
  def categories_extract
    params[:sCategories].split(",").map do |c|
      Category.find(c).self_and_descendants.map { |cc| cc.id }.flatten
    end
  end
#...

I used awesome_nested_set gem to organize categories as nested tree.

Let’s take a look on result:

To make my multiselect that awesome, I used jquery chosen plugin.