DataTable with Ajax not working after using serverSide: true

Question

I have a user table using DataTables that will contain more than 200 rows. It looks so great when I use the default DataTables "pageLength": 10 , and this is an example table.

  Username | Type | Request | user01 1 request01 user02 1 request02 user03 2 request03 user04 1 request04 user05 1 request05 user06 1 request06 user07 1 request07 user08 1 request08 user09 1 request09 user10 1 request10 

Showing 1 to 10 of 200 entries First Previous 1 2 3 ... 20 Next Last

So, to reduce load time, I decide to use "processing": true and "serverSide": true . Then I got some problems with this "serverSide" : true , It prints 200 rows of data in a table.

Showing 0 to 0 of 0 entries (filtered from NaN total entries) . Then the pagination still prints, and after I click on page 2 , it does nothing.

I do not want DataTables to receive 10 data for the first, after clicking page 2 , it will get another 10, etc.

I am using CodeIgniter, here is my code:

On my Views + Js :

 <select name="task" id="task"> <option value="1">Task 1</option> <option value="2">Task 2</option> </select> <table id="user-request" class="table"> <thead> <tr> <th>Username</th> <th>Type</th> <th>Request</th> </tr> </thead> </table> <script> ... on task change ... ... var task = $("#task").val(); ... $('#user-request').DataTable({ 'processing': true, 'serverSide': true, 'ajax': { 'type': 'POST', 'url': base_url+'user/get_user_request', 'data': {"task":task,"csrf_token":$("input[name=csrf_token]").val()} } }) </script> 

Note : A task is another group, for example, class 1 or class 2, Orchard University or Harvard University.

On my controller :

 $task = $this->input->post('task', TRUE); $user_request = $this->model->all_user_request(task); foreach ($user_request as $ur) { $arr = array(); $arr[] = $ur->username; $arr[] = $ur->type; $arr[] = $ur->request; $data[] = $arr; } $output = array( "data" => $data ); if (COUNT($output) > 0) { echo json_encode($output); } 

On my Model :

 public function all_user_request($task_id) { $query = "SELECT * FROM user_request WHERE task_id = ?"; return $this->db->query($query, $task_id)->result(); } 

Note The model actually uses 2 INNER JOIN , I just simplify the selection just for the query here. (turning into a denormalization table only here).

I tried to add draw , recordsTotal , recordsFiltered to $output in my controller, just using numeric data. Example

 $output = array( "draw" => 5, "recordsTotal" => 5, "recordsFiltered" => 5, "data" => $data ); if (COUNT($output) > 0) { echo json_encode($output); } 

I was looking for an answer, but I think the problem is here, but I still don't know where I should get the data draw - recordsTotal - recordsFiltered . I see a different answer from others, they use "draw" => $_POST['draw'] , then I tried it and it does nothing.

So I'm trying to use numerical data, but the result is still the same. I need help. It still prints 200 rows of data in a table.

Showing 0 to 0 of 0 entries (filtered from NaN total entries) . Then the pagination still prints, and after I click on page 2 , it does nothing.

Answer 1

Datatables send everything you need - if you look at your console on the network, you will see that they use the ajax-get method to send these requests to the server

GET parameter is as follows

 draw columns start length search 

You can find the whole list here.

which means you have to adapt your model correctly ...

something like this should work

 public function all_user_request($task_id) { $intStart = intval($this->input->get("start")); $intLength = intval($this->input->get("length")); $strSearch = (strlen($this->input->get("search")["value"]) >= 2) ? $this->input->get("search",true)["value"] : false; $order = $this->input->get("order",true); $this->setQuery($task_id,$strSearch); $query = $this->db->get(); $this->recordsTotal = $query->num_rows(); $this->setQuery($task_id, $strSearch); if ($intStart >= 0 && $intLength > 0) { $this->db->limit($intLength,$intStart); } $strOrderField = 'username'; $strDirection = "ASC"; if (is_array($order)) { switch($order[0]['column']) { case 1: $strOrderField = 'type'; break; case 2: $strOrderField = 'request'; break; } if (!empty($order[0]['dir'])) $strDirection = $order[0]['dir']; } $this->db->order_by($strOrderField,$strDirection); $query = $this->db->get(); $arrData = $query->result(); return $arrData; } public function getRecordsTotal() { return $this->recordsTotal; } private function setQuery($task_id, $strSearch="") { $this->db ->select('*') ->from('user_request') ->where('task_id', $task_id); if (!empty($strSearch)) { $this->db->like('task_id', $strSearch); } } 

and your controller

 //controller $task = $this->input->post('task', TRUE); $user_request = $this->model->all_user_request($task); $data = []; foreach ($user_request as $ur) { $data[] = [ $ur->username, $ur->type, $ur->request ]; } $arrCompiledData = [ 'data' => $data, 'draw' => $this->input->get('draw'), 'recordsTotal' => $this->model->getRecordsTotal(), 'recordsFiltered' => $this->model->getRecordsTotal(), ]; $this->output ->set_content_type('application/json') ->set_output(json_encode($arrCompiledData)); 

Please keep in mind that I just wrote this - maybe there are some typos, but you should understand how the datatables server data processing on the server should work.

Answer 2

While you select server mode, you need to manage everything through requests. Thus, you must dynamically create the values ​​of the output array:

 $output = array( "draw" => $_POST['draw'], "recordsTotal" => $this->my_model->get_total_records(), "recordsFiltered" => $this->my_model->get_total_filtered(), "data" => $this->my_model->all_user_request($id) ); 

and model functions

 public function all_user_request($task_id) { $query = "SELECT * FROM user_request WHERE task_id = ?"; // add limit $_POST['length'], $_POST['start'] to your request return $this->db->query($query, $task_id)->result(); } 

Answer 3

If you use serverSide = true , you must specify your own filter counter and total counter. Also provide your own search function, order, etc. Use the controller and model below for reference.

controller

 $task = $this->input->post('task', TRUE); $user_request = $this->model->all_user_request($task); $output = array( 'draw' => $this->input->post('draw', TRUE), 'recordsTotal' => $user_request['recordsTotal'], 'recordsFiltered => $user_request['recordsFiltered'], 'data' => empty($user_request['data'])? array() : $user_request['data'] ); echo json_encode($output); 

Model

 public function all_user_request($task_id) { $params = $this->input->post(null, TRUE); $search_fields = array('username','type','request'); //change this into your table fields $data = array(); $this->db->start_cache(); $this->db->select("username, type, request"); $this->db->from("user_request"); $this->db->where("task_id", $task_id); if(!empty($params['search']['value'])){ $str = $params['search']['value']; $this->db->group_start(); foreach($search_fields as $row){ $this->db->or_like($row, $str, 'BOTH'); } $this->db->group_end(); } $data['recordsTotal'] = $this->db->count_all_results(); $this->db->stop_cache(); $this->db->limit($params['length'], $params['start']); $data['recordsFiltered'] = $this->db->count_all_results(); $query = $this->db->get(); $this->db->flush_cache(); foreach($query->result_array() as $row){ $data['data'][] = array_values($row); } return $data; }