Query "or" and "and" in Laravel

Question

Could someone help me?

I need to do this query on the Laravel:

SELECT * FROM materiais 
        where material like '%caneta%' 
        or localização like '%armario%' 
        or fornecedor like '%kalunga%' 
        and setor = 'secretaria'
        order by item_id asc;

Objective: To filter all the "pens" or all the "armories" or all the "Kalunga" suppliers that exist only in the "secretariat", in the other sectors it ignores. Doing this direct search in the database, returns exactly what I need to return.

In Windows, although unsuccessful, the closest I could get to the above query was:

$teste = Material::where('setor', 'secretaria')
        ->orWhere('material', 'like', '%caneta%')
        ->orWhere('localização', 'like', '%armario%')
        ->orWhere('fornecedor', 'like', '%kalunga%')
        ->orderBy('id', 'desc')
        ->paginate(150);

This returns all materials correctly, but not only from the secretariat, it returns from all other sectors as well.

If anyone knows a solution. Thank you and hugs!

Answer 1

You must use Parameter Grouping, in this way:

$teste = Material::where('setor', 'secretaria')
                  ->where(function($query) {
                      $query->where('material', 'like', '%caneta%')
                          ->orWhere('localização', 'like', '%armario%')
                          ->orWhere('fornecedor', 'like', '%kalunga%');
                  })
                  ->orderBy('id', 'desc')
                  ->paginate(150);

In the first line of the function you use where, and the rest orWhere. The above code would be equivalent to:

...
WHERE setor = 'secretaria'
AND (
   material LIKE '%caneta%'
   OR
   localização LIKE '%armario%'
   OR
   fornecedor LIKE '%kalunga%'
)
ORDER BY id DESC
...

That is, the setor must be secretaria and at least one of the 3 conditions under material, localização or fornecedor must be attended to.