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

Question

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

Navigation

#1 by (4 votes)

4

I'm trying to do a simple search engine 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 can be empty returning null and mysql does not search 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 type in the field name: 'aaaa' and leave the others empty, nothing is returned. If I comment on the other's 'where ()' lines and leave off the name, it works normally.

Another thing, will have several fields.

laravel laravel-5

asked by anonymous 06.05.2016 / 00:45

1 answer

What happens on these lines in C ++? Vector reading in C is wrong

score 4 · Accepted Answer

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

I usually do this to make conditions for Where in a search, because as the logic is tied within Closure , I can maintain a good organization. Thus, you can make several conditions for your consultation without having headaches due to countless 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 has of Illuminate\Http\Request method will check if there is any value for that input. If there is not, cleverly an excerpt from the where condition is not mounted; but if there is, then there will be the query by the term.

For all those who want to create a search system in Laravel , I would advise doing so, since typing query logic within a Closure will avoid polluting the scope with multiple variables and loose ifs , outside the anonymous function.

For example, this logic without Closure would look ugly to damn 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 sentence of your question, you cite that you want to do this logic with several fields. My suggestion is, for you not to repeat the logic I quoted in the first example, you create a list of fields where you want to query and, using the Request::only method, you specify the possible fields to query the bank. You will loop through the array generated by the only method with a foreach and within it, it will check if the value of the field listed is empty and then query if the value is not empty. / p>

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 it got dynamic. If you add another element in array $campos , you will have a new added condition, brand new.

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

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

If you're confused when it comes to meaning, I'll explain: It's the same thing as if . However, since I just want to execute something, then I do not "waste time" using keys and line breaks. This short excerpt 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 decrease the code :).

I hope this helps!