Hi everybody, its very good library for datatables (i’am a huge fan of datatables) but when i start using CI with Doctrine models relations with database i rewrite this library.
here it is :
Library :
<?php
if (!defined("BASEPATH"))
exit("No direct script access allowed");
/**
* Datatables(.net) for CodeIgniter
*
* This class/library is an attempt to port the native Datatables php script
* found at http://datatables.net/examples/data_sources/server_side.html for CodeIgniter
*
* @package CodeIgniter
* @subpackage libraries
* @category library
* @version 1.0
* @author Vincent Bambico <vb@newmediaservices.com.au>
* @link http://ellislab.com/forums/viewthread/160896/
*
* @upgrade for doctrine by Simonas Šerlinskas simonas@pc.lt
*/
class Datatables {
/**
* CodeIgniter global variable
*
* @global object $ci
* @name $ci
*/
protected $ci;
/**
* Copies an instance of CI
*/
public function __construct() {
$this->ci = & get_instance();
}
/**
* Builds all the necessary query segments and performs the main query based on passed arguments
*
* @param string $model
* @param string $columns
* @param string $index
* @return string
*/
public function generate($model, $columns, $index) {
/*
* WHERE clause
*/
$sWhere = "";
if ($this->ci->input->post("sSearch") != "") {
$sWhere = "";
for ($i = 0; $i < count($columns); $i++)
$sWhere .= $columns[$i] . " LIKE '%" . $this->ci->input->post("sSearch") . "%' OR ";
$sWhere = substr_replace($sWhere, "", -3);
}
if ($sWhere == "") {
$sWhere = '1';
}
/*
* ORDER clause
*/
$sOrder = "";
if ($this->ci->input->post("iSortCol_0") != null) {
$sOrder = "";
for ($i = 0; $i < intval($this->ci->input->post("iSortingCols")); $i++)
$sOrder .= $columns[intval($this->ci->input->post("iSortCol_" . $i))] . " " . $this->ci->input->post("sSortDir_" . $i) . ", ";
$sOrder = substr_replace($sOrder, "", -2);
}
if ($sOrder == "") {
$sOrder = $columns[1];
}
/*
* LIMIT clause
*/
$sLimit = "LIMIT ";
if ($this->ci->input->post("iDisplayStart") && $this->ci->input->post("iDisplayLength") != "-1")
$sLimit .= $this->ci->input->post("iDisplayStart") . ", " . $this->ci->input->post("iDisplayLength");
else {
$iDisplayLength = $this->ci->input->post("iDisplayLength");
if (empty($iDisplayLength))
$sLimit .= "0,10";
else
$sLimit .= "0," . $iDisplayLength;
}
$select = implode(", ", $columns);
$q = Doctrine_Query::create()
->select($select)
->from($model)
->where($sWhere)
/* in order clause is joined the limit clause, because i did not find how to range result in doctrine */
->orderBy($sOrder . " " . $sLimit)
->setHydrationMode(Doctrine::HYDRATE_ARRAY);
$rResult = $q->execute();
/*
* Count all records without ordering and pagination.
*
* if someone knows how to get correct num rows at that query before of all records let me know simonas@pc.lt
*/
$q_all = Doctrine_Query::create()
->select('COUNT(id) AS num_rows')
->from($model)
->where($sWhere)
->setHydrationMode(Doctrine::HYDRATE_ARRAY);
$rResult_all = $q_all->execute();
$num_all = $rResult_all[0]['num_rows'];
$aaData = array();
$row = 0;
foreach ($rResult as $r) {
for ($i = 0; $i < count($columns); $i++) {
$aaData[$row][$i] = $r[$columns[$i]];
}
$row++;
}
$sOutput = array
(
"sEcho" => intval($this->ci->input->post("sEcho")),
"iTotalRecords" => "$num_all",
"iTotalDisplayRecords" => "$num_all",
"aaData" => $aaData
);
return json_encode($sOutput);
}
}
in controler you need to define MODEL name not table!
Hope it helps, works very well, and for me its very useful.
