Problems with Laravel Query Builder

Asked

Viewed 345 times

-1

I have the following problem, when trying to implement a consultation with joins, in query Builder:

My Query (pure SQL):

SELECT COUNT(*) as total, 
orgao.sigla AS sigla_org_uni 
FROM protocolo 
INNER JOIN procedimento p ON protocolo.id_protocolo = p.id_procedimento 
INNER JOIN unidade uni_ger 
INNER JOIN orgao ON uni_ger.id_orgao = orgao.id_orgao 
ON protocolo.id_unidade_geradora = uni_ger.id_unidade 
WHERE protocolo.sta_protocolo='P' 
AND protocolo.sta_nivel_acesso_global<>'2' 
GROUP BY uni_ger.id_orgao, orgao.sigla 
ORDER BY orgao.sigla ASC

My query with Query Builder:

DB::select(`orgao.sigla as sigla_org_uni`)
         ->addSelect(DB::raw(`COUNT(*) as total`))
    ->from(`protocolo`)
    ->join(`procedimento as p`, function($join) {
        $join->on(`protocolo.id_protocolo`, `=`, `p.id_procedimento`);
        })
    ->from(`unidade as uni_ger`)
    ->join(`orgao`, function($join) {
        $join->on(`protocolo.id_unidade_geradora`, `=`, `uni_ger.id_unidade`);
        })
    ->where(`protocolo.sta_protocolo`, `=`, `P`)
    ->where(`protocolo.sta_nivel_acesso_global`, `<>`, 2)
    ->groupBy(`uni_ger.id_orgao`)
    ->groupBy(`orgao.sigla`)
    ->orderBy(`orgao.sigla`, `ASC`)
    ->get();

I’m pretty sure the query inside the Uilder query is correct, but when I call it inside the application, Laravel returns the following error:

SQLSTATE[HY000]: General error: 20018 Invalid object name 'procedimento'. 
[20018] (severity 16) [(null)] (SQL: select [orgao].[sigla] as 
[sigla_org_uni], COUNT(*) as total from [unidade] as [uni_ger] inner join 
[procedimento] as [p] on [protocolo].[id_protocolo] = [p].[id_procedimento] 
inner join [orgao] on [protocolo].[id_unidade_geradora] = [uni_ger].
[id_unidade] where [protocolo].[sta_protocolo] = P and [protocolo].
[sta_nivel_acesso_global] <> 2 group by [uni_ger].[id_orgao], [orgao].
[sigla] order by [orgao].[sigla] asc)
php sql laravel querybuilder

  • Just look at your SQL so INNER JOIN unidade uni_ger and in Builder it’s like this ->from(unit as uni_ger), it seems that there is something wrong there! take a look! and it seems that in SQL already has problems

  • here’s another problem ->where(protocol.sta_protocol, =, P) because if it is a string of that letter then it is single (or double) quotes example ->where(protocol.sta_protocol, =, 'P'), that is, your code has problems in SQL and Builder

  • It is! this case is quite peculiar, considering that I have not found any example to implement a Join in the query Builder in which there is no 'ON'. For example, I would like to know how to convert pure sql (INNER JOIN uni_ger drive) to Query Builder. I believe this is the source of the problem here. Thanks in advance for the feedback @Virgilionovic !

  • If you put the SQL right I make an answer with her, is that in one of the Ner Join is missing ! take a look there

2 answers

0

By the comments a Join done in the Builder is basically:

$this join(string $table, 
           string $one, 
           string $operator = null, 
           string $two = null, 
           string $type = 'inner', 
           bool $where = false)

So in this SQL is what can be generated basically:

DB::selectRaw('orgao.sigla, count(*) as total')
    ->from('protocolo')
    ->join(`procedimento`,'protocolo.id_protocolo','=','procedimento.id_procedimento') 
    ->join('unidade','protocolo.id_unidade_geradora','=','unidade.id_unidade')  
    ->where('protocolo.sta_protocolo','=', 'P')
    ->where('protocolo.sta_nivel_acesso_global', '<>', 2)    
    ->groupBy('orgao.sigla')
    ->orderBy('orgao.sigla')
    ->get();

but, missed pass in your question the table orgao your relationship and perhaps some more details so that the answer is a valid example for your question...

0

I was able to do it this way:

1) I made some changes to the JOIN of my query and it was the same way:

SELECT COUNT(*) as total, 
orgao.sigla AS sigla_org_uni 
FROM protocolo 
JOIN procedimento p ON protocolo.id_protocolo = p.id_procedimento 
JOIN unidade uni_ger ON protocolo.id_unidade_geradora = uni_ger.id_unidade 
JOIN orgao ON uni_ger.id_orgao = orgao.id_orgao 
WHERE protocolo.sta_protocolo='P' 
AND protocolo.sta_nivel_acesso_global<>'2' 
AND orgao.sigla <> 'xx'
AND orgao.sigla <> 'xx'
GROUP BY uni_ger.id_orgao, orgao.sigla 
ORDER BY orgao.sigla ASC

2) The final implementation in query Builder was thus:

$data = Protocolo::select('orgao.sigla as sigla_org_uni')
            ->addSelect(DB::raw('COUNT(*) as total'))
            ->from('protocolo')
            ->join('procedimento as p', function($join) {
                $join->on('protocolo.id_protocolo', '=', 'p.id_procedimento');
                })
            ->join('unidade as uni_ger', function($join) {
                $join->on('protocolo.id_unidade_geradora', '=', 'uni_ger.id_unidade');
                })
            ->join('orgao', function($join) {
                $join->on('uni_ger.id_orgao', '=', 'orgao.id_orgao');
                })
            ->where('protocolo.sta_protocolo', '=', 'P')
            ->where('protocolo.sta_nivel_acesso_global', '<>', 2)
            ->where('orgao.sigla', '<>', 'xx')
            ->where('orgao.sigla', '<>', 'xx')
            ->groupBy('uni_ger.id_orgao')
            ->groupBy('orgao.sigla')
            ->orderBy('orgao.sigla', 'ASC')
            ->get();

From now on, thank you for your attention @Virgilio Novic. This is the first time I’m posting on Stack Overflow and forgive me if I said anything stupid. = P

Browser other questions tagged php sql laravel querybuilder

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