Error using Between with codeigniter

Question

Error using Between with codeigniter

Asked 5 years, 1 month ago

Viewed 33 times

0

How to create a function

SELECT 
    ii.sku,
    ii.cod_setor,
    ia.setor_ini,
    ia.setor_fim,
    sum(ii.quantidade) as quantidades,
    ia.desc_ambiente,
    ii.cod_coletor
FROM 
    item_inventarios ii
inner join inventario_ambientes ia on ii.id_inventario = ia.id_inventario
    and ii.cod_setor between setor_ini and setor_fim
WHERE 
    ii.id_inventario = '46' 
Group by ii.sku    
order by 1

I tried to do it this way and it went wrong

Error message

between right in the codeigniter is to be used within the Where, so you were facing problems

– Otto

2019/08/01 at 19:43
Perfect, that way it works. However, this process is very slow .. I’m doing something wrong.?

– Hugo Rutemberg

2019/08/02 at 13:43
Check that the filters are correct and clear ... the table indexes

– Otto

2019/08/02 at 13:51
Filters and indexes are ok and yet mdemora records (11269 total, The consultation took 31.9350 seconds.)



SQL:

SELECT ii.cod_setor, ii.sku, sum(ii.quantidade) as quantidades, ia.desc_ambiente, ii.cod_coletor FROM item_inventarios ii inner join inventario_ambientes ia on ii.id_inventario = ia.id_inventario and ii.cod_setor between ia.setor_ini and ia.setor_fim WHERE ii.id_inventario = '117' Group by ii.cod_setor, ii.sku, ia.desc_ambiente, ii.cod_coletor order by 1

– Hugo Rutemberg

2019/08/02 at 14:22
performs an explain in the direct query in the database that you discover pq this slow

– Otto

2019/08/02 at 15:19
I received this reply: id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1
SIMPLE
ia
ALL
NULL
NULL
NULL
NULL
14528
Using temporary; Using filesort
1
SIMPLE
ii
ALL
NULL
NULL
NULL
NULL
246291
Using where; Using join buffer (flat, BNL join)

– Hugo Rutemberg

2019/08/02 at 15:22
Chief, come on it’s case for another post, so make it easy, we’re mixing a lack of knowledge of Codeigniter with poorly formed or slow sql problem

– Otto

2019/08/02 at 18:53
All right. Obg.

– Hugo Rutemberg

2019/08/05 at 16:23

Show 3 more comments

1 answer

Browser other questions tagged php sql codeigniter

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

by Hugo Rutemberg • **307** points · Answer 1 · 2019-05-24T21:54:44+00:00

Problem solved with the code below;

public function listar_arquivo_saida($id)
    {
        return $this->db->select("ii.sku,ii.cod_setor,ia.setor_ini,ia.setor_fim,sum(ii.quantidade) as quantidades,ia.desc_ambiente,ii.cod_coletor")
              ->join("inventario_ambientes ia", "ii.id_inventario = ia.id_inventario
    and ii.cod_setor between setor_ini and setor_fim")
              ->group_by("ii.sku")
              ->order_by("ii.sku")
              ->get_where("item_inventarios ii", array(
                "ii.id_inventario"=> $id
               ))->result_array();

    }