Problem in Query Builder Laravel

Question

Problem in Query Builder Laravel

Navigation

#1 by (1 votes)

1

I am in doubt about a query with joins in my project, when running Select directly in the database I have no problems, but when building with Query Builder, I do not get results. Where would he be missing out on her set up?

Normal Query:

SELECT EmpresaCNAE.*, empresa.*, ServicoValor.* FROM EmpresaCNAE 
INNER JOIN empresa on empresa.EmpresaId=EmpresaCNAE.EmpresaId
INNER JOIN ServicoValor on ServicoValor.ServicoId=EmpresaCNAE.CNAEId
WHERE EmpresaCNAE.CNAEId=4721103 and ServicoValor.EmpresaId=EmpresaCNAE.EmpresaId

Mounted Query:

$Dados = DB::table('EmpresaCNAE')
        ->join('empresa', 'empresa.EmpresaId', '=', 'EmpresaCNAE.EmpresaId')
        ->join('ServicoValor', 'ServicoValor.ServicoId', '=', 'EmpresaCNAE.CNAEId')
        ->where([['EmpresaCNAE.CNAEId', $id->id],['ServicoValor.EmpresaId', 'EmpresaCNAE.EmpresaId']])
        ->select('EmpresaCnae.*', 'empresa.*', 'ServicoValor.*')
        ->get();

$ id-> id contains the 4721103, I did all tests with the same id

Running with toSql () I get the following:

select 'EmpresaCnae'.*, 'empresa'.*, 'ServicoValor'.* from 'EmpresaCNAE' inner join 'empresa' on 'empresa'.'EmpresaId' = 'EmpresaCNAE'.'EmpresaId' inner join 'ServicoValor' on 'ServicoValor'.'ServicoId' = 'EmpresaCNAE'.'CNAEId' where ('EmpresaCNAE'.'CNAEId' = ? and 'ServicoValor'.'EmpresaId' = ?)

I tested another form of query but with no results either:

$Dados = DB::table('EmpresaCNAE')->select('EmpresaCnae.*', 'empresa.*', 'ServicoValor.*')
->where('EmpresaCNAE.CNAEId', '=', $id->id)->join('empresa', function($join){
$join->on('empresa.EmpresaId', '=', 'EmpresaCNAE.EmpresaId');
})->join('ServicoValor', function($join){
$join->on('ServicoValor.ServicoId', '=', 'EmpresaCNAE.CNAEId');
})->where('ServicoValor.EmpresaId', '=', 'EmpresaCNAE.EmpresaId')->toSql();

Result of toSql of this last form:

select 'EmpresaCnae'.*, 'empresa'.*, 'ServicoValor'.* from 'EmpresaCNAE' inner join 'empresa' on 'empresa'.'EmpresaId' = 'EmpresaCNAE'.'EmpresaId' inner join 'ServicoValor' on 'ServicoValor'.'ServicoId' = 'EmpresaCNAE'.'CNAEId' where 'EmpresaCNAE'.'CNAEId' = ? and 'ServicoValor'.'EmpresaId' = ?

sql query laravel

asked by anonymous 04.01.2018 / 01:34

1 answer

Relate 4 bank tables and perform accounts How to use touch event on iPad with ui-draggable and ui-resizable?

score 1 · Accepted Answer

I was able to resolve it using the following form:

 $Dados = DB::select('SELECT EmpresaCNAE.*, empresa.*, ServicoValor.* FROM EmpresaCNAE 
       INNER JOIN empresa on empresa.EmpresaId=EmpresaCNAE.EmpresaId
       INNER JOIN ServicoValor on ServicoValor.ServicoId=EmpresaCNAE.CNAEId
       WHERE EmpresaCNAE.CNAEId = ? and ServicoValor.EmpresaId = EmpresaCNAE.EmpresaId', [$id->id]);

And an alternate form that also worked:

 $Dados = DB::table('EmpresaCNAE')
        ->join('empresa', 'empresa.EmpresaId', '=', 'EmpresaCNAE.EmpresaId')
        ->join('ServicoValor', 'ServicoValor.ServicoId', '=', 'EmpresaCNAE.CNAEId')
        ->where([['EmpresaCNAE.CNAEId', $id->id]])
        ->whereColumn([['ServicoValor.EmpresaId', 'EmpresaCNAE.EmpresaId']])
        ->select('EmpresaCNAE.*', 'empresa.*', 'ServicoValor.*')
        ->get();