Problems with Query Builder in Laravel 4.2

Question

Problems with Query Builder in Laravel 4.2

Navigation

#1 by (1 votes)

1

Hello,

I am having trouble generating a query using the Query Builder of Laravel 4.2 , the difficulty arises from the need for resulting queries in the database to be inside of parentheses, below is the desired query:

select * from 'EV_RELATORIO' 
    where 'DTALTERACAO' between ? and ? and
         ('MUN_ID' is null or ('MUN_ID' in (?))) and
         ('REG_ID' is null or ('REG_ID' in (?)));

But you are returning the following query:

select * from 'EV_RELATORIO' 
    where 'DTALTERACAO' between ? and ? and
          'MUN_ID' in (?) or 'MUN_ID' is null and
          'REG_ID' in (?) or 'MUN_ID' is null';

The construction of the querie within the model using Laravel follows below: (Archive VW_Relation.php )

<?php

class VW_Relatorio extends BaseModel
{
    protected $table = 'EV_RELATORIO';
    protected $primaryKey = 'ID';

    public function scopeMundos($query, $dados) {
        if(!empty($dados)){
            $query->whereIn('MUN_ID', $dados)->orWhereNull('MUN_ID');
        }
        return $query;
    }

     public function scopeRegionais($query, $dados) {
        if(!empty($dados)){
            $query->whereIn('REG_ID', $dados)->orWhereNull('MUN_ID');
        }
        return $query;
    }
}

I make the call in my class with the following code:

$rel = \VW_Relatorio::whereBetween('DTALTERACAO',array($dataInicial, $dataFinal))
    ->mundos($data[0]['mundos'])
    ->regionais($data[0]['regionais'])
    ->get();

I tried to use "Where" advanced, but I can not generate the querie correctly.

Thank you in advance for your attention.

sql query laravel-4 where

asked by anonymous 05.12.2016 / 20:12

1 answer

How to turn v-on: click="sync" into something automatic that is triggered when opening the html How to get DB value for a variable?

score 1 · Accepted Answer

I think it's like this, I did it right without putting it as scope if it works, just transfer it, the syntax is this:

$param1 = $data[0]['mundos'];
$param2 = $data[0]['regionais'];
$rel = \VW_Relatorio::whereBetween('DTALTERACAO',array($dataInicial, $dataFinal))
    ->where(function($query) use ($param1){
           $query->whereIn('MUN_ID', $param1)
                 ->orWhere(\DB::raw('MUN_ID is NULL')); 
    })
    ->where(function($query) use ($param2){
           $query->whereIn('REG_ID', $param2)
                 ->orWhere(\DB::raw('REG_ID is NULL'));
    })
    ->get();