How to write this query using Eloquent?

Question

I need to write a specific SQL in a project Laravel but I can’t convert the SQL for the syntax of Eloquent, basically, I need to do this SELECT:

SELECT * FROM `movimentacoes` 
    WHERE nome LIKE '%pattern%' AND grupo_id = id AND (custo_id != 0 OR custo_id IS NULL);

Until the restriction of the field grupo_id, can write:

return $this->movimentacoes
               ->where('nome','LIKE','%'.$nome.'%')
               ->where('grupo_id', "=" ,$id)
               ->orderBy('nome', 'asc')
               ->paginate($this->PAGINATE);

Intuitively, to add field constraint custo_id, I should just add:

    return $this->movimentacoes
                           ->where('nome','LIKE','%'.$id.'%')
                           ->where('grupo_id','=',$id)
/*ADD*/                    ->where('custo_id','=',$custo_id)->orWhereNull('custo_id')
                           ->orderBy('nome', 'asc')
                           ->paginate($this->PAGINATE);

but instead of the return being the same as the SQL I quoted would return, the return is the same as the following SQL would return:

SELECT * FROM `movimentacoes` 
    WHERE nome LIKE '%pattern%' AND grupo_id = id AND (custo_id != 0) OR custo_id IS NULL;

Note that the difference between the two SQL is in the position of a single parenthesis, but the result is totally different.

I need the statement that the custo_id may be different from 0 or nulo, because just other than 0 does not return me null records.

Answer 1

Of that SQL:

SELECT * FROM `movimentacoes` 
    WHERE nome LIKE '%pattern%' AND grupo_id = id AND (custo_id != 0 OR custo_id IS NULL);

To Eloquent Model:

return $this->movimentacoes
            ->where('nome','LIKE','%'.$nome.'%')
            ->where('grupo_id', "=" ,$id)
            ->where(function($query) { // aqui a abordagem
                $query->where('id', '<>', 0)
                      ->orWhereNull('id');
            })
            ->orderBy('nome', 'asc')
            ->paginate($this->PAGINATE);

This type of approach in the Builder is called Parameter Grouping.

Answer 2

To group multiple conditions you must pass an anonymous function in the call of where. For example:

$query->where(function ($condicao) {
    $condicao->where('custo_id','=',$custo_id)->orWhereNull('custo_id');
});

This way the generated SQL will include the parentheses in the correct place.