Custom column filtering on the server side of DataTables

Question

I was hoping someone could help me. I went crazy with that.

I have a situation where I load the DataTables grid (a wonderful thing by the way!), And everything is fine. Then I go looking, and I have problems. The data that the grid fills comes from two different database tables (this is good), but when someone searches, I have no way of knowing where to go and get the data. I need to know what criteria a search refers to (i.e. Name or contact). I see that when a search is called from the server using the default search box, there are variables like "sSearch_0" that are not set, how to set them?

Here is the table initialization code:

oTable = $('#example').dataTable({ "bJQueryUI": true, "bFilter": true, "sPaginationType": "full_numbers", "bPaginate " : true, "bServerSide" : true, "sAjaxSource" : "php/tabledata.php", "aoColumnDefs": [ { "bSortable": false, "aTargets": [ 0,6,8 ] }, { "sClass": "tdCenter", "aTargets": [ 0,1,2,3,4,5,6,7,8 ] } ], "fnServerData": function ( sSource, aoData, fnCallback ) { aoData.push( { "name": "userid", "value": userid } ); $.getJSON( sSource, aoData, function (json) { fnCallback(json) }); } 

});

I examined the options for adding data to "fnServerData" and actually used it for the first call to the initialization server, but I don’t know how to use this for the subsequent call to the server. I tried using "fnFilter", but I can’t see what server call is doing for more data. At the moment, I see no other way to make a server call, except for the default search box, and I see how to find out which column the search is in.

Can someone help me here and point me in the right direction?

Answer 1

If you are receiving data from the server for the DataTables plugin, you should set bServerSide to true, set sAjaxSource to the appropriate URL, and ideally configure fnServerData if you need to make any callbacks.

If you use server-side processing, all sorting, filtering and paging should be handled by you on the server. If you configure DataTables correctly, it will request data from the server at any time when there is a swap, filtering, or sorting event.

DataTables APIs Documentation

PHP server side processing example

Answer 2

In the interest of all who will refer to this question, this is what I have implemented.

Client side (JavaScript)

Run fnFilter after pressing Enter.

 $(tableId + " thead input").keypress( function () { if (event.which == 13) { event.preventDefault(); oTable.fnFilter( this.value, $("thead input").index(this) ); } } ); 

Server Side (Ruby)

Find the sSearch_ (int) hash code and extract the column index from the key. Get the column names from the array and build the search string.

 def column_search search_string = [] params.keys.map {|x| x if params[x].present? and x.include? "sSearch_"}.compact.each do |search| index = search.split("_").last.to_i search_string << "#{columns[index]} ilike '%#{params[search]}%'" end search_string.join(' and ') end