Use 'WHERE' if value is different from 'NULL' in a search system

Question

I’m trying to make a simple search system with some text fields:

$nome = $request->nome;
$email = $request->email;
$bairro = $request->bairro;

$request = Contato::where('nome', 'like', $nome)
    ->where('email', 'like', $email)
    ->where('bairro', 'like', $bairro)
    ->paginate(15);

The problem is that some fields may become empty by returning null value and mysql does not search for anything. You can see the problem because I have the following table:

tabelaTeste

id | nome | email | bairro
1    aaaa   null     null 
2    bbbb   null     null 
3    cccc   null     null 

And if I write in the name field:'aaaa' and leave the others empty, nothing is returned. If I comment on the lines of the other 'Where()' and leave just the name, it works normally.

Something else, will have several fields.

Answer 1

Friend, I suggest using the whereSub of Laravel to do such an operation. It is called internally when you use a where with a Closure.

I usually do this to make conditions for the Where in a search, because as logic is tied inside the Closure, I can maintain a good organization. So, you can make several conditions for your consultation without having headaches due to numerous conditions

Example:

$callbackSearch = function ($query) use($request)
{
    if ($request->has('nome'))
    {
        $query->where('nome', 'like', $request->get('nome'));
    }

    if ($request->has('email'))
    {
        $query->where('email', 'like', $request->get('email'));
    }

    // E assim por diante
};

Contato::where($callbackSearch)->paginate(15);

The method has of Illuminate\Http\Request will be in charge of checking if there is any value for that input. If there is not, intelligently an excerpt from the condition where is not assembled; but if there is, there will be consultation by the term.

For all who want to create a search system on Laravel, I would advise to do so, because "tie" the logic of consultation within a Closure will prevent scope pollution with various variables and ifs loose, outside the anonymous function.

For example, that logic without Closure would be ugly as hell, like this:

$query = Contato::newQuery();

if ($request->has('nome'))
{
    $query->where('nome', 'like', $request->get('nome'));
}

if ($request->has('email'))
{
    $query->where('email', 'like', $request->get('email'));
}

$contato = $query->paginate(15);

In the last excerpt of your question, you quote that you want to make this logic with several fields. My suggestion is, for you not to repeat the logic I quoted in the first example, is you create a list of fields where you want to query and, use the method Request::only, you specify the possible fields for database query. You will go through the array generated by the method only with a foreach and, inside it, will make a check if the value of the field listed is empty and then make the query if the value is not empty.

Example:

$callbackSearch = function ($query) use($request)
{

    $campos = ['nome', 'email', 'telefone', 'endereco'];

    foreach ($request->only($campos) as $campo => $valor)
    {
        $valor && $query->where($campo, 'like', $valor);
    }

};

See how dynamic it looks. If you add one more element in array $campos, will have a new condition added, brand new.

Note that in the example I used a simplified conditional expression:

  $valor && $query->where($campo, 'like', $valor);

If you got confused when to the meaning, I explain: It’s the same thing as making one if. However, since I just want to run something, so I don’t "waste time" using keys and line breaks. This short passage could be written in the following ways:

if ($valor) $query->where($campo, 'like', $valor);

if ($valor) {
    $query->where($campo, 'like', $valor);
}

if ($valor != '') {
   $query->where($campo, 'like', $valor);
}

I just want you to understand that I wanted to tone down the code :).

I hope this helps!