Queries Builder Problems (Query Builder) in Laravel 4.2

Question

Hello,

I’m having trouble generating a querie using the Querie Builder of Laravel 4.2, the difficulty arises from the need that the resulting sentences for search in the database are within parentheses, below this the desired querie:

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 the next darling is returning:

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 inside the model using Laravel follows below: (Filing Vw_report.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 using advanced "Where" but could not generate the querie correctly.

Thank you in advance for your attention.

Answer 1

I guess it’s like, I did it right without putting like scope If it works, just transfer it there, 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();