Model:
public function get_adjustment($group_id, $start, $length, $search, $order_by, $order_dir)
{
$this->db->select('uni.inv_id, batch.batch_id, uni.item_code, des.design_name, cat.cat_type, supply.item_size, des.gender_status, uni.quantity, batch.b_remain_qty, batch.batch_code');
$this->db->from('ebs.uni_inventory uni');
$this->db->join('ebs.uni_batch batch', 'batch.group_id = uni.group_id AND batch.item_code = uni.item_code', 'inner');
$this->db->join('ebs.uni_stocksupply_v2 supply', 'supply.item_type = uni.item_type AND supply.item_design = uni.item_design AND supply.item_code = uni.item_code AND supply.item_code = batch.item_code', 'inner');
$this->db->join('ebs.uni_stockdesign_v2 des', 'des.design_type = uni.item_type AND des.design_id = uni.item_design', 'inner');
$this->db->join('ebs.uni_stockcategory_v2 cat', 'cat.stock_id = uni.item_type', 'inner');
$this->db->where('uni.status', 'active');
$this->db->where('uni.group_id', $group_id);
$this->db->order_by($order_by, $order_dir);
$this->db->limit($length, $start);
if (!empty($search)) {
$this->db->group_start();
$this->db->like('uni.item_code', $search);
$this->db->or_like('des.design_name', $search);
$this->db->or_like('cat.cat_type', $search);
$this->db->or_like('supply.item_size', $search);
$this->db->or_like('des.gender_status', $search);
$this->db->or_like('uni.quantity', $search);
$this->db->or_like('batch.b_remain_qty', $search);
$this->db->or_like('batch.batch_code', $search);
$this->db->group_end();
}
$result = $this->db->get();
return $result->result_array();
}
public function getTotalRecords($group_id, $search)
{
$this->db->select('*');
$this->db->from('ebs.uni_inventory uni');
$this->db->join('ebs.uni_batch batch', 'batch.group_id = uni.group_id AND batch.item_code = uni.item_code', 'inner');
$this->db->join('ebs.uni_stocksupply_v2 supply', 'supply.item_type = uni.item_type AND supply.item_design = uni.item_design AND supply.item_code = uni.item_code AND supply.item_code = batch.item_code', 'inner');
$this->db->join('ebs.uni_stockdesign_v2 des', 'des.design_type = uni.item_type AND des.design_id = uni.item_design', 'inner');
$this->db->join('ebs.uni_stockcategory_v2 cat', 'cat.stock_id = uni.item_type', 'inner');
$this->db->where('uni.status', 'active');
$this->db->where('uni.group_id', $group_id);
// $this->db->where('uni.quantity != batch.b_remain_qty');
if (!empty($search)) {
$this->db->group_start();
$this->db->like('uni.item_code', $search);
$this->db->or_like('des.design_name', $search);
$this->db->or_like('cat.cat_type', $search);
$this->db->or_like('supply.item_size', $search);
$this->db->or_like('des.gender_status', $search);
$this->db->or_like('uni.quantity', $search);
$this->db->or_like('batch.b_remain_qty', $search);
$this->db->or_like('batch.batch_code', $search);
$this->db->group_end();
}
// return $this->db->get()->row()->total_records;
$query = $this->db->get();
return count($query->result_array());
}...............
}
Controller:
public function get_inventory_data()
{
$group_id = $this->input->post('group_id');
$start = $this->input->post('start');
$length = $this->input->post('length');
$search = $this->input->post('search')['value'];
// $column = $this->input->post('order')[0]['column'];
// $order_dir = $this->input->post('order')[0]['dir'];
// $columns = ['inv_id', 'batch_id', 'item_code', 'design_name', 'cat_type', 'item_size', 'gender_status', 'quantity', 'b_remain_qty'];
// $order_by = $columns[$column];
$order_by = $this->input->post('columns')[$this->input->post('order')[0]['column']]['data'];
$order_dir = $this->input->post('order')[0]['dir'];
$columns = ['inv_id', 'batch_id', 'item_code', 'design_name', 'cat_type', 'item_size', 'gender_status', 'quantity', 'b_remain_qty'];
if (empty($order_by) || !in_array($order_by, $columns)) {
$order_by = $columns[0];
}
$data = $this->AdjustmentModel->get_adjustment($group_id, $start, $length, $search, $order_by, $order_dir);
$totalRecords = $this->AdjustmentModel->getTotalRecords($group_id, $search);
$data_output = array(
'draw' => intval($this->input->post('draw')),
'recordsTotal' => intval($totalRecords),
'recordsFiltered' => intval($totalRecords),
'data' => $data,
);
echo json_encode($data_output);
}
I Want to get the data fast and load data when search fast.......helpp