DATATABLE CODEIGNITER - INNER JOIN QUERY

Question

Hello!

I’m using dataTable in codeigniter and performing a query through the field search

Seeing the query below, it works well when it comes to query within the same table tb_pagamento.

Now I want to query the operator name in this same query. However, in the table tb_pagamento I only have the operator ID (operador_id), where the operator name is in the table tb_operador

tb_operator
'id'
'name'

See the consultation:

var $tabela      = 'tb_pagamento';
var $tb_operador = 'tb_operador';

var $column_order = array('id', 'empresa', null);
var $column_search = array('id', 'empresa', 'nome_cliente', 'dt_pagamento', 'valor_pagamento', 'operador_id');
var $order = array('id' => 'asc');

private function get_query() {
    $this->db->from($this->tabela);
    $i = 0;
    foreach ($this->column_search as $item) {
        if($_POST['search']['value']) {
            if($i === 0) {
                $this->db->group_start();
                $this->db->like($item, $_POST['search']['value']);
            } else {
                $this->db->or_like($item, $_POST['search']['value']);
            }
            if(count($this->column_search) - 1 == $i){
                $this->db->group_end();
            }
        }
        $i++;
    }

    if(isset($_POST['order'])) {
        $this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
    } else if(isset($this->order)) {
        $order = $this->order;
        $this->db->order_by(key($order), $order[key($order)]);
    }
}

Answer 1

Do it this way here in your Model class, I’ve had this problem and solved it with this.

private function _get_datatables_query($term = '') {

    $column = array('p.nome', 'p.cargo', 'f.datap', 'f.datav', 'f.status', 'f.fatura_id');
    $this->db->select('*');
    $this->db->from('pessoa as c');
    $this->db->where('f.pessoa_id = p.id');
    $this->db->join('fatura as f', 'f.pessoa_id  = p.id', 'left');
    $this->db->like('p.nome', $term);
    $this->db->or_like('f.datap', $term);
    $this->db->or_like('f.datav', $term);
    $this->db->or_like('f.status', $term);


    if (isset($_REQUEST['order'])) { // here order processing
        $this->db->order_by($column[$_REQUEST['order']['0']['column']], $_REQUEST['order']['0']['dir']);
    } else if (isset($this->order)) {
        $order = $this->order;
        $this->db->order_by(key($order), $order[key($order)]);
    }
}

function get_datatables() {
    $term = $_REQUEST['search']['value'];
    $this->_get_datatables_query($term);
    if ($_REQUEST['length'] != -1)
        $this->db->limit($_REQUEST['length'], $_REQUEST['start']);
    $query = $this->db->get();
    return $query->result();
}

function count_filtered() {
    $term = $_REQUEST['search']['value'];
    $this->_get_datatables_query($term);
    $query = $this->db->get();
    return $query->num_rows();
}

public function count_all() {
    $this->db->from($this->table);
    return $this->db->count_all_results();
}