Closed ghost closed 5 years ago
jdriesen
commented
9 years ago Hello,
It seems that the official version of this library also has some probs with filtering on individual columns. (global searching is working for me)
Does your code solve the problem on filtering individual columns?
Thanks in advance for your reply,
Johnny
ghost
commented
9 years ago Hi Johnny,
I haven't really tried, it might work, at the present moment I wanted library to handle pagination, global search and sorting.
I had to amend the javascript code that I had in an initial post to the one below:
var oTable = $('#enquiry_table').dataTable({
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "<?php echo base_url(); ?>welcome/datatable",
"columns": [
{ "name": "enquiry.id", "data": "id" },
{ "name": "enquiry_status.status", "data": "status" },
{ "name": "enquiry.created_on", "data": "created_on" },
{ "name": "enquiry.leaving_on", "data": "leaving_on" },
{ "name": "enquiry.price", "data": "price" },
{ "name": "enquiry.callback", "data": "callback" },
{ "data": "actions", "orderable": false, "searchable": false }
],
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"iDisplayStart ":20,
'fnServerData': function(sSource, aoData, fnCallback)
{
$.ajax
({
'dataType': 'json',
'type' : 'POST',
'url' : sSource,
'data' : aoData,
'success' : fnCallback
});
},
"order": [
[0, "desc"]
]
});Where I define columns, each column got to have a "name" param that defines to which column in DB column is linked to. So the column 'id' is linked to the DB column: enquiry.id. Also you got to specified which columns can be searched and / or ordered.
One thing that I have identified is that you can't call class functions within add_column or edit_column, so I had to loop through json that datatables have returned.
Below is just an example of what I mean:
$this->datatables->select('id,client_id,status')
->from('jobs');
$data = json_decode($this->datatables->generate("json"), true);
foreach($data['data'] as $key => $ele)
{
$ele['workers'] = $this->getWorkers($ele['id']);
$data['data'][$key] = $ele;
}
echo json_encode($data); Hi Spikerok,
Thanks for your reply ! Highly appreciated !
I'm coming back on this tomorrow with a working - online - example (global search, pagination, 100000+ records.
(Just column searching is still a problem... but I'm working on it ...)
Kind regards,
Johnny
jdriesen
commented
9 years ago Hi Vladislav,
First of all, THANKS for your code updates. Really a great help (and I guess not only for me ...)
As promised yesterday, I've created a small sample program. It can be found at http://www.3sn.info (see left menu under "tests" ... the Add, Edit and View functionalities do NOT work yet... so no worries about that)
This program has 2 'entities' ...
Entity 1, named 'fakes'
Entity 2, named 'tools'
By the fact I'm writing an APP which at the end will contain a lot of large tables, it would be great if column searching also can be done on the server, but I'm struggling with it for more than a week now ... ... When you 've some time, can I ask you to take a look at it ? Your input / ideas are highly appreciated !
Thanks in advance for your reply,
Johnny
jdriesen
commented
9 years ago Hi,
I'm afraid the global search functionality (searching in all colums) is not working when you're joining 2 tables with the same fieldname ...
Grtz,
Johnny
ghost
commented
9 years ago Hi Johnny,
I will look it up.
Vlad.
ghost
commented
9 years ago Example of jQuery, this is for datatables pre1.10, so rather than sName you need to use "name" and rather than mData you need to use "data". Same applies to other pre1.10 params.
On the page I have three buttons that enable filtering by status column and I have 3 statuses, I can either switch all on, or have none.. basically any type of combinations. On the load, by default, i want to show status "PENDING" and "IN PROGRESS" so I trigger javascript below
$('#table').dataTable().fnFilter("Pending|IN PROGRESS", 0, true);So above code states that I want to have status "PENDING" or "IN PROGRESS" for column 0.As result the db query that is sent by datatables will include following lines:
`jobs_status`.`name` LIKE '%Completed%'
OR `jobs_status`.`name` LIKE '%Pending%'Jquery used to trigger datatables
$('#table').dataTable({
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "/welcome/datatables",
"sAjaxDataProp": "data",
"fnServerData": function ( sSource, aoData, fnCallback, oSettings ) {
oSettings.jqXHR = $.ajax( {
"dataType": 'json',
"type": "POST",
"url": sSource,
"data": aoData,
"success": fnCallback,
error: function(XMLHttpRequest, textStatus, errorThrown) {
console.log(XMLHttpRequest);
console.log(textStatus);
console.log(errorThrown);
}
} );
},
"aoColumns": [
{ "sName": "jobs_status.name", "mData": "status_label" },
{ "sName": "ident", "sType": "date-ident", "mData": "ident" },
{ "sName": "user_profiles.data", "mData": "client_name" },
{ "bSortable": false, "bSearchable": false, "mData": "workers" },
{ "sName": "created_on", "sType": "date-euro", "mData": "created_on" },
{ "sName": "deadline", "sType": "date-euro", "mData": "deadline" },
{ "bSortable": false, "bSearchable": false, "mData": "file_count" },
{ "bSortable": false, "bSearchable": false, "mData": "action" }
],
"aLengthMenu": [
[10, 25, 50, -1],
[10, 25, 50, "All"] // change per page values here
],
// set the initial value
"iDisplayLength": 10,
"sPaginationType": "bootstrap",
"oLanguage": {
"sLengthMenu": "_MENU_ records",
"oPaginate": {
"sPrevious": "Prev",
"sNext": "Next"
}
},
"aaSorting": [[ 1, "desc" ]],
});Example of PHP code within controller
$this->datatables->select('jobs.id, jobs.client_id, jobs.status, jobs_status.name as status_label, jobs.created_on,
jobs.deadline, jobs.ident, jobs.completed_on,
user_profiles.data as client_name')
->from('jobs')
->join('user_profiles', 'jobs.client_id = user_profiles.user_id', 'left')
->join('jobs_status', 'jobs.status = jobs_status.id', 'left')
->join('job_assign', 'job_assign.job_id = jobs.id', 'left')
->where('user_profiles.meta', 'name')
->group_by('jobs.id');
$data = json_decode($this->datatables->generate("json"), true);
foreach($data['data'] as $key => $ele)
{
$ele['action'] = $this->getButtons($ele['id'], $ele['status']);
$ele['status_label'] = $this->getStatusLabel($ele['status']);
$ele['file_count'] = $this->countFiles($ele['id']);
$ele['workers'] = $this->getWorkers($ele['id']);
$ele['created_on'] = $this->formatDateUK($ele['created_on']);
$data['data'][$key] = $ele;
}
echo json_encode($data); Updated datatables.php file:
<?php if(!defined('BASEPATH')) exit('No direct script access allowed');
/**
* Ignited Datatables
*
* This is a wrapper class/library based on the native Datatables server-side implementation by Allan Jardine
* found at http://datatables.net/examples/data_sources/server_side.html for CodeIgniter
*
* @package CodeIgniter
* @subpackage libraries
* @category library
* @version 2.0 <beta>
* @author Vincent Bambico <metal.conspiracy@gmail.com>
* Yusuf Ozdemir <yusuf@ozdemir.be>
* @link http://ellislab.com/forums/viewthread/160896/
*/
class Datatables
{
/**
* Global container variables for chained argument results
*
*/
private $ci;
private $table;
private $distinct;
private $group_by = array();
private $select = array();
private $joins = array();
private $columns = array();
private $where = array();
private $or_where = array();
private $where_in = array();
private $like = array();
private $filter = array();
private $add_columns = array();
private $edit_columns = array();
private $unset_columns = array();
/**
* Copies an instance of CI
*/
public function __construct()
{
$this->ci =& get_instance();
}
/**
* If you establish multiple databases in config/database.php this will allow you to
* set the database (other than $active_group) - more info: http://ellislab.com/forums/viewthread/145901/#712942
*/
public function set_database($db_name)
{
$db_data = $this->ci->load->database($db_name, TRUE);
$this->ci->db = $db_data;
}
/**
* Generates the SELECT portion of the query
*
* @param string $columns
* @param bool $backtick_protect
* @return mixed
*/
public function select($columns, $backtick_protect = TRUE)
{
foreach($this->explode(',', $columns) as $val)
{
$column = trim(preg_replace('/(.*)\s+as\s+(\w*)/i', '$2', $val));
$column = preg_replace('/.*\.(.*)/i', '$1', $column); // get name after `.`
$this->columns[] = $column;
$this->select[$column] = trim(preg_replace('/(.*)\s+as\s+(\w*)/i', '$1', $val));
}
$this->ci->db->select($columns, $backtick_protect);
return $this;
}
/**
* Generates the DISTINCT portion of the query
*
* @param string $column
* @return mixed
*/
public function distinct($column)
{
$this->distinct = $column;
$this->ci->db->distinct($column);
return $this;
}
/**
* Generates a custom GROUP BY portion of the query
*
* @param string $val
* @return mixed
*/
public function group_by($val)
{
$this->group_by[] = $val;
$this->ci->db->group_by($val);
return $this;
}
/**
* Generates the FROM portion of the query
*
* @param string $table
* @return mixed
*/
public function from($table)
{
$this->table = $table;
return $this;
}
/**
* Generates the JOIN portion of the query
*
* @param string $table
* @param string $fk
* @param string $type
* @return mixed
*/
public function join($table, $fk, $type = NULL)
{
$this->joins[] = array($table, $fk, $type);
$this->ci->db->join($table, $fk, $type);
return $this;
}
/**
* Generates the WHERE portion of the query
*
* @param mixed $key_condition
* @param string $val
* @param bool $backtick_protect
* @return mixed
*/
public function where($key_condition, $val = NULL, $backtick_protect = TRUE)
{
$this->where[] = array($key_condition, $val, $backtick_protect);
$this->ci->db->where($key_condition, $val, $backtick_protect);
return $this;
}
/**
* Generates the WHERE portion of the query
*
* @param mixed $key_condition
* @param string $val
* @param bool $backtick_protect
* @return mixed
*/
public function or_where($key_condition, $val = NULL, $backtick_protect = TRUE)
{
$this->or_where[] = array($key_condition, $val, $backtick_protect);
$this->ci->db->or_where($key_condition, $val, $backtick_protect);
return $this;
}
/**
* Generates the WHERE IN portion of the query
*
* @param mixed $key_condition
* @param string $val
* @param bool $backtick_protect
* @return mixed
*/
public function where_in($key_condition, $val = NULL, $backtick_protect = TRUE)
{
$this->where_in[] = array($key_condition, $val, $backtick_protect);
$this->ci->db->where_in($key_condition, $val, $backtick_protect);
return $this;
}
/**
* Generates the WHERE portion of the query
*
* @param mixed $key_condition
* @param string $val
* @param bool $backtick_protect
* @return mixed
*/
public function filter($key_condition, $val = NULL, $backtick_protect = TRUE)
{
$this->filter[] = array($key_condition, $val, $backtick_protect);
return $this;
}
/**
* Generates a %LIKE% portion of the query
*
* @param mixed $key_condition
* @param string $val
* @param bool $backtick_protect
* @return mixed
*/
public function like($key_condition, $val = NULL, $backtick_protect = TRUE)
{
$this->like[] = array($key_condition, $val, $backtick_protect);
$this->ci->db->like($key_condition, $val, $backtick_protect);
return $this;
}
/**
* Sets additional column variables for adding custom columns
*
* @param string $column
* @param string $content
* @param string $match_replacement
* @return mixed
*/
public function add_column($column, $content, $match_replacement = NULL)
{
$this->add_columns[$column] = array('content' => $content, 'replacement' => $this->explode(',', $match_replacement));
return $this;
}
/**
* Sets additional column variables for editing columns
*
* @param string $column
* @param string $content
* @param string $match_replacement
* @return mixed
*/
public function edit_column($column, $content, $match_replacement)
{
$this->edit_columns[$column][] = array('content' => $content, 'replacement' => $this->explode(',', $match_replacement));
return $this;
}
/**
* Unset column
*
* @param string $column
* @return mixed
*/
public function unset_column($column)
{
$column=explode(',',$column);
$this->unset_columns=array_merge($this->unset_columns,$column);
return $this;
}
/**
* Builds all the necessary query segments and performs the main query based on results set from chained statements
*
* @param string $output
* @param string $charset
* @return string
*/
public function generate($output = 'json', $charset = 'UTF-8')
{
if(strtolower($output) == 'json')
{
$this->get_paging();
}
$this->get_ordering();
$this->get_filtering();
return $this->produce_output(strtolower($output), strtolower($charset));
}
/**
* Generates the LIMIT portion of the query
*
* @return mixed
*/
private function get_paging()
{
$iStart = $this->ci->input->post('iDisplayStart');
$iLength = $this->ci->input->post('iDisplayLength');
if($iLength != '' && $iLength != '-1')
$this->ci->db->limit($iLength, ($iStart)? $iStart : 0);
}
/**
* Generates the ORDER BY portion of the query
*
* @return mixed
*/
private function get_ordering()
{
if($this->ci->input->post('iSortCol_0') || $this->ci->input->post('iSortCol_0') == 0)
{
for($i = 0; $i < intval($this->ci->input->post('iSortingCols')); $i++)
{
if($this->ci->input->post('bSortable_' . intval($this->ci->input->post('iSortCol_' . $i))) == "true")
{
$this->ci->db->order_by($this->ci->input->post('mDataProp_' . $this->ci->input->post('iSortCol_' . $i)), $this->ci->input->post('sSortDir_' . $i));
}
}
}
}
/**
* Generates a %LIKE% portion of the query
*
* @return mixed
*/
private function get_filtering()
{
$sColumns = explode(",", $this->ci->input->post('sColumns'));
if ($this->ci->input->post('sSearch') && $this->ci->input->post('sSearch') != "" )
{
$search_terms = explode("|", $this->ci->input->post('sSearch'));
for ( $i=0 ; $i < $this->ci->input->post('iColumns'); $i++ )
{
if($this->ci->input->post('bSearchable_' . $i) == "true")
{
foreach($search_terms as $key => $ele)
{
if(strlen($ele) > 0)
{
$this->ci->db->or_like($sColumns[$i], $ele);
}
}
}
}
}
for ( $i=0 ; $i < $this->ci->input->post('iColumns'); $i++ )
{
if($this->ci->input->post('bSearchable_' . $i) == "true")
{
$search_terms = explode("|", $this->ci->input->post('sSearch_' . $i));
foreach($search_terms as $key => $ele)
{
if(strlen($ele) > 0)
{
$this->ci->db->or_like($sColumns[$i], $ele);
}
}
}
}
}
/**
* Compiles the select statement based on the other functions called and runs the query
*
* @return mixed
*/
private function get_display_result()
{
return $this->ci->db->get($this->table);
}
/**
* Builds an encoded string data. Returns JSON by default, and an array of aaData if output is set to raw.
*
* @param string $output
* @param string $charset
* @return mixed
*/
private function produce_output($output, $charset)
{
$aaData = array();
$rResult = $this->get_display_result();
if($output == 'json')
{
$iTotal = $this->get_total_results();
$iFilteredTotal = $this->get_total_results(TRUE);
}
foreach($rResult->result_array() as $row_key => $row_val)
{
$aaData[$row_key] = ($this->check_cType())? $row_val : array_values($row_val);
foreach($this->add_columns as $field => $val)
if($this->check_cType())
$aaData[$row_key][$field] = $this->exec_replace($val, $aaData[$row_key]);
else
$aaData[$row_key][] = $this->exec_replace($val, $aaData[$row_key]);
foreach($this->edit_columns as $modkey => $modval)
foreach($modval as $val)
$aaData[$row_key][($this->check_cType())? $modkey : array_search($modkey, $this->columns)] = $this->exec_replace($val, $aaData[$row_key]);
$aaData[$row_key] = array_diff_key($aaData[$row_key], ($this->check_cType())? $this->unset_columns : array_intersect($this->columns, $this->unset_columns));
if(!$this->check_cType())
$aaData[$row_key] = array_values($aaData[$row_key]);
}
if($output == 'json')
{
$sOutput = array
(
'draw' => intval($this->ci->input->post('draw')),
'recordsTotal' => $iTotal,
'recordsFiltered' => $iFilteredTotal,
'data' => $aaData
);
if($charset == 'utf-8')
return json_encode($sOutput);
else
return $this->jsonify($sOutput);
}
else
return array('aaData' => $aaData);
}
/**
* Get result count
*
* @return integer
*/
private function get_total_results($filtering = FALSE)
{
if($filtering)
$this->get_filtering();
foreach($this->joins as $val)
$this->ci->db->join($val[0], $val[1], $val[2]);
foreach($this->where as $val)
$this->ci->db->where($val[0], $val[1], $val[2]);
foreach($this->or_where as $val)
$this->ci->db->or_where($val[0], $val[1], $val[2]);
foreach($this->where_in as $val)
$this->ci->db->where_in($val[0], $val[1], $val[2]);
foreach($this->group_by as $val)
$this->ci->db->group_by($val);
foreach($this->like as $val)
$this->ci->db->like($val[0], $val[1], $val[2]);
if(strlen($this->distinct) > 0)
{
$this->ci->db->distinct($this->distinct);
$this->ci->db->select($this->columns);
}
$query = $this->ci->db->get($this->table, NULL, NULL, FALSE);
return $query->num_rows();
}
/**
* Runs callback functions and makes replacements
*
* @param mixed $custom_val
* @param mixed $row_data
* @return string $custom_val['content']
*/
private function exec_replace($custom_val, $row_data)
{
$replace_string = '';
if(isset($custom_val['replacement']) && is_array($custom_val['replacement']))
{
//Added this line because when the replacement has over 10 elements replaced the variable "$1" first by the "$10"
$custom_val['replacement'] = array_reverse($custom_val['replacement'], true);
foreach($custom_val['replacement'] as $key => $val)
{
$sval = preg_replace("/(?<!\w)([\'\"])(.*)\\1(?!\w)/i", '$2', trim($val));
if(preg_match('/(\w+::\w+|\w+)\((.*)\)/i', $val, $matches) && is_callable($matches[1]))
{
$func = $matches[1];
$args = preg_split("/[\s,]*\\\"([^\\\"]+)\\\"[\s,]*|" . "[\s,]*'([^']+)'[\s,]*|" . "[,]+/", $matches[2], 0, PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE);
foreach($args as $args_key => $args_val)
{
$args_val = preg_replace("/(?<!\w)([\'\"])(.*)\\1(?!\w)/i", '$2', trim($args_val));
$args[$args_key] = (in_array($args_val, $this->columns))? ($row_data[($this->check_cType())? $args_val : array_search($args_val, $this->columns)]) : $args_val;
}
$replace_string = call_user_func_array($func, $args);
}
elseif(in_array($sval, $this->columns))
$replace_string = $row_data[($this->check_cType())? $sval : array_search($sval, $this->columns)];
else
$replace_string = $sval;
$custom_val['content'] = str_ireplace('$' . ($key + 1), $replace_string, $custom_val['content']);
}
}
return $custom_val['content'];
}
/**
* Check column type -numeric or column name
*
* @return bool
*/
private function check_cType()
{
$column = $this->ci->input->post('columns');
if(is_numeric($column[0]['data']))
return FALSE;
else
return TRUE;
}
/**
* Return the difference of open and close characters
*
* @param string $str
* @param string $open
* @param string $close
* @return string $retval
*/
private function balanceChars($str, $open, $close)
{
$openCount = substr_count($str, $open);
$closeCount = substr_count($str, $close);
$retval = $openCount - $closeCount;
return $retval;
}
/**
* Explode, but ignore delimiter until closing characters are found
*
* @param string $delimiter
* @param string $str
* @param string $open
* @param string $close
* @return mixed $retval
*/
private function explode($delimiter, $str, $open = '(', $close=')')
{
$retval = array();
$hold = array();
$balance = 0;
$parts = explode($delimiter, $str);
foreach($parts as $part)
{
$hold[] = $part;
$balance += $this->balanceChars($part, $open, $close);
if($balance < 1)
{
$retval[] = implode($delimiter, $hold);
$hold = array();
$balance = 0;
}
}
if(count($hold) > 0)
$retval[] = implode($delimiter, $hold);
return $retval;
}
/**
* Workaround for json_encode's UTF-8 encoding if a different charset needs to be used
*
* @param mixed $result
* @return string
*/
private function jsonify($result = FALSE)
{
if(is_null($result))
return 'null';
if($result === FALSE)
return 'false';
if($result === TRUE)
return 'true';
if(is_scalar($result))
{
if(is_float($result))
return floatval(str_replace(',', '.', strval($result)));
if(is_string($result))
{
static $jsonReplaces = array(array('\\', '/', '\n', '\t', '\r', '\b', '\f', '"'), array('\\\\', '\\/', '\\n', '\\t', '\\r', '\\b', '\\f', '\"'));
return '"' . str_replace($jsonReplaces[0], $jsonReplaces[1], $result) . '"';
}
else
return $result;
}
$isList = TRUE;
for($i = 0, reset($result); $i < count($result); $i++, next($result))
{
if(key($result) !== $i)
{
$isList = FALSE;
break;
}
}
$json = array();
if($isList)
{
foreach($result as $value)
$json[] = $this->jsonify($value);
return '[' . join(',', $json) . ']';
}
else
{
foreach($result as $key => $value)
$json[] = $this->jsonify($key) . ':' . $this->jsonify($value);
return '{' . join(',', $json) . '}';
}
}
/**
* returns the sql statement of the last query run
* @return type
*/
public function last_query()
{
return $this->ci->db->last_query();
}
}
/* End of file Datatables.php */
/* Location: ./application/libraries/Datatables.php */Also, if you are to apply filtering on initial load, similar to below, you go to use "iDeferLoading", which will prevent your table being overridden, because fnFilter will probably return result quicker than datatables init.
var otable = $('.table').datatables();
otable.fnFilter('some data', 2, true);Speed wise, i have about 4k rows and it takes 0.85 seconds for MySQL to process the data while it takes about 5second for data to be displayed on the screen. This is on my local machine.
On the server I get results nearly straight away, initial load about 1-2 seconds and hard to notice, which is just what i wanted :)
The only thing that I have noticed is that navigation doesn't function as should, when you click "next"
ghost
commented
9 years ago I have updated the way filter works, so that individual and global search can work together. At the present moment individual column filter supports "=" sign, so you cant filter by data range at the present moment.
<?php if(!defined('BASEPATH')) exit('No direct script access allowed');
/**
* Ignited Datatables
*
* This is a wrapper class/library based on the native Datatables server-side implementation by Allan Jardine
* found at http://datatables.net/examples/data_sources/server_side.html for CodeIgniter
*
* @package CodeIgniter
* @subpackage libraries
* @category library
* @version 2.0 <beta>
* @author Vincent Bambico <metal.conspiracy@gmail.com>
* Yusuf Ozdemir <yusuf@ozdemir.be>
* @link http://ellislab.com/forums/viewthread/160896/
*/
class Datatables
{
/**
* Global container variables for chained argument results
*
*/
private $ci;
private $table;
private $distinct;
private $group_by = array();
private $select = array();
private $joins = array();
private $columns = array();
private $where = array();
private $or_where = array();
private $where_in = array();
private $like = array();
private $filter = array();
private $add_columns = array();
private $edit_columns = array();
private $unset_columns = array();
/**
* Copies an instance of CI
*/
public function __construct()
{
$this->ci =& get_instance();
}
/**
* If you establish multiple databases in config/database.php this will allow you to
* set the database (other than $active_group) - more info: http://ellislab.com/forums/viewthread/145901/#712942
*/
public function set_database($db_name)
{
$db_data = $this->ci->load->database($db_name, TRUE);
$this->ci->db = $db_data;
}
/**
* Generates the SELECT portion of the query
*
* @param string $columns
* @param bool $backtick_protect
* @return mixed
*/
public function select($columns, $backtick_protect = TRUE)
{
foreach($this->explode(',', $columns) as $val)
{
$column = trim(preg_replace('/(.*)\s+as\s+(\w*)/i', '$2', $val));
$column = preg_replace('/.*\.(.*)/i', '$1', $column); // get name after `.`
$this->columns[] = $column;
$this->select[$column] = trim(preg_replace('/(.*)\s+as\s+(\w*)/i', '$1', $val));
}
$this->ci->db->select($columns, $backtick_protect);
return $this;
}
/**
* Generates the DISTINCT portion of the query
*
* @param string $column
* @return mixed
*/
public function distinct($column)
{
$this->distinct = $column;
$this->ci->db->distinct($column);
return $this;
}
/**
* Generates a custom GROUP BY portion of the query
*
* @param string $val
* @return mixed
*/
public function group_by($val)
{
$this->group_by[] = $val;
$this->ci->db->group_by($val);
return $this;
}
/**
* Generates the FROM portion of the query
*
* @param string $table
* @return mixed
*/
public function from($table)
{
$this->table = $table;
return $this;
}
/**
* Generates the JOIN portion of the query
*
* @param string $table
* @param string $fk
* @param string $type
* @return mixed
*/
public function join($table, $fk, $type = NULL)
{
$this->joins[] = array($table, $fk, $type);
$this->ci->db->join($table, $fk, $type);
return $this;
}
/**
* Generates the WHERE portion of the query
*
* @param mixed $key_condition
* @param string $val
* @param bool $backtick_protect
* @return mixed
*/
public function where($key_condition, $val = NULL, $backtick_protect = TRUE)
{
$this->where[] = array($key_condition, $val, $backtick_protect);
$this->ci->db->where($key_condition, $val, $backtick_protect);
return $this;
}
/**
* Generates the WHERE portion of the query
*
* @param mixed $key_condition
* @param string $val
* @param bool $backtick_protect
* @return mixed
*/
public function or_where($key_condition, $val = NULL, $backtick_protect = TRUE)
{
$this->or_where[] = array($key_condition, $val, $backtick_protect);
$this->ci->db->or_where($key_condition, $val, $backtick_protect);
return $this;
}
/**
* Generates the WHERE IN portion of the query
*
* @param mixed $key_condition
* @param string $val
* @param bool $backtick_protect
* @return mixed
*/
public function where_in($key_condition, $val = NULL, $backtick_protect = TRUE)
{
$this->where_in[] = array($key_condition, $val, $backtick_protect);
$this->ci->db->where_in($key_condition, $val, $backtick_protect);
return $this;
}
/**
* Generates the WHERE portion of the query
*
* @param mixed $key_condition
* @param string $val
* @param bool $backtick_protect
* @return mixed
*/
public function filter($key_condition, $val = NULL, $backtick_protect = TRUE)
{
$this->filter[] = array($key_condition, $val, $backtick_protect);
return $this;
}
/**
* Generates a %LIKE% portion of the query
*
* @param mixed $key_condition
* @param string $val
* @param bool $backtick_protect
* @return mixed
*/
public function like($key_condition, $val = NULL, $backtick_protect = TRUE)
{
$this->like[] = array($key_condition, $val, $backtick_protect);
$this->ci->db->like($key_condition, $val, $backtick_protect);
return $this;
}
/**
* Sets additional column variables for adding custom columns
*
* @param string $column
* @param string $content
* @param string $match_replacement
* @return mixed
*/
public function add_column($column, $content, $match_replacement = NULL)
{
$this->add_columns[$column] = array('content' => $content, 'replacement' => $this->explode(',', $match_replacement));
return $this;
}
/**
* Sets additional column variables for editing columns
*
* @param string $column
* @param string $content
* @param string $match_replacement
* @return mixed
*/
public function edit_column($column, $content, $match_replacement)
{
$this->edit_columns[$column][] = array('content' => $content, 'replacement' => $this->explode(',', $match_replacement));
return $this;
}
/**
* Unset column
*
* @param string $column
* @return mixed
*/
public function unset_column($column)
{
$column=explode(',',$column);
$this->unset_columns=array_merge($this->unset_columns,$column);
return $this;
}
/**
* Builds all the necessary query segments and performs the main query based on results set from chained statements
*
* @param string $output
* @param string $charset
* @return string
*/
public function generate($output = 'json', $charset = 'UTF-8')
{
if(strtolower($output) == 'json')
{
$this->get_paging();
}
$this->get_ordering();
$this->get_filtering();
return $this->produce_output(strtolower($output), strtolower($charset));
}
/**
* Generates the LIMIT portion of the query
*
* @return mixed
*/
private function get_paging()
{
$iStart = $this->ci->input->post('iDisplayStart');
$iLength = $this->ci->input->post('iDisplayLength');
if($iLength != '' && $iLength != '-1')
$this->ci->db->limit($iLength, ($iStart)? $iStart : 0);
}
/**
* Generates the ORDER BY portion of the query
*
* @return mixed
*/
private function get_ordering()
{
if($this->ci->input->post('iSortCol_0') || $this->ci->input->post('iSortCol_0') == 0)
{
for($i = 0; $i < intval($this->ci->input->post('iSortingCols')); $i++)
{
if($this->ci->input->post('bSortable_' . intval($this->ci->input->post('iSortCol_' . $i))) == "true")
{
$this->ci->db->order_by($this->ci->input->post('mDataProp_' . $this->ci->input->post('iSortCol_' . $i)), $this->ci->input->post('sSortDir_' . $i));
}
}
}
}
/**
* Generates a %LIKE% portion of the query
*
* @return mixed
*/
private function get_filtering()
{
$sColumns = explode(",", $this->ci->input->post('sColumns'));
if ($this->ci->input->post('sSearch') && $this->ci->input->post('sSearch') != "" )
{
$search_terms = explode("|", $this->ci->input->post('sSearch'));
$ele_i = 0;
$like = "";
for ( $i=0 ; $i < $this->ci->input->post('iColumns'); $i++ )
{
if($this->ci->input->post('bSearchable_' . $i) == "true")
{
foreach($search_terms as $key => $ele)
{
if(strlen($ele) > 0)
{
if($ele_i != 0) $like .= " OR ";
$like .= $sColumns[$i] . " LIKE '%" . $ele . "%'";
$ele_i++;
}
}
}
}
if($like != "") $this->ci->db->where("(" . $like . ")");
}
for ( $i=0 ; $i < $this->ci->input->post('iColumns'); $i++ )
{
if($this->ci->input->post('bSearchable_' . $i) == "true")
{
$search_terms = explode("|", $this->ci->input->post('sSearch_' . $i));
$ele_i = 0;
$where = "";
foreach($search_terms as $key => $ele)
{
if(strlen($ele) > 0)
{
if($ele_i != 0) $where .= " OR ";
$where .= $sColumns[$i] . " = '" . $ele . "'";
$ele_i++;
}
}
if($where != "") $this->ci->db->where("(" . $where . ")");
}
}
}
/**
* Compiles the select statement based on the other functions called and runs the query
*
* @return mixed
*/
private function get_display_result()
{
return $this->ci->db->get($this->table);
}
/**
* Builds an encoded string data. Returns JSON by default, and an array of aaData if output is set to raw.
*
* @param string $output
* @param string $charset
* @return mixed
*/
private function produce_output($output, $charset)
{
$aaData = array();
$rResult = $this->get_display_result();
if($output == 'json')
{
$iTotal = $this->get_total_results();
$iFilteredTotal = $this->get_total_results(TRUE);
}
foreach($rResult->result_array() as $row_key => $row_val)
{
$aaData[$row_key] = ($this->check_cType())? $row_val : array_values($row_val);
foreach($this->add_columns as $field => $val)
if($this->check_cType())
$aaData[$row_key][$field] = $this->exec_replace($val, $aaData[$row_key]);
else
$aaData[$row_key][] = $this->exec_replace($val, $aaData[$row_key]);
foreach($this->edit_columns as $modkey => $modval)
foreach($modval as $val)
$aaData[$row_key][($this->check_cType())? $modkey : array_search($modkey, $this->columns)] = $this->exec_replace($val, $aaData[$row_key]);
$aaData[$row_key] = array_diff_key($aaData[$row_key], ($this->check_cType())? $this->unset_columns : array_intersect($this->columns, $this->unset_columns));
if(!$this->check_cType())
$aaData[$row_key] = array_values($aaData[$row_key]);
}
if($output == 'json')
{
$sOutput = array
(
'draw' => intval($this->ci->input->post('draw')),
'recordsTotal' => $iTotal,
'recordsFiltered' => $iFilteredTotal,
'data' => $aaData
);
if($charset == 'utf-8')
return json_encode($sOutput);
else
return $this->jsonify($sOutput);
}
else
return array('aaData' => $aaData);
}
/**
* Get result count
*
* @return integer
*/
private function get_total_results($filtering = FALSE)
{
if($filtering)
$this->get_filtering();
foreach($this->joins as $val)
$this->ci->db->join($val[0], $val[1], $val[2]);
foreach($this->where as $val)
$this->ci->db->where($val[0], $val[1], $val[2]);
foreach($this->or_where as $val)
$this->ci->db->or_where($val[0], $val[1], $val[2]);
foreach($this->where_in as $val)
$this->ci->db->where_in($val[0], $val[1], $val[2]);
foreach($this->group_by as $val)
$this->ci->db->group_by($val);
foreach($this->like as $val)
$this->ci->db->like($val[0], $val[1], $val[2]);
if(strlen($this->distinct) > 0)
{
$this->ci->db->distinct($this->distinct);
$this->ci->db->select($this->columns);
}
$query = $this->ci->db->get($this->table, NULL, NULL, FALSE);
return $query->num_rows();
}
/**
* Runs callback functions and makes replacements
*
* @param mixed $custom_val
* @param mixed $row_data
* @return string $custom_val['content']
*/
private function exec_replace($custom_val, $row_data)
{
$replace_string = '';
if(isset($custom_val['replacement']) && is_array($custom_val['replacement']))
{
//Added this line because when the replacement has over 10 elements replaced the variable "$1" first by the "$10"
$custom_val['replacement'] = array_reverse($custom_val['replacement'], true);
foreach($custom_val['replacement'] as $key => $val)
{
$sval = preg_replace("/(?<!\w)([\'\"])(.*)\\1(?!\w)/i", '$2', trim($val));
if(preg_match('/(\w+::\w+|\w+)\((.*)\)/i', $val, $matches) && is_callable($matches[1]))
{
$func = $matches[1];
$args = preg_split("/[\s,]*\\\"([^\\\"]+)\\\"[\s,]*|" . "[\s,]*'([^']+)'[\s,]*|" . "[,]+/", $matches[2], 0, PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE);
foreach($args as $args_key => $args_val)
{
$args_val = preg_replace("/(?<!\w)([\'\"])(.*)\\1(?!\w)/i", '$2', trim($args_val));
$args[$args_key] = (in_array($args_val, $this->columns))? ($row_data[($this->check_cType())? $args_val : array_search($args_val, $this->columns)]) : $args_val;
}
$replace_string = call_user_func_array($func, $args);
}
elseif(in_array($sval, $this->columns))
$replace_string = $row_data[($this->check_cType())? $sval : array_search($sval, $this->columns)];
else
$replace_string = $sval;
$custom_val['content'] = str_ireplace('$' . ($key + 1), $replace_string, $custom_val['content']);
}
}
return $custom_val['content'];
}
/**
* Check column type -numeric or column name
*
* @return bool
*/
private function check_cType()
{
$column = $this->ci->input->post('columns');
if(is_numeric($column[0]['data']))
return FALSE;
else
return TRUE;
}
/**
* Return the difference of open and close characters
*
* @param string $str
* @param string $open
* @param string $close
* @return string $retval
*/
private function balanceChars($str, $open, $close)
{
$openCount = substr_count($str, $open);
$closeCount = substr_count($str, $close);
$retval = $openCount - $closeCount;
return $retval;
}
/**
* Explode, but ignore delimiter until closing characters are found
*
* @param string $delimiter
* @param string $str
* @param string $open
* @param string $close
* @return mixed $retval
*/
private function explode($delimiter, $str, $open = '(', $close=')')
{
$retval = array();
$hold = array();
$balance = 0;
$parts = explode($delimiter, $str);
foreach($parts as $part)
{
$hold[] = $part;
$balance += $this->balanceChars($part, $open, $close);
if($balance < 1)
{
$retval[] = implode($delimiter, $hold);
$hold = array();
$balance = 0;
}
}
if(count($hold) > 0)
$retval[] = implode($delimiter, $hold);
return $retval;
}
/**
* Workaround for json_encode's UTF-8 encoding if a different charset needs to be used
*
* @param mixed $result
* @return string
*/
private function jsonify($result = FALSE)
{
if(is_null($result))
return 'null';
if($result === FALSE)
return 'false';
if($result === TRUE)
return 'true';
if(is_scalar($result))
{
if(is_float($result))
return floatval(str_replace(',', '.', strval($result)));
if(is_string($result))
{
static $jsonReplaces = array(array('\\', '/', '\n', '\t', '\r', '\b', '\f', '"'), array('\\\\', '\\/', '\\n', '\\t', '\\r', '\\b', '\\f', '\"'));
return '"' . str_replace($jsonReplaces[0], $jsonReplaces[1], $result) . '"';
}
else
return $result;
}
$isList = TRUE;
for($i = 0, reset($result); $i < count($result); $i++, next($result))
{
if(key($result) !== $i)
{
$isList = FALSE;
break;
}
}
$json = array();
if($isList)
{
foreach($result as $value)
$json[] = $this->jsonify($value);
return '[' . join(',', $json) . ']';
}
else
{
foreach($result as $key => $value)
$json[] = $this->jsonify($key) . ':' . $this->jsonify($value);
return '{' . join(',', $json) . '}';
}
}
/**
* returns the sql statement of the last query run
* @return type
*/
public function last_query()
{
return $this->ci->db->last_query();
}
}
/* End of file Datatables.php */
/* Location: ./application/libraries/Datatables.php */Hello Vladislav,
First of all, sorry for disturbing…
And I know it’s not the purpose of giving support on Github code, but, I really hope you are willing to give me a hand…
For a very long time, I’m struggling with “general” and “column” searching in combination with ignited tables… (CodeIgniter 3 / HMVC / JSON) and 100,000+ records…
As far as I remember, it worked fine with CI2, but nowadays , and I can’t find what I’m doing wrong …
I tried to make my example as minimalistic as possible
I have 2 tables …
As you can see, both tables are having a field named “name” …
The LAYOUT I would like to have, can be found on http://www.3sn.info/persons/show_old
Via the topsearch, I would like to perform an “overall search” (so, on all fields shown in the table)
Via the searchboxes on top of each column… I would like to “detail” my filtering.
What I could achieve till now, can be found on http://www.3sn.info/persons/show_new (just my general table is shown, though with NO data at all …)
I’ve almost literally copied your example code…
When I’m debugging, I get an message “TypeError: Cannot read property 'fnInit' of undefined”
(Am I including the wrong .js files ? I really don’t know … I’m not a specialist on this matter…)
I used your latest library (found on Github in this post)
If needed, I can send you my code, and the database data …
PS.: I don’t have any problem with paying a contribution for a solution !
Will you please give me a reply ?
Thanks in advance,
Johnny Driesen
Info at 3SN dot BE
ghost
commented
9 years ago Hi Johnny,
If you could send me your mock up code than I can have a quick look?
my email is hello@web-assistants.co.uk
Vlad
jdriesen
commented
9 years ago Hi Vlad,
Thanks for your reply, and the fact you’re willing to help. I really appreciate this.
Certainly no offense, though I’ve to say I gave up on using ignited tables. I’m currently using a library written by Paul Zepernick https://github.com/zepernick/Codeigniter-DataTables
I’m currently loading 300,000+ records at an incredibable speed. See http://www.3sn.info/fakenames for a little demo …
Regards,
Johnny
ghost
commented
9 years ago Hi Johnny,
Sure, no worries. I might a look at this repository myself...
I had a look at your demo and information loads up very quickly. The Global Search doesn't seem to work on it.
Vlad.
jdriesen
commented
9 years ago Hi Vlad,
Indeed …
Global Search WAS working, but not anymore …
Messed up smt (just to keep myself busy J )
Will give you a sign when evrt is working again…
Grtz, Johnny
tiegosilveira
commented
9 years ago problem You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIKE '%conet%' OR LIKE '%conet%') ORDER BY CID_ID asc LIMIT 10' at line 3
SELECT CID_ID, CID_NOME FROM (CIDADEX) WHERE ( LIKE '%conet%' OR LIKE '%conet%') ORDER BY CID_ID asc LIMIT 10??? what should I do with this error
ghost
commented
9 years ago Try running this sql statement inside mysql, something below, so that each statement is on the new line:
SELECT CID_ID, CID_NOME FROM (CIDADEX) WHERE ( LIKE '%conet%' OR LIKE '%conet%') ORDER BY CID_ID asc LIMIT 10
Also, is this issue with the code that I have provided in this post?
tiegosilveira
commented
9 years ago when using the search field
answer erro #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIKE '%conet%' OR LIKE '%conet%') ORDER BY CID_ID asc LIMIT 10' at line 1
mysql 5.1.73-1 ci:2.0 //code.jquery.com/jquery-1.11.1.min.js //cdn.datatables.net/1.10.4/css/jquery.dataTables.css //cdn.datatables.net/1.10.4/js/jquery.dataTables.min.js
It worked well* SELECT CID_ID, CID_NOME FROM (CIDADEX) WHERE ('CID_ID' LIKE '%conet%' OR 'CID_ID' LIKE '%conet%') ORDER BY CID_ID asc LIMIT 10
I'm using you spent
tiegosilveira
commented
9 years ago A Help please?? the sql is
$this->datatables->select('CID_ID,CID_NOME,CID_EST_ID')
->unset_column('CID_ID')
->from('CIDADEX');
echo $this->datatables->generate("json");
the problem seems to be in
private function get_filtering()
line * 333
tiegosilveira
commented
9 years ago resolved, the error was
This missing this part
"columns": [
{ "name": "CIDADEX.CID_ID","data": "CID_ID" },
{ "name": "CIDADEX.CID_NOME","data": "CID_NOME" },
],
I have noticed that library can generate the table but cant handle filtering, sorting, pagination or search.
I have realised that library needs GET rather than POST which I have in javascript, but having GET rather than POST didn't really help at all.
Due to that I have amend functions in the library, listed below:
So, I have amended library with amendments by me and got this library to work as should. I understand that quite few of you guys are using this library so I am posting my code at the end for you to have a look.
I have following javascript that kickstarts everything
PHP in welcome/datatable:
Amended library code: