jQuery Datatables Integration

[eluser]Destreyf[/eluser]
Hello, i have a piece of code to present today that allows easier interaction with jQuery DataTables by Allan Jardine.

I was recently faced with a scenario where i needed to be able to easily return a data structure and print it out, (JSON loading) the given code seemed too heavy, so i have here today a small Library, that does just that, in a simple move.

This is a Active Record driven library, it balances on that, allowing you to use alias's in your columns, and tables, its just as simple as issuing a regular Active Record Query.

A couple downsides to the code is it requires PHP5+, however, i don't see much downside in that, it also is written for Codeigniter 1.7.X i haven't moved to 2.0 quite yet on this project that I'm busy with.

Code:

<?php
/* Requires PHP5 */
class Datatables {
    /* Codeigniter Base */
    var $CI            = NULL;
    
    /* Column Mapping System */
    var $colums        = array();
    var $col_map    = array();
    
    /* Limit and Sorting (NYI) */
    var $default_limit    = false;
    
    
    function __construct(){
        $this->CI =& get_instance();
    }
    
    function setLimit($limit){
        if(is_int($limit)){
            if($limit > 0){
                $this->default_limit = $limit;
            } else {
                throw new Exception("You must use a value greater than zero when defining a limit","1000");
            }
        } else {
            throw new Exception("You must use an integer value.","1001");
        }
    }
    
    function addColumn($column,$name=NULL){
        if(is_array($column)){
            foreach($column as $column_name => $alias_as){
                if(is_int($column_name)){
                    $this->columns[$alias_as] = $alias_as;
                    $this->col_map[] = $alias_as;
                } else {
                    $this->columns[$column_name] = $alias_as;
                    $this->col_map[] = $column_name;
                }
            }
        } else {
            $this->columns[$column] = (is_null($name) ? $column : $name);
            $this->col_map[] = $column;
        }
    }
    
    function get($query,$table=NULL,$columns=array()){
        /* Bind Columns that may have been passed in */
        foreach($columns as $column => $name){
            if(!is_int($column)){
                $this->addColumn($column,$name);
            } else {
                $this->addColumn($name,$name);
            }
        }
        /* ***************************************** */
        
        /* Map Some Default Variables */
        $cols        = $this->CI->input->post('iColumns');
        $column_map    = $this->col_map;
        /* ************************** */
        
        /* Sorting Options */
        $sorting_count = (int)$this->CI->input->post('iSortingCols');
        if($sorting_count > 0){
            $sort = 'asc';
            for($i=0;$i<$sorting_count;$i++){
                if($sort = $this->CI->input->post('sSortDir_'.$i)){
                    $j = (int)$this->CI->input->post('iSortCol_'.$i);
                    $query->order_by($column_map[$j],$sort);
                }
            }
        }
        /* *************** */
        
        /* Search Keyword */
        $search = $this->CI->input->post('sSearch');
        if(strlen($search) > 0){
            $fields            = array();
            $search_split    = explode(' ',$search);
            for($i=0;$i<$cols;$i++){
                $items = array($search);
                if(count($search_split) > 1){
                    foreach($search_split as $item){
                        if($item != ''){
                            $items[] = $item;
                        }
                    }
                }
                $fields[] = $column_map[$i] . " REGEXP \"".implode("|",$items)."\"";
            }
            $str = "(".implode("||",$fields).")";
            $query->where($str);
        }
        /* ************** */
        
        $total = clone $query;
        $total = $total->count_all_results('cdr');
        
        $limit = ($this->CI->input->post('iDisplayLength') ? $this->CI->input->post('iDisplayLength') : ($this->default_limit ? $this->default_limit : false));
        
        if($limit){
            $offset = $this->CI->input->post('iDisplayStart');
            $query->limit($limit,($offset ? $offset : 0));
        }
        
        foreach($this->columns as $column_name => $alias_as){
            $query->select("$column_name AS $alias_as");
        }
        if(is_null($table)){
            $query = $query->get();
        } else {
            $query = $query->get($table);
        }
        
        $rows = array();
        foreach($query->result_array() as $row){
            $rows[] = array_values($row);
        }
        
        $data = array(
            'iTotalRecords'            => $query->num_rows(),
            'iTotalDisplayRecords'    => $total,
            'aaData'                => $rows,
        );
        return $data;
    }
}
?&gt;

Continued Next Post!

[eluser]Destreyf[/eluser]
Continued!

The usage for this code is very simple.

Code:

<?php
class Example extends Controller {
    function get_data(){
        $column_map    = array('clock_date','clock_method');
        $this->load->library('Datatables');


        $this->datatables->addColumn($column_map);
        $q    = $this->db->where('employee_id','12345')

        $data = $this->datatables->get($q,'employee_timecard');
        echo json_encode($data);
    }
}

A basic explanation would that you build you query, outside of the datatables library (using models, ect) and once you get it to the point where you're ready, assign it to a variable and pass it in as an argument, along with the table name, it will automatically build all of the order by columns, and the searches.

Some alternative syntax is as follows:

Code:

class Example extends Controller {
    function get_data(){
        $this->load->library('Datatables');

        $data = $this->datatables->get($this->db->where('employee_id','12345'),'employee_timecard',array('clock_date','clock_method'));
        echo json_encode($data);
    }
}

I believe this to be a simple alternative, my setting for jQuery DataTables by Allan Jardine are as follows:

Code:

$(document).ready(function() {
    $('#example').dataTable( {
        "bProcessing": true,
        "bServerSide": true,
        "sAjaxSource": "/example/get_data.html",
        'fnServerData': function(sSource, aoData, fnCallback){
            $.ajax({
                'dataType': 'json',
                'type'    : 'POST',
                'url'     : sSource,
                'data'    : aoData,
                'success' : fnCallback
            });
        }
    });
});

The code provided here is provided as is, with no warranty - Use at your own risk or advantage, a few notes to be heard.

This does not Currently support the "Non Searchable" and "Non Sortable" options housed by the jQuery DataTables plug-in, but i plan on adding those soon, as well as filling in some additional functionality, however, my free time is sparse, and i figured I'd contribute back to a fantastic community with what little i can.

[eluser]cryogenix[/eluser]
erm have you seen our implementation of this already?

http://ellislab.com/forums/viewthread/160896/

if you don't mind forking from there, your active record version can refactor our library. =)

[eluser]Destreyf[/eluser]
Hello cryogenix, i have seen your code, and i have used it, however, i felt it was too enforced on how to access it, the active record implementation is simple, as you can see, and if you'd like to integrate the functionality, please do, i would love for my code to be used in a manor that helps everyone. However, for my needs, what i have is sufficient, i will be posting a few more updates to my code in order to add a couple bits of functionality, but for the most part, its ready to go out of the box.

[eluser]4ever[/eluser]
Could you bh and explain me simply what this code serves for? I was on the link datatables just don't know what does it serve for.

[eluser]Destreyf[/eluser]
DataTables is a jQuery library, designed to make Pagination, Searching, Limiting, and Sorting SUPER easy, and while it does, i deal with some 1+million records at some times that have to be sorted out, so using its native javascript HTML Table functionality does not work with my needs, so i used its integrated AJAX loading functionality, to retrieve a backend result generated JSON object, all this Library does, it makes formatting the data, and getting a response easier with as little code as possible.