Rails datatables select filter

Question

I have an ajax datatable for my SKUs. For this I am using the ajax-datatables-rails gem. Searcing and sorting works perfectly, but now I'm trying to add a filtering function to my table and it doesn't seem to do anything. I used this example for the filter function: https://datatables.net/examples/api/multi_filter_select.html. In the example, select boxes are drawn in the footer, but for me the footer is empty. Like the code doesn't run at all. I also don't get any errors.

I initialize my datatable in my coffeescrip file (assets/javascripts/vendor_skus.js.coffee) so I had to translate it to coffeescript. I'm not experienced with coffeescript or using ajax with rails so I'm kind of lost as to what is going wrong.

How I solved my problem:

The standard select boxes were problematic for my situation, as I am using AJAX for my table and the select boxes seemed to only work properly on client side tables. Instead of going with the standard select boxes, I decided to make my own custom filters. These are regular select boxes like so:

<%= select_tag "store-id", options_from_collection_for_select(@stores, "id", "name"), include_blank: true, class:"store-id form-control" %>  
<%= select_tag "status", options_for_select([ "Open", "On Hold", "Cancelled", "Closed", "Error" ]), include_blank: true, class:"form-control", multiple:true %>

This is my coffeescript to make jQuery submit the parameters to the server and reload the table onchange:

$ ->
  $('#orders-table').DataTable
    processing: true
    serverSide: true
    retrieve: true
    pageLength: 50
    title: 'orders'
    lengthMenu: [[10, 25, 50, -1], [10, 25, 50, "All"]]
    ajax: data: (d) ->
      d.store_id = $('#store-id').val();
      d.status = $('#status').val();
      return

$ ->
  $('#store-id').on 'change', ->
    $('#orders-table').DataTable().ajax.reload()
    return

$ ->
  $('#status').on 'change', ->
    $('#orders-table').DataTable().ajax.reload()
    return   

In your controller, make sure to pass the parameters along to Datatables like so:

respond_to do |format|
  format.html
  format.json { render json: OrderDatatable.new(view_context, { store_id: params[:store_id], status: params[:status] }) }
end

And then in your Datatable file, use the parameters to filter your results. In this case I am using a multi select for status, so when the blank value is selected, params[:status].present? results in true. That's why I added a check to see if the first item is an empty string.

  def get_raw_records
    # insert query here
    query = Order.all
    query = query.status(params[:status]) if params[:status].present? && (params[:status].count == 1 && params[:status][0] == "") == false 
    query = query.store(params[:store_id]) if params[:store_id].present?
    query.joins(:store)
  end

Answer 1

I ran into the same issue when implementing this. I found out that the issue was with this line:

column.search((if val then '^' + val + '$' else ''), true, false).draw()

where coffee script did not like the following bit:

, true, false

After removing it like so:

column.search(if val then '^' + val + '$' else '').draw()

everything worked fine. The caveat to this is, I am not a javascript/coffeescript guy, so what negative impact the result does is beyond me. But like you I am currently battling to get all results to appear in the selectable drop down filter. It only shows any unique values from the current page of data - which is not helpful.

FYI, to get pagination working on this, go to your datatable.rb file and uncomment the correct line toward the top that refers to the pagination you're using. I am using "will_paginate" for bootstrap, so mine looked like this:

  include AjaxDatatablesRails::Extensions::WillPaginate

Hope that helps. By chance, did you find a way to show all results in the select filter?

Answer 2

My working code for an ajax datatable filter using the ajax-datatables-rails gem.

in the datatable view I created a table above the datatable to input the range variables, then add some javascript to reload the datatable on change:

<table>
        <tbody><tr>
            <td>Minimum CWD:</td>
            <td><input type="text" id="minCWD" name="minCWD"></td>
        </tr>
        <tr>
            <td>Maximum CWD:</td>
            <td><input type="text" id="maxCWD" name="maxCWD"></td>
        </tr>
    </tbody></table>

<script>
$(document).ready(function () {             
            // other options
            var table = $('#example').DataTable()
                    
            $("#minCWD").change(function () {
              table.ajax.reload();
            });
            $("#maxCWD").change(function() {
              table.ajax.reload();
            });
        });
</script>

then to add the filter variables to the ajax call (in the coffee file):

ajax: {
      url: $('#example').data('source'),
      beforeSend: (xhr) => xhr.setRequestHeader('Content-Type', 'application/json'),
      data: (d) ->
        $.extend {}, d, 'minCWD': $('#minCWD').val(),
        $.extend {}, d, 'maxCWD': $('#maxCWD').val()
    }
// note: the beforeSend may not be required

then add a filter in the model_datatable.rb:

def get_raw_records
    #YOUR TYPICAL SELECTION...note: I'm using AREL and joining schools with pstats
    #now filter by your max min variables
    if params['minCWD'].present?
      schools = schools.where(pstats[:cwd_percent].gteq(params['minCWD']))
    end
    if params['maxCWD'].present?
      schools = schools.where(pstats[:cwd_percent].lteq(params['maxCWD']))
    end
    return schools
  end

My controller looks like this:

respond_to do |format|
      format.html
      format.json { render json: ExampleDatatable.new(params, view_context: view_context) }
end

working example here: https://schoolsparrow.com/arizona/schools