Laravel dynamic query

Question

I have a GET form with three filters.

1. make
2. Year
3. country

I need to get all posts from db. But filter the results based on these three filters.

1. If a country is selected, get posts for that country only or all countries.
2. if a make is selected, get posts for that make only or all makes
3. if a year is selected, get posts for that year only or all years

how to write one query that filters all these three options. What I have done is used if and else statements and written different queries for each scenario. That's 9 queries to get one information. Can we make it dynamic and just have one query?

My Example query:

    public function search(Request $request)
    {

        $search=$request->input('search');

        if($request->input('country') == "all")
        {
           $posts = Post::where('status','Published')->orderBy('status_change','DESC')
           ->where('status','Published')
           ->where(function($query) use ($search){
               $query->where('title','LIKE','%'.$search.'%');
               $query->orWhere('model','LIKE','%'.$search.'%');
               $query->orWhere('notes','LIKE','%'.$search.'%');
               $query->orWhere('description','LIKE','%'.$search.'%');
           })
           ->paginate(25);            
        }
        else
        {
           $posts = Country::where('country_name', $request->input('country'))->first()->posts()->orderBy('status_change','DESC')
           ->where('status','Published')
           ->where(function($query) use ($search){
               $query->where('title','LIKE','%'.$search.'%');
               $query->orWhere('model','LIKE','%'.$search.'%');
               $query->orWhere('notes','LIKE','%'.$search.'%');
               $query->orWhere('description','LIKE','%'.$search.'%');
           })
           ->paginate(25);
        }
        return view('welcome')
        ->with('published_posts',$posts)
        ;
    }

Answer 1

I used 'when' method.

$make = null;
$year = null;
$country = null;

if($request->filled('make')){
    $make = $request->query('make');
}
if($request->filled('year')){
    $year = $request->query('year');
}
if($request->filled('country')){
    $country = $request->query('country');
}

$posts = DB::table('posts')
    ->when($make, function($query, $make){
        return $query->where("make", "=", $make);
    })
    ->when($year, function($query, $year){
        return $query->whereYear("year", "=", $year);
    })
    ->when($country, function($query, $country){
        return $query->where('country', "like", $country);
    })
    ->get();

Check out the Laravel Docs:
Check out an article here

Answer 2

I think something like this would work:

/**
 * @param Request $request
 */
function search(Request $request)
{

    $postsQuery = Post::where('status', 'Published');

    if ($request->has('country')) {
        $country = $request->country;
        // assuming relationships are setup correclty
        $postsQuery->whereHas('country', function ($query) use ($country) {
            $query->where('country_name', 'LIKE', $country);
        });

    }
    if ($request->has('search')) {
        $postsQuery->where(function ($query) use ($search) {
            $query->where('title', 'LIKE', '%' . $request->search . '%');
            $query->orWhere('model', 'LIKE', '%' . $request->search . '%');
            $query->orWhere('notes', 'LIKE', '%' . $request->search . '%');
            $query->orWhere('description', 'LIKE', '%' . $request->search . '%');
        });
    }

    $postsQuery->orderBy('status_change', 'DESC')->paginate(25);

    return view('welcome')->with('published_posts', $result);
}