Inner Join with subselect codeigniter

Asked

Viewed 218 times

0

How to create a function that brings the same query result below using codeigniter? 

SELECT AC.cod_setor,AC.cod_coletor ,SUM(ac.quantidade) AS quantidade 
FROM item_inventarios AC 
INNER JOIN ( SELECT cod_setor , SUM(quantidade) AS quantidade FROM item_inventarios GROUP BY cod_setor HAVING COUNT(distinct cod_setor + cod_coletor) > 1 ) AC2 ON AC2.cod_setor = AC.cod_setor 
where `id_inventario`='37' 
GROUP BY AC.cod_setor , AC.cod_coletor
php sql codeigniter

  • wouldn’t be because the \id_inventario`` is in quotes (and is considered a string)?

  • rLinhares, in the example I posted this way, but in the codeigniter it will look like this; ->get_where("item_inventarios", array( "id_inventario"=> $id ))->result_array();

2 answers

1

Hello! Instead of writing the table name in the first parameter of join() you need to write your SELECT monster that produces the table for the Join. Too bad that writing so much can generate an incompatibility if switching bank. The instruction inner is last in the parameters.

$this->db->select('AC.cod_setor, AC.cod_coletor, SUM(ac.quantidade) AS quantidade');
$this->db->from('item_inventarios AC');
$this->db->join('(SELECT cod_setor, SUM(quantidade) AS quantidade FROM item_inventarios GROUP BY cod_setor HAVING COUNT(distinct cod_setor + cod_coletor) > 1 )', 'AC2.cod_setor = AC.cod_setor', 'inner');
$this->db->where('id_inventario', 37);
$this->db->group_by('AC.cod_setor , AC.cod_coletor');
$query = $this->db->get();

To debug if your SQL command has been built perfectly, use: $this->db->last_query();

  • 1

    And what would that part look like? Where id_inventario='37' GROUP BY AC.cod_sector , AC.cod_collector

  • I forgot to include. I incremented.

0


I managed to solve with the code below;

  //campos do select
        $this->db->select('AC.cod_setor, AC.cod_coletor, SUM(ac.quantidade) AS quantidade');
        // tabela from
        $this->db->from('item_inventarios AC');
        // join
        $this->db->join('(SELECT cod_setor, SUM(quantidade) AS quantidade FROM item_inventarios GROUP BY cod_setor HAVING COUNT(distinct cod_setor + cod_coletor) > 1 ) AC2'
                , 'AC2.cod_setor = AC.cod_setor', 'inner');

        $where['id_inventario'] = $id;
        //where
        $this->db->where(array('id_inventario' => $id));

        //group by
        $this->db->group_by('AC.cod_setor , AC.cod_coletor');

        // executa a query e retorna um vetor de resultados
        $resultado_query = $this->db->get('')->result_array();
        echo $this->db->last_query(); //COMENTAR PARA NÃO MOSTRAR O SELECT
        return $resultado_query;

Browser other questions tagged php sql codeigniter

You are not signed in. Login or sign up in order to post.