elastic search datatables query

1. jquery.ejax-query-datatables.js

/*! Ejax Datatables - v0.0.1 - 2016-02-19
 * Copyright (c) 2016 charles peterson; Licensed MIT */
(function ($) {
    /**
     * debug_ejax = console.log alot of crap
     * elastic_json = the query for elastic
     * pkey = key to define the object data structure if y`ant to
     * serverUrl = elasticsearch server search path
     * callbackSearch = callback to change the search for your elastic_json based on databases search updates made by the user
     * callbackRow = callback to modify result row
     * callbackResponse = callback to modify the elasticsearch result as a whole
     * errorCallback = callback to handle errors for your datatable 
     *
     * @param opts
     *
     * @returns {Function}
     */
    $.fn.dataTable.ejax_query_datatables = function (opts) {
        // Configuration options
        var conf = $.extend({
            'debug_ejax': false,
            'elastic_json': {
                'query': {
                    'match_all': {}
                }
            },
            'pKey': '',
            'serverUrl': '',
            'callbackSearch': false,
            'callbackRow': false,
            'callbackResponse': false,
            'errorCallback': false
        }, opts);

        return function (data, callback, settings) {
            if (conf.debug_ejax) {
                console.log(conf.serverUrl+' data', data);
                console.log(conf.serverUrl+' settings', settings);
            }
            var request_json = conf.elastic_json;
            if (conf.debug_ejax) {
                console.log(conf.serverUrl+' request_json init', request_json);
            }
            // setup for nested query, used for our proxy system.
            // otherwise just update the elastic search data.
            if (request_json.elastic) {
                request_json.elastic.from = data.start;
                request_json.elastic.size = data.length;
                if(isFunction(conf.callbackSearch)){
                    conf.callbackSearch(request_json.elastic,data.search);
                }
            } else {
                request_json.from = data.start;
                request_json.size = data.length
                if(isFunction(conf.callbackSearch)){
                    conf.callbackSearch(request_json,data.search);
                }
            }
            if (data.order.length > 0) {
                /**
                 "sort" : [
                 { "post_date" : {"order" : "asc"}},
                 "user",
                 { "name" : "desc" },
                 { "age" : "desc" },
                 "_score"
                 ],
                 */
                request_json.elastic.sort = Array();
                for (var i = 0; i < data.order.length; i++) {
                    var columnName = data.columns[data.order[i].column].data;
                    var columnDir = data.order[i].dir;
                    request_json.elastic.sort[i] = {
                        [columnName]: {
                            'order': columnDir
                        }
                    }
                }
                request_json.elastic.sort[request_json.elastic.sort.length] = '_score';
            }
            if (conf.debug_ejax) {
                console.log(conf.serverUrl+' request_json final', request_json);
            }
            jQuery.ajax({
                url: conf.serverUrl,
                type: 'POST',
                dataType: 'json',
                data: request_json,
                success: function (response) {
                    if(response.error){
                        if (conf.debug_ejax) {
                            console.error(conf.serverUrl,'section', 'error', response.error);
                        }
                        if(isFunction(conf.errorCallback)){
                            row = conf.errorCallback(response);
                        } else {
                            alert( 'An error occurred on the server. Please try again in a minute.' );
                        }
                        return false;
                    }
                    if(isFunction(conf.callbackResponse)){
                        row = conf.callbackResponse(response);
                    }
                    if (conf.debug_ejax) {
                        console.log(conf.serverUrl+' response', response);
                    }
                    var responseData = {};
                    responseData.data = [];
                    if (response.hits.hits.length > 0) {
                        for (var i = 0; i < response.hits.hits.length; i++) {
                            var row = response.hits.hits[i]._source;
                            //console.log(conf.serverUrl+' response.hits.hits[i]._source',row );
                            row._index = response.hits.hits[i]._index;
                            if (conf.pKey) {
                                row.DT_RowId = row._index + '_' + row[conf.pKey];
                                row.DT_RowData = {
                                    "uniqueId": row.DT_RowId
                                };
                            }
                            if(isFunction(conf.callbackRow)){
                                row = conf.callbackRow(row);
                            }
                            responseData.data[i] = row;
                        }
                        responseData.draw = data.draw;
                        responseData.recordsTotal = response.hits.total;
                        responseData.recordsFiltered = response.hits.total;
                    }
                    if (conf.debug_ejax) {
                        console.log(conf.serverUrl+' responseData', responseData);
                    }
                    callback(responseData);
                },
                error: function (jqXHR, textStatus, errorThrown) {
                    //var jso = jQuery.parseJSON(jqXHR.responseText);
                    if(isFunction(conf.errorCallback)){
                        row = conf.errorCallback(jso);
                    } else {
                        alert( 'An error occurred on the server. Please try again in a minute.' );
                    }
                    if (conf.debug_ejax) {
                        console.error(conf.serverUrl,'section', 'error', '(' + jqXHR.status + ') ' + errorThrown + ' --<br />' + jso.error);
                    }
                }
            });
        };
    };
}(jQuery));

ajax-subdirectory-.htaccess

php_value include_path ".:../include:/usr/local/lib/php"

## No directory listings
IndexIgnore *

## Can be commented out if causes errors, see notes above.
Options +FollowSymlinks
Options -Indexes

## Mod_rewrite in use.

RewriteEngine On

RewriteRule .* - [E=HTTP_AUTHORIZATION:%{HTTP:Authorization}]
#
# If the requested path and file is not /index.php and the request
# has not already been internally rewritten to the index.php script
RewriteCond %{REQUEST_URI} !^/index\.php
# and the requested path and file doesn't directly match a physical file
RewriteCond %{REQUEST_FILENAME} !-f
# and the requested path and file doesn't directly match a physical folder
RewriteCond %{REQUEST_FILENAME} !-d
# internally rewrite the request to the index.php script
RewriteRule .* index.php [L]

ajax-subdirectory-index.php

<?php

// example of proxying an ajax request with changes to the data...
// uses structured form data as the elasticsearch query...

require_once('config.php');

/**
 *
 *
 * REQUIRES QUERY TO BE IN $_REQUEST['elastic']
 * @example
array(4) {
    ["elastic"]=>
  array(3) {
        ["from"]=>
    string(1) "0"
        ["size"]=>
    string(3) "250"
        ["query"]=>
    array(1) {
            ["filtered"]=>
      array(2) {
                ["query"]=>
        array(1) {
                    ["query_string"]=>
          array(2) {
                        ["query"]=>
            string(5) "world"
                        ["fields"]=>
            array(11) {
                            [0]=>
              string(7) "company"
                            [1]=>
              string(5) "first"
                            [2]=>
              string(4) "last"
                            [3]=>
              string(5) "login"
                            [4]=>
              string(7) "special"
                            [5]=>
              string(5) "title"
            }
          }
        }
        ["filter"]=>
        array(1) {
                    ["bool"]=>
          array(1) {
                        ["must"]=>
            array(4) {
                            [1]=>
              array(1) {
                                ["indices"]=>
                array(3) {
                                    ["indices"]=>
                  array(4) {
                                        [0]=>
                    string(6) "a"
                                        [1]=>
                    string(2) "b"
                  }
                  ["no_match_filter"]=>
                  string(4) "none"
                                    ["filter"]=>
                  string(2) "{}"
                }
              }
              [4]=>
              array(1) {
                                ["term"]=>
                array(1) {
                                    ["compid"]=>
                  string(0) ""
                }
              }
              [10]=>
              array(1) {
                                ["terms"]=>
                array(1) {
                                    ["approval"]=>
                  array(3) {
                                        [0]=>
                    string(1) "3"
                                        [1]=>
                    string(1) "2"
                                        [2]=>
                    string(1) "1"
                  }
                }
              }
              [14]=>
              array(1) {
                                ["range"]=>
                array(1) {
                                    ["parts"]=>
                  array(2) {
                                        ["gte"]=>
                    string(0) ""
                                        ["lte"]=>
                    string(0) ""
                  }
                }
              }
            }
          }
        }
      }
    }
  }
  ["type"]=>
  string(5) "cache"
    ["from"]=>
  string(1) "0"
    ["size"]=>
  string(3) "100"
}



*/
process_elastic_array($_REQUEST['elastic']);
$json = process_elastic_filters($_REQUEST['type'],$_REQUEST['elastic']);

//echo $json; exit;

$path = str_replace('/ajax/','',$_SERVER['REQUEST_URI']);
try{
    $response = do_post_request(_ELASTIC_SERVICE.'admin/'.$_REQUEST['type'].'/_search', $json);
} catch(Exception $e) {
    $response = json_encode(array(
        'error'=>$e->getMessage(),
        'request'=>$json
    ));

}

header('Content-Type: application/json');
echo $response;
exit;

/**
 * create a json string.
 * the sorting allows for objects to be reset to arrays where needed for elasticsearch
 *
 * @param $type
 * @param $data_array
 * @return string
 */
function process_elastic_filters($type,$data_array){
// alter arrays
    $fields['query']['cache'] = array("company","first","last","login","phone","keywords","email","website","mfgs");
    $fields['query']['user'] = array("first","last","login","special","title","phone","email");
    $fields['filter']['cache'] = array("compid","rep_id","tele","approval","region","state","country","parts");
    $fields['filter']['user'] = array("compid","security");

// find all keys NOT in fields and remove them.
    if(!empty($data_array["query"]["filtered"]["filter"]["bool"]["must"])){
        foreach($data_array["query"]["filtered"]["filter"]["bool"]["must"] as $x=>$value){
            if(isset($value['range'])){
                foreach($value['range'] as $k=>$key){
                    if(!in_array($k,$fields['filter'][$type])){
                        unset($data_array["query"]["filtered"]["filter"]["bool"]["must"][$x]);
                    }
                }
            }
            if(isset($value['term'])){
                foreach($value['term'] as $k=>$key){
                    if(!in_array($k,$fields['filter'][$type])){
                        unset($data_array["query"]["filtered"]["filter"]["bool"]["must"][$x]);
                    }
                }
            }
            if(isset($value['terms'])){
                foreach($value['terms'] as $k=>$key){
                    if(!in_array($k,$fields['filter'][$type])){
                        unset($data_array["query"]["filtered"]["filter"]["bool"]["must"][$x]);
                    }
                }
            }
        }
        sort($data_array["query"]["filtered"]["filter"]["bool"]["must"]);
    }

// keep only the intersection of fields to compare string to
    if(isset($data_array["query"]["filtered"]["query"]["query_string"]["fields"])) {
        $data_array["query"]["filtered"]["query"]["query_string"]["fields"] = array_intersect($data_array["query"]["filtered"]["query"]["query_string"]["fields"], $fields['query'][$type]);
        sort($data_array["query"]["filtered"]["query"]["query_string"]["fields"]);
    }

    sort($data_array["sort"]);

    return json_encode($data_array);
}

/**
 * @param $data_array
 */
function process_elastic_array(&$data_array){
    if(!empty($data_array["query"]["filtered"]["query"]["query_string"]["query"])){
        $data_array["query"]["filtered"]["query"]["query_string"]["query"] = '*' . $data_array["query"]["filtered"]["query"]["query_string"]["query"] . '*';
    } else {
        $data_array["query"]["filtered"]["query"]["query_string"]["query"]='*';
    }

// cleanup the request variables...
    if(!empty($data_array["query"]["filtered"]["filter"]["bool"]["must"])){
        foreach($data_array["query"]["filtered"]["filter"]["bool"]["must"] as $x=>&$value){
            if(isset($value['range'])){
                foreach($value['range'] as $k=>&$key){
                    if(empty($key['gte']) || $key['gte']<=0){
                        unset($key['gte']);
                    }
                    if(empty($key['lte']) || $key['lte']<=0){
                        unset($key['lte']);
                    }
                    if(empty($key)){
                        unset($value['range'][$k]);
                    }
                }
                if(empty($value['range'])){
                    unset($data_array["query"]["filtered"]["filter"]["bool"]["must"][$x]);
                }
            }
            if(isset($value['term'])){
                foreach($value['term'] as $k=>&$key){
                    if(empty($key)){
                        unset($value['term'][$k]);
                    }
                }
                if(empty($value['term'])){
                    unset($data_array["query"]["filtered"]["filter"]["bool"]["must"][$x]);
                }
            }
            if(isset($value['indices'])){
                foreach($value['indices'] as $k=>$valx){
                    if($valx=='{}'){
                        $data_array["query"]["filtered"]["filter"]["bool"]["must"][$x]['indices']['filter'] = new stdClass();
                    }
                }
            }
        }
    }
    if($data_array["query"]["filtered"]["query"]["query_string"]["query"]===''){
        unset($data_array["query"]["filtered"]["query"]["query_string"]);
        $data_array["query"]["filtered"]["query"]["match_all"]=new stdClass();
    }
}

datatable_users.js

        $('#users').DataTable({
            'search': {
                'search': 'this is a search string...'
            },
            'columns': [
                {
                    'title': 'Login',
                    'data': 'login',
                    'name': 'login',
                    'defaultContent': "n/a"
                },
                {
                    'title': 'COMP',
                    'data': 'company',
                    'name': 'company',
                    'defaultContent': "n/a"
                },
                {
                    'title': 'First',
                    'data': 'first',
                    'name': 'first',
                    'defaultContent': "n/a"
                },
                {
                    'title': 'Last',
                    'data': 'last',
                    'name': 'last',
                    'defaultContent': "n/a"
                },
                {
                    'title': 'Email',
                    'data': 'email',
                    'name': 'email',
                    'defaultContent': "n/a",
                    'render': function (data, type, full) {
                        return '<a href="mailto:'+data+'">'+data+'</a>';
                    }
                },
                {
                    'title': 'Phone',
                    'data': 'phone',
                    'name': 'phone',
                    'defaultContent': "n/a"
                },
                {
                    'title': 'Position',
                    'data': 'position',
                    'name': 'position'
                }
            ],
            "processing": true,
            "serverSide": true,
            "ajax": $.fn.dataTable.ejax_query_datatables({
                'debug_ejax': true,
                'elastic_json': userElasticJson,
                'pKey': 'login',
                'serverUrl': 'https://localhsot/ajax/admin/user/_search',
                'callbackSearch': function (request, search) {
                    request.query.filtered.query.query_string.query = search.value;
                },
                'errorCallback': function (response) {
                    $('#users_processing').removeClass('panel').removeClass('panel-default').addClass('alert').addClass('alert-danger').html(response.error);
                }
            })
        });