Laravel dynamic query

Question

I have a GET form with three filters.

make
Year
country

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

If a country is selected, get posts for that country only or all countries.
if a make is selected, get posts for that make only or all makes
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)
        ;
    }

Achraf Khouadja · Accepted Answer · 2019-08-13 08:06:37Z

1

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);
}

edited Aug 13, 2019 at 8:06

Achraf Khouadja

6,2994 gold badges29 silver badges40 bronze badges

answered Aug 13, 2019 at 7:30

PtrTon

3,8932 gold badges16 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Achraf Khouadja Over a year ago

Its better To do a where on the relation (whereHas) rather than Country::where

PtrTon Over a year ago

That's a nicer solution yes, could you provide an example for it?

Achraf Khouadja Over a year ago

it's exactly as you wrote almost but without using the Country Model (no big difference just feels nicer to me :v) i updated your answer, i hope i made no mistake

jon · Accepted Answer · 2019-10-05 22:13:30Z

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

Collectives™ on Stack Overflow

Laravel dynamic query

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related