Sunday 29 March 2015

Datatables Custom Server Side Script

I seem to spend much of my weekends these days playing with DataTables!

I had a need to search filters Contacts using their Categories within a Category table. To put it simply: a Contact can belong to multiple Categories and each of those Categories has a General value and can have a Specific value. They can also be dated.

I display the Categories the Contact belongs to on a table within their detail view but there was a requirement to display all Contacts belonging to a General and/or Specific Category. After playing with the standard server-side script which you can get access to once you've bought the Editor (something which I recommend as this tool is just so brilliant! (Thanks for the retweet Allan!)) I realised I'd have to roll my own and I was filled with some trepidation. After reading the excellent documentation though I was slightly less concerned.

I pulled my sleeves up and wrote this:

<?php
    /**
     * Created by PhpStorm.
     * User: Dominic
     * Date: 28/01/2015
     * Time: 16:39
     * UPDATED Date: 29/03/2015
     * UPDATED Time: 09:56
     */
    require_once $_SERVER["DOCUMENT_ROOT"].'vendor/autoload.php';
    require_once $_SERVER["DOCUMENT_ROOT"].'php/variables.php';
    function IsNullOrEmptyString($question){
        return (!isset($question) || trim($question)==='');
    }
    $query = array();
    $and = array();
    foreach($_REQUEST["columns"] as $column){
        if(!IsNullOrEmptyString($column["search"]["value"])){
            $and[] = $column["search"]["value"];
        }
    }
    $response["recordsTotal"] = $db->count("contact_categories");
    if(count($and) !== 0){
        if(count($and) === 1 || $and[1] === "all"){
            $query["contact_categories.general"] = $and[0];
        }else{
            $query["AND"] = array(
                "contact_categories.general" => $and[0],
                "contact_categories.specific" => ($and[1] === "blank") ? "" : $and[1],
            );
        }
    }
    $response["recordsFiltered"] = $db->count(
        "contact_categories",
        array(
            "[>]contacts" => "contact_id"
        ),
        "*",
        $query
    );
    if(intval($_REQUEST["length"]) !== -1){
        $query["LIMIT"] = array($_REQUEST["start"], $_REQUEST["length"]);
    }
    $query["ORDER"] = array();
    foreach($_REQUEST["order"] as $order){
        $query["ORDER"][] = $_REQUEST["columns"][$order["column"]]["data"]." ".$order["dir"];
    }
    $response["data"] = $db->select(
        "contact_categories",
        array(
            "[>]contacts" => "contact_id"
        ),
        "*",
        $query
    );
    echo json_encode($response);

You'll probably be able to see that I've used Medoo again and within my client-side script I have two drop down selects with each General and Specific Category. With the Specific Category changing depending upon the General Category selected, and with all and blank added to allow for all records as well as for those without a Specific Category to be shown.

I also only needed to search using the General or Specific Category, nothing else. I only needed to order by the other remaining columns (the Date of the Category or the Full Name of the Contact), so it is somewhat crippled in functionality. I also have to do a couple of extra queries to get the correct counts for total records and filtered records... but it's not too shabby for all that and works a treat! I thought it'd take me ages to write this but it took less time than it's taken to write this all in all.

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.

Friday 20 March 2015

Datatable Jump to Page functionality

I love Datatables, I'm sure if you've read any of my posts on here you'll probably already know that. But I was recently asked to implement "Jump to Page" functionality on a table which had something like 9000 rows. The heavy lifting was done on the server but I needed a way to allow the user to jump to a given page in the table. The table itself is on a site that uses bootstrap so updated the table DOM to give me some space to work in like this:
,"dom": 'T<"clearfix">lfrt<"row"<"col-md-4"i><"#page-jump.col-md-3"><"col-md-5"p>>'
This gave me a little space to put a number input so I used the fnInitComplete to do just that:
,"fnInitComplete": function(){
    var pagination_data = table.page.info();
    var jump_form = $("<div></div>",{
        "class": "form-inline row",
        "id": "jump-form"
    });
    var jump_form_group = $("<div></div>",{
        "class":"form-group col-xs-8"
    }).appendTo(jump_form);
    jump_form_group.append($("<label></label>",{
        "class": "sr-only",
        "for":"jump_page_number",
        "text":"Page:"
    }));
    var jump_input_group = $("<div></div>",{
        "style": "width:100%;",
        "class":"input-group"
    }).appendTo(jump_form_group);
    jump_input_group.append($("<input>",{
        "type": "number",
        "class": "form-control",
        "id": "jump-number",
        "placeholder": "Page",
        "min": "1",
        "value": "1"
    }));
    jump_form.append($("<button></button>",{
        "class":"btn btn-primary col-xs-4",
        "id": "jump-form-button",
        "text": "Go"
    }));
    $("#page-jump").append(jump_form);
    $("#jump-number").attr("max", pagination_data.pages).val(pagination_data.page + 1);
}
In order to make sure that the max attribute was always correct and updated when the number of rows displayed changed I also used the fnDrawCallback function like this:
,"fnDrawCallback": function () {
    if (typeof table != 'undefined'){
        var pagination_data = table.page.info();
        $("#jump-number").attr("max", pagination_data.pages).val(pagination_data.page + 1);
    }
}
Then I got to thinking about the input and how it didn't make sense to allow the user to add page numbers which might not exist so I added this jQuery to not only correct incorrect input but to also take care of moving pages:
$(document).on("click", "#jump-form-button", function(){
    table.page(parseInt($("#jump-number").val(), 10) - 1).draw(false);
}).on("input", "#jump-number", function(){
    if(isNaN(parseInt($("#jump-number").val(), 10))){
        $("#jump-number").val(parseInt($("#jump-number").attr("min"), 10));
    }else{
        if(parseInt($("#jump-number").val(), 10) > parseInt($("#jump-number").attr("max"), 10)){
            $("#jump-number").val(parseInt($("#jump-number").attr("max"), 10))
        }else{
            if(parseInt($("#jump-number").val(), 10) < parseInt($("#jump-number").attr("min"), 10)){
                $("#jump-number").val(parseInt($("#jump-number").attr("min"), 10))
            }
        }
    }
});
I know it's not exactly rocket science but I found it to work a treat so I hope it helps someone.