jeudi 26 avril 2018

Laravel query builder 'where like' not work, but work in sql console

i have the next code in laravel 5

DB::connection($random)
    ->table('STATSTOCK AS ST')
    ->select('ST.KOPR', 'ST.NOKOPR', 'ST.STFI2', 'ST.RLUD', 'T.ECUACIONU2')
    ->join('TABPRE AS T', 'ST.KOPR', '=', 'T.KOPR')
    ->join('MAEPR AS M', 'ST.KOPR', '=', 'M.KOPR')
    ->where(function($query) use ($data){
        $query->where('ST.STFI2', '>', 2);
        $query->whereNotIn('M.BLOQUEAPR', ['T','V']);

        if(isset($data['lista']) && $data['lista']){
            $query->where("T.KOLT", '=', $data['lista']);
        }

        if(isset($data['sucursal']) && $data['sucursal']){
            $query->where("ST.KOSU", '=', $data['sucursal']);
        }

        if(isset($data['buscado']) && $data['buscado'] != ''){
            $buscado = strtoupper($data['buscado']);
            $buscado = urlencode ("%". $buscado. "%");
            $query->where(function($subq) use ($buscado){
                $subq->where('ST.KOPR', 'LIKE', $buscado);
                $subq->orWhere('ST.NOKOPR', 'LIKE', $buscado);
            });
        }

        if(isset($data['codigo']) && $data['codigo']){
            $query->where('ST.KOPR', '=', $data['codigo']);
        }

        $query->where(function($subq){
            $subq->where('ST.KOPR', 'NOT LIKE', '99%');
            $subq->orWhere('ST.KOPR', 'NOT LIKE', '98%');
            $subq->orWhere('ST.KOPR', 'NOT LIKE', 'G%');
            $subq->orWhere('ST.KOPR', 'NOT LIKE', 'A%');
            $subq->orWhere('ST.KOPR', 'NOT LIKE', 'V%');
        });
    })
    ->orderBy('ST.KOPR', 'ASC')
    ->get();

the generated query is

select 
[ST].[KOPR], 
[ST].[NOKOPR], 
[ST].[STFI2], 
[ST].[RLUD], 
[T].[ECUACIONU2] 
from [STATSTOCK] as [ST] 
inner join [TABPRE] as [T] on [ST].[KOPR] = [T].[KOPR] 
inner join [MAEPR] as [M] on [ST].[KOPR] = [M].[KOPR] 
where (
[ST].[STFI2] > 2 
and [M].[BLOQUEAPR] not in ('T', 'V') 
and [T].[KOLT] = 'P09' 
and [ST].[KOSU] = '001' 
and ([ST].[KOPR] LIKE '%ALP%' or [ST].[NOKOPR] LIKE '%ALP%') 
and ([ST].[KOPR] NOT LIKE '99%' 
    or [ST].[KOPR] NOT LIKE '98%' 
    or [ST].[KOPR] NOT LIKE 'G%' 
    or [ST].[KOPR] NOT LIKE 'A%' 
    or [ST].[KOPR] NOT LIKE 'V%')
) 
order by [ST].[KOPR] asc

In mssql console return 2 records, but in laravel return 0 records. If I remove this section, query return all records correctly in mssql console and laravel, but i need this filter

if(isset($data['buscado']) && $data['buscado'] != ''){
        $buscado = strtoupper($data['buscado']);
        $buscado = urlencode ("%". $buscado. "%");
        $query->where(function($subq) use ($buscado){
            $subq->where('ST.KOPR', 'LIKE', $buscado);
            $subq->orWhere('ST.NOKOPR', 'LIKE', $buscado);
        });
    }

Any idea of ​​what is happening?

Any idea of ​​what is happening?

Any idea of ​​what is happening?

Any idea of ​​what is happening?

Any idea of ​​what is happening?

Any idea of ​​what is happening?

Any idea of ​​what is happening?

Any idea of ​​what is happening?

(sorry for repeating, but the page says I need more text or less code)

thanks.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire