How to Filter Multiple Fields in the Query?

Asked

Viewed 1,007 times

6

I need to make a filter in a query in Laravel.

How can I do this in the best way ? For example, I have a query on my page and I want to filter it. For this I have a column that has some filter options.

How would I do that in the code? I thought so:

if($string)
   $consulta = Model::where('nome', 'like', '%'.$consulta.'%');

if($numero)
   $consulta = Model::where('matricula', '=', $numero);

if($outro)
   $consulta = Model::where('outro', 'like', '%'.$outro.'%');

And then at the end I would do the consultation. Is there any better way ?

laravel

  • In codeigniter I would do that way ai you propose, I found the most sensible way

  • 1

    I think that model is fine, but I have a question: in which cases would I enter the last if($outro)? In that structure that showed why it does not else if.., not the need to do the remaining checks after a return True... $consulta will always be the last condition you enter

  • It’s just an example I left not to increase the code in my question, but it can be any filter. City, Neighborhood... Sex...

  • I think you’re doing the right thing

  • That question seems to be duplicated, I answered it here one day

  • 1

    Possible duplicate of Laravel - Use 'WHERE' if value is different from 'NULL'

  • @Andrébaill think that indepentende to be Codeigniter or Laravel, the logic is the same. And the simplification should also be the same. It does not seem wise to fill the code with ifs, which will check the same thing, but for different values.

Show 2 more comments

2 answers

5


Yes it is possible to do this using the method where(function(){}) as follows:

To the Laravel 4.2

$consulta = Model::where(function($query) use($string, $numero, $outro) {

     if($string)
         $query->where('nome', "like", "%{$consulta}%");

     if($numero)
         $query->where('matricula', '=', $numero);

      if($outro)
         $query->where('outro', "like", "%{$outro}%"); 
})
->paginate(20);

or 

$consulta = Model::where(function($query) {

         if(Input::has('consulta'))
             $consulta = Input::get('consulta'); 
             $query->where('nome', "like", "%{$consulta}%");

         if(Input::has('matricula'))
             $numero = Input::get('matricula');
             $query->where('matricula', '=', $numero);

          if(Input::has('outro'))
             $outro = Input::get('outro');
             $query->where('outro', "like", "%{$outro}%"); 
    })
   ->paginate(20);

to the Laravel 5.2 

$consulta = Model::where(function($query) use($request) {

         if($request->has('consulta'))
             $consulta = $request->consulta; 
             $query->where('nome', "like", "%{$consulta}%");

         if($request->has('matricula'))
             $numero = $request->matricula;
             $query->where('matricula', '=', $numero);

          if($request->has('outro'))
             $outro = $request->outro;
             $query->where('outro', "like", "%{$outro}%"); 
    })
    ->paginate(20);

3

Petter, if the logic of the query is going to repeat, there is no reason to fill "the parade" of if. I recommend that you simplify your code by encapsulating the query logic within a loop.

For example, you want the fields primeiro_nome, email and sobrenome are researched with LIKE and nivel_id, idade and curso are searched with the operator =.

You wouldn’t need to do several ifs, but could make a foreach and apply logic inside.

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

    $likes = ['primeiro_nome', 'email', 'sobrenome'];

    foreach ($request->only($campos_like) as $name => $value)
    {
        $value && $query->where($name, 'LIKE', "%{$value}%");
    }


    $equals = ['nivel_id', 'idade', 'curso'];

    foreach ($request->only($campos_equal) as $name => $value) {

        $value && $query->where([$name => $value]);
    }

};

Usuario::orderBy('primeiro_nome')->where($calback)->paginate(15)->appends($request->all());

Please note that the code does not need to be filled in if. If you can simplify, always simplify.

Maybe you’re wondering, "But where are the ifs"?

If you look closely, I’ve replaced the if by the expression $value && $query->where(...). I usually do that when I want to simplify a if. But I won’t go into too much detail, because I’ve answered that before :)

Refactoring is worth it if the soul is not small!

Refatorar vale a pena se a alma não é pequena!

Browser other questions tagged laravel

You are not signed in. Login or sign up in order to post.