Problem in Query Builder Laravel

Asked

Viewed 153 times

2

I am in doubt in 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 get no results. Where would I be missing from her montage?

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 without results too:

$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();

toSql result this last way:

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 laravel query

  • after select() put like this select('EmpresaCnae.*', 'empresa.*', 'ServicoValor.*')->toSql() will get SQL return check if it hit with normal?

  • Use the method toSql() to return the query that Laravel is mounting. So you can compare better.

  • I edited the question @Valdeirpsr

1 answer

1


I managed to solve using the following way:

 $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 alternative way 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();

Browser other questions tagged sql laravel query

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