Custom Filtering (date range) with SSP Class

TannSan

Hi, I'm pretty new to datatables, had the luck of finding and implementing it on my site the day before you released 1.10 :)

So now I've updated everything to use the new version but there is a feature I'm trying to add which isn't working. That feature is date range filtering. I followed the example from your site (http://datatables.net/examples/plug-ins/range_filtering.html) but it just seems to ignore the custom filter. I've spent ages battling with it, trying to work out where things are going wrong and then it hit me that it could be the way data is being sent/not sent to the database. I'm fetching the data out of a MySQL database using the ssp.class.php file you provided in the 1.10 "examples/server_side/scripts" directory.

My simple test to check if it is working is simply to filter out everything:

$.fn.dataTable.ext.search.push( function( settings, data, dataIndex ) { return false; } );

So my questions are:

1) Are custom search filters i.e. data range filters supposed to work with the default ssp MySQL method?

2) If not then could you suggest where I should be looking to add this functionality myself? My guess is that I might have to tinker in _fnFilterComplete (jquery.dataTables.js) or the static filter function in ssp.class.php.

TannSan

It was pretty clear that the custom filtering doesn't work with SQL supplied data, at least for me I couldn't get it working. I then thought about tweaking it so that for all other filtering it would use the database filter system and then for date ranges I could just do a client-side hack but that didn't really work and I got worried when I considered how it would run with tens of thousands of records further down the line. I then moved onto trying to do it with a RegEx search but I quickly realised that pretty much everybody on the planet says doing a date range filter with RegEx is a very very bad idea!

In the end I decided to write my own cheaters system. Each time they type into one of the date input text fields it fires a java call to do a "column.search" but I manually craft the search term. I prefix the search with the keyword "Date:". I then tack the values from both the start and end text fields together so a search might actually look like this:

"Date:2014-04-01 02:15:35<>2014-05-02 18:21:56"

Then in the ssp.class.php file I modified the column search code inside the filter function. That then looks for the "Date:" prefix and then performs my date range search. In my test version I have enabled the Any+Time date/time picker and will prevent direct typing into the input fields. I know this won't stop people from sneakily putting their own data in, it's just for ease of not worrying about the date formatting people might type. This way I know if the data is in the wrong format then they are doing something they shouldn't.

Above my DataTable instance I have several filter boxes. Most of these are just plain input text fields. For my dates I have 2 input text fields, a start date and an end date:

<input id="col1_filter_start" type="text" name="col1_filter_start"></input>
<input id="col1_filter_end" type="text" name="col1_filter_end"></input>

$(document).ready(function() {

   // My DataTable setup is more complicated than this, I just put it there to put the rest of the code below in context.
   var oTable = $('#weapons').DataTable();

   /* So each time a keyup event happens in either date range text field it makes a custom search term up.
    * My dates look like "2014-05-02 18:21:56" so the custom search term would look like:
    * "2014-04-01 02:15:35<>2014-05-02 18:21:56"
   $("#col1_filter_start").on('keyup', {id:i}, function(event) { oTable.column(0).search("Date:" + $("#col1_filter_start").val() + "<>" + $("#col1_filter_end").val()).draw(); });
   $("#col1_filter_end").on('keyup', {id:i}, function(event) { oTable.column(0).search("Date:" + $("#col1_filter_start").val() + "<>" + $("#col1_filter_end").val()).draw(); });

} );

Then the final phase was to mod the static filter function in ssp.class.php from (line 164 for me v1.10.0):

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

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

To:

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

if ( $requestColumn['searchable'] == 'true' && $str != '' )
   {
      // Date Range Search
      $pos = strpos($str, 'Date:');
      $is_date_special = false;
      if ($pos !== false && $pos === 0)
         {
            $is_date_special = true;

            $dates = explode('<>', substr($str, 5));
            $dates[0] = trim($dates[0]);
            $dates[1] = trim($dates[1]);

            if ($dates[0] != '' && $dates[1] == '')
               {
                  // Return all dates newer than start date
                  $binding = SSP::bind( $bindings, $dates[0], PDO::PARAM_STR );
                  $columnSearch[] = "`".$column['db']."` >= ".$binding;
               }
            else if ($dates[0] == '' && $dates[1] != '')
               {
                  // Return all dates prior to the end date
                  $binding = SSP::bind( $bindings, $dates[1], PDO::PARAM_STR );
                  $columnSearch[] = "`".$column['db']."` <= ".$binding;
               }
            else if ($dates[0] != '' && $dates[1] != '')
            {
                  // Return all dates within range
                  $binding = SSP::bind( $bindings, $dates[0], PDO::PARAM_STR );
                  $binding2 = SSP::bind( $bindings, $dates[1], PDO::PARAM_STR );
                  $columnSearch[] = "`".$column['db']."` BETWEEN ".$binding." AND ".$binding2;
            }
         }

      // Skip default "LIKE" clause if we used the special date range
      if (!$is_date_special)
         {
            $binding = SSP::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
            $columnSearch[] = "`".$column['db']."` LIKE ".$binding;
         }
   }

TannSan

I'm not sure why it's ignoring all the code indentation etc in my code posts, I'll update the post if anyone can tell me.

lyndonwil

I've been having an issue with this as well.. i've included the search. push function but i can't get the internal function to call.. is this a serverside issue ? if not, i will add a test link..

tannsan, i'll have a look through your code as well.. thanks

allan

Thanks for sharing your solution with us!

It was pretty clear that the custom filtering doesn't work with SQL supplied data, at least for me I couldn't get it working.

Custom filtered on the client-side doesn't work when you have server-side processing enabled, since you are server-side processing the data, not client-side :-). So yes, it needs to be done on the server-side.

I'm not sure why it's ignoring all the code indentation etc in my code posts

I broke that for a little while. Fixed now :-)

Allan

TannSan

ha yeah I noticed you super quick fixed the forum code indentation when I checked back for the reply today! Still doesn't indent when Previewing though ;)

I also noticed that the reply email is sending the links with the HTML links in plain text format:

<a href="/forums/profile/1/allan">allan</a> answered your question: <a href="">Custom Filtering (date range) with SSP Class</a>

Follow the link below to check it out:
http://datatables.net/forums/discussion/comment/60546#Comment_60546

Have a great day!

Also, when you do "Preview" on this comment box there is no button to return to editing it, just "Save Draft" and "Post Comment". Saving draft and tapping F5 to refresh gets around that for now.

Hope you don't mind me throwing these nega-vibes your way, I think you've done an amazing job with DataTables and the site.

Edit: hmmm maybe indenting does work with previewing, when I first published this comment the indenting was still broke even though the code in my other comment was indented but then when I refreshed the page all of it looked ok. Could just be some gremlins hiding in my cache.

tangerine

Also, when you do "Preview" on this comment box there is no button to return to editing it, just "Save Draft" and "Post Comment".

Thanks - I keep forgetting to mention that!

lyndonwil

TannSan, thanks for posting your code.

I've been having the same issues and I thought i'd come back and let you know how i went about it.

Rather than send the dates as part of the search string, i sent the date information via ajax using the data option.

"ajax": {

    "url":"tablepdo.php",
    "data": function ( d ) {
            d.additionalCols = additionalCols,
        d.additionalSearch =    { 
            '1': { 'col':'3', 'type':'date', 'min': $('#col1min').val(), 'max': $('#col1max').val() } ,
            '2': { 'col':'4', 'type':'date', 'min': $('#col2min').val(), 'max': $('#col2max').val() } ,
            '3': { 'col':'5', 'type':'date', 'min': $('#col3min').val(), 'max': $('#col3max').val() } ,
            '4': { 'col':'6', 'type':'date', 'min': $('#col4min').val(), 'max': $('#col4max').val() }
        }
    }
}

On this particular grid, i have 4 additional date range filters. i send the affected column, the type of data and the range values to the script

I then run a function which creates the HTML and adds the handlers. On the key up for each handler. i call the search

table.search('').draw();

I then change the script to loop through the ajax data and matching any columns

// 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 the column has a serach value and is searchable 
            if ( $requestColumn['searchable'] == 'true' && $str != '' ) {
                $binding = SSP::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
                $columnSearch[] = "`".$column['db']."` LIKE ".$binding;
            }
            
            // Check to see if the column is one of the additiona columns
            // Loop through array
            for ( $j=0, $l=$additionalCols ; $j<$l ; $j++ ) {
                $min = $request['additionalSearch'][$j+1]['min'];
                $max = $request['additionalSearch'][$j+1]['max'];
                $col = $request['additionalSearch'][$j+1]['col'];
                $type = $request['additionalSearch'][$j+1]['type'];
                
                // Check for matched columns
                if ($columnIdx==$col){
                    
                    // Check for type of column
                    // If date, format the min and max values correctly
                    if ($type=='date'){
                        if ($min != ''){
                            $min = date( 'Y-d-m H:i:s', strtotime($min));
                        }
                        if ($max != ''){
                            $max = date( 'Y-d-m H:i:s', strtotime($max));
                        }
                    }
                    
                    if ($min != '' && $max == '')
                    {
                        // Return all dates newer than start date
                        $binding = SSP::bind( $bindings, $min, PDO::PARAM_STR );
                        $columnSearch[] = "`".$column['db']."` >= ".$binding;
                    }
                    else if ($min == '' && $max != '')
                    {
                        // Return all dates prior to the end date
                        $binding = SSP::bind( $bindings, $max, PDO::PARAM_STR );
                        $columnSearch[] = "`".$column['db']."` <= ".$binding;
                    }
                    else if ($min != '' && $max != '')
                    {
                        // Return all dates within range
                        $binding = SSP::bind( $bindings, $min, PDO::PARAM_STR );
                        $binding2 = SSP::bind( $bindings, $max, PDO::PARAM_STR );
                        $columnSearch[] = "`".$column['db']."` BETWEEN ".$binding." AND ".$binding2;
                    }
                }
            }
        }

It's a different way of doing things but now it's set up, i can have any number of range filters set up and the script handles accordingly.

This code hasn't been properly tested yet but it gives a good idea

Lynd

lyndonwil

=