Server-side compatibility with the last DataTables.js version (1.10.x)

[GoogleCodeExporter]

Hello,

I corrected the plugin. Just simple modifications that permits it compatibility 
with the last version of DataTables from line 790 to 820 :

if (oTable.api().settings().context[0].bAjaxDataGet) {

                var fnServerDataOriginal = oTable.api().settings().ajax;

                oTable.api().settings().ajax = function (data, callback, settings) { 

                    for (j = 0; j < aiCustomSearch_Indexes.length; j++) {
                        var index = aiCustomSearch_Indexes[j];


                        for (k = 0; k < data.length; k++) {
                            if (data[k][searchable] === true)
                                data[k][search][value] = afnSearch_[j]();
                        }
                    }
                    // We don't need because we don't trust the user input ;)
                    //aoData.push({ "name": "sRangeSeparator", "value": properties.sRangeSeparator });

                    if (fnServerDataOriginal != null) {
                        try {
                            fnServerDataOriginal(data, callback, settings, oTable.fnSettings()); //TODO: See Issue 18
                        } catch (ex) {
                            fnServerDataOriginal(data, callback, settings);
                        }
                    }
                    else {
                        $.getJSON(sSource, aoData, function (json) {
                            fnCallback(json)
                        });
                    }
                };

            }

Original issue reported on code.google.com by [email protected] on 2 May 2014 at 2:50

Attachments:

• jquery.dataTables.columnFilter.js

[GoogleCodeExporter]

Many thanks for this. This got my server-side filtering working, but for ranges 
no search values are being sent in the request. Since I am new to this software 
I don't know where this issue was introduced (original code or this patch). I 
am just not getting any parameter values through for range search values so I 
can't do the filtering on the server side for those fields.

Original comment by [email protected] on 9 May 2014 at 9:28

[GoogleCodeExporter]

Range filtering didn't work for me either, with this solution -- I THINK it's 
because I'm using an object for the value of my "ajax" datatable setting, 
instead of a function. Eventually I replaced all the code above with:

if (oTable.api().settings().context[0].bAjaxDataGet) {

                $(oTable[0]).on('preXhr.dt', function ( e, settings, data ) {
                    for (j = 0; j < aiCustomSearch_Indexes.length; j++) {
                        var index = aiCustomSearch_Indexes[j];


                        if ($.fn.dataTable.ext.legacy.ajax) {
                            for (var key in data) {
                                if (key == "sSearch_" + index)
                                    data[key] = afnSearch_[j]();
                            }
                        } else {
                            for (k = 0; k < data.length; k++) {
                                if (data[k][searchable] === true)
                                    data[k][search][value] = afnSearch_[j]();
                            }
                        }
                    }
                    console.log(data);
                } );

            }

which seemed to work.

Original comment by [email protected] on 20 May 2014 at 11:25

[GoogleCodeExporter]

Oops, I left a trailing console.log line that can be removed.

Original comment by [email protected] on 20 May 2014 at 11:25

[GoogleCodeExporter]

Silvertree-

Thank you very much for your patch. I was tearing my hair out over this one. Do 
you (or anyone else) know when this might be integrated into the release 
version?

-Josh

Original comment by [email protected] on 20 Aug 2014 at 1:48

[GoogleCodeExporter]

Hi, 
in your experience, this the only change to do to use 
jquery-datatables-column-filter
with DataTables 1.10 ?
As I see the official trunk is stopped at March so the last version doesn't 
work with DataTables 1.0

Original comment by [email protected] on 2 Oct 2014 at 2:29

[GoogleCodeExporter]

Yes.

But now, I andonned this jQuery plugin to be fully compatible with the last 
version. I fully generate individual column filters with my server language 
(PHP). I write a librairie available.
DOc : http://www.robin-d.fr/DataTablesPHP/
Source : https://github.com/RobinDev/DataTablesPHP

I kept the same logic except I don't yet add the capability to generate select 
option from the data. It will come soon.

Original comment by [email protected] on 16 Nov 2014 at 10:53

[GoogleCodeExporter]

hi  still i have the problem..  any solutions for the bug fix.  the ajax column 
filtering is not working.  

Original comment by [email protected] on 1 Dec 2014 at 9:03

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Hi,
thanks a lot for your code snippets. They made my day much easier :)

What wasn't working for me is server side processing with date ranges.
So I extended the datatables ssp.class.php to accept customer specific 
parameters sent by the client. Maybe it will be helpful for someone else.

In "static function filter()" I added these lines:

        // Individual column filtering
        for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
            $requestColumn = $request['columns'][$i];
            $columnIdx = array_search( $requestColumn['data'], $dtColumns );
            $column = $columns[ $columnIdx ];

            $str = $requestColumn['search']['value'];

            if ( $requestColumn['searchable'] == 'true' &&
             $str != '' ) {
                $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
                $columnSearch[] = "`".$column['db']."` LIKE ".$binding;
            }
        }

+// Individual column filtering for date ranges, first add start date
+       foreach ( $request['startDate'] as $columnId => $val ) {
+           if ( $val != '' ) {
+               $columnIdx = array_search( $columnId, $dtColumns );
+               $column = $columns[ $columnIdx ];
+               $binding = self::bind( $bindings, $val, PDO::PARAM_STR );
+               $columnSearch[] = "`".$column['db']."` >= ".$binding;
+           }
+       }
+// Now add end date to search query
+       foreach ( $request['endDate'] as $columnId => $val ) {
+           if ( $val != '' ) {
+               $columnIdx = array_search( $columnId, $dtColumns );
+               $column = $columns[ $columnIdx ];
+               $binding = self::bind( $bindings, $val, PDO::PARAM_STR );
+               $columnSearch[] = "`".$column['db']."` <= ".$binding;
+           }
+       }


Then I added the customer specific values of date fields in my datatables 
client side code like this:

"ajax": {
                "url": "server/side/script/to/call.php",
                "data": function (d) {
                    var startDateArray = {};
                    var endDateArray = {};
                    startDateArray['1'] = $('#<MY_TABLE_ID>_range_from_1').val();
                    endDateArray['1'] = $('#<MY_TABLE_ID>_range_to_1').val();
                    d.startDate = startDateArray;
                    d.endDate = endDateArray;
                }
            },

Now date range searching on server side works as well as searching only for a 
start or end date.
This should be similar to simple range searches (no date, only numbers).
Maybe code is optimiseable. Feel free to correct me ;)

Of course the array numbers in client side code have to match the column index 
in your table and date field IDs should also be set individually :)

Original comment by [email protected] on 31 Dec 2014 at 12:01

[GoogleCodeExporter]

Thank You very much for this script 

Original comment by [email protected] on 23 Mar 2015 at 9:09