Problems with Laravel Query Builder

Question

Problems with Laravel Query Builder

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I'm having the following problem, when trying to implement a query with joins, in the 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 that the query inside the query builder is correct, however 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

asked by anonymous 25.01.2018 / 14:40

2 answers

Spring + hibernate + jsp Get current webView url in xamarin

score 0 · Answer 1

By comments a join done in 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 this 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 you did not pass in your question the orgao table of your relationship and maybe some more details so that the answer is a valid example for your question ...

score 0 · Answer 2

I was able to do the following:

1) I made some changes to the JOIN of my query and it looked like this:

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 the query builder looks like this:

$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, thank you for the attention @Virgilio Novic. This is the first time I'm posting on Stack Overflow and forgive me if I said anything silly. = P