Saturday, 28 March 2015

Datatables Date Range Search

Following on from my last post about implementing Jump to Page functionality in a Datatable I've recently been asked to implement a Date Range Filter within another table and I used something quite similar:

,"fnInitComplete": function(){
    var date_range = $("#date-range").addClass("row");
    var from_fate = $("<div></div>",{
        "class": "form-group col-xs-4"
    }).appendTo(date_range);
    var to_fate = $("<div></div>",{
        "class": "form-group col-xs-4"
    }).appendTo(date_range);
    var null_box = $("<div></div>",{
        "class": "checkbox col-xs-2"
    }).appendTo(date_range);
    null_box.append($("<label></label>",{
        "text": "Empty?"
    }).prepend($("<input/>",{
        "type": "checkbox",
        "id": "null_box"
    })));
    date_range.append($("<button></button>",{
        "id": "filter_date",
        "class": "btn btn-primary col-xs-2",
        "text": "Filter Dates"
    }));
    from_fate.append($("<label></label>",{
        "class": "sr-only",
        "for": "from_date",
        "text": "From Date"
    })).append($("<input>",{
        "type": "date",
        "class": "form-control",
        "id": "from_date",
        "name": "from_date",
        "value": moment(earliest, "X").format("YYYY-MM-DD")
    }));
    to_fate.append($("<label></label>",{
        "class": "sr-only",
        "for": "to_date",
        "text": "To Date"
    })).append($("<input>",{
        "type": "date",
        "class": "form-control",
        "id": "to_date",
        "name": "to_date",
        "value": moment(latest, "X").format("YYYY-MM-DD")
    }));
}

Within the Twig template I set the earliest and latest variables after doing a couple of simple Medoo queries:

$latest = $db->get(
    "contact_donation",
    "accepted",
    array(
        "ORDER" => "accepted DESC",
        "LIMIT" => 1
    )
);
$earliest = $db->get(
    "contact_donation",
    "accepted",
    array(
        "accepted[!]" => null,
        "ORDER" => "accepted ASC",
        "LIMIT" => 1
    )
);

I also needed to listen to changes to the from_date and to_date inputs and alter those variables as a result:

$(document).on("change", "#from_date", function(){
    if(moment($("#from_date").val()).isValid()){
        earliest = parseInt(moment($("#from_date").val()).format("X"), 10);
    }
});
$(document).on("change", "#to_date", function(){
    if(moment($("#to_date").val()).isValid()){
        latest = parseInt(moment($("#from_date").val()).format("X"), 10);
    }
});

And listen for the filter_date click event:

$(document).on("click", "#filter_date", function(){
    table.draw();
});

I clocked that not all the records would have a Date and wanted those to be available so I added the checkbox to include those dates despite the Dates searched for and so this is the search code I added:

$.fn.dataTable.ext.search.push(
  function(settings, data, dataIndex){
    var show_null = $("#null_box").prop("checked");
    var min = earliest;
    var max = latest;
    var donation_date = (moment(data[2], "DD/MM/YYYY").isValid()) 
      ? parseInt(moment(data[2], "DD/MM/YYYY").format("X"), 10) 
      : "null";
    return (show_null && donation_date === "null") || 
      (min <= donation_date && donation_date <= max);
  }
);

As you can probably tell I've taken all of the pain out of JavaScript dates by using momentjs. I was getting all sorts of result issues until I clocked that when I rendered the table I was rendering dates using the DD/MM/YYYY format rather than the default YYYY-MM-DD.