Laravel - AJAX Search allow Multiple Filters to be applied

Question

I have a controller that works on an AJAX jQuery call when I need to search/filter the database:

$launchsitesatellite = DB::table('satellites')
->where(function($q) use ($request) {
    if(empty($request->type) && empty($request->rocket_type)) {
        $q->orWhere('satname','LIKE','%'.$request->search.'%')
            ->orWhere('norad_cat_id','LIKE','%'.$request->search.'%')
            ->orWhere('country','LIKE','%'.$request->search.'%')
            ->orWhere('object_id','LIKE','%'.$request->search.'%');
    } else {
        if(!empty($request->type)) {
            $q->orWhere($request->type,'LIKE','%'.$request->search.'%');
        }
        if(!empty($request->object_type)) {
            $q->orWhere('object_type','LIKE','%'.$request->object_type.'%');
        }
        if(!empty($request->launch_year)) {
            $q->orWhere('launch','LIKE','%'.$request->launch_year.'%');
        }
    }
})
->where('site', $site_code)->Paginate(300);

This controller can search/filter my database with no problem. The only thing I would like to fix is to allow multiple filters to be applied. For example, currently when I filter by Object Type and then decide to filter by Country, it resets the Object Type.

What I want to be able to do is to allow it to filter by Object Type AND Country, not only one.

There was a lack of examples/documentation so I could not find any examples of how this is done.

EDIT: JS AJAX Call

$("#filter-type").change(function() {
$value=$(this).val();
  $.ajax({
    type: "get",
    url: "{{$launchsitename->site_code}}",
    data: {'search':$value, type:'object_type'},
    success: function(data){
      $('#launchsatdisplay').html(data);
    }
});
});

Have you considered using a search engine with Laravel Scout: https://laravel.com/docs/5.4/scout? — Julien Bourdeau, Aug 09 '17 at 21:07
@LakshayJain I am able to select one filter, but when I select a second filter, it resets the first one. — D. 777KLM, Aug 10 '17 at 06:58
Looking at your code I don't see why using a second filter would **reset** the first one. It would mean that you'd see more results though because you're using `orWhere` or is this what you want? — Rwd, Aug 10 '17 at 07:23
@RossWilson I also don't really know why it would reset. I thought adding the filters into the `if` statement would make them work together, not reset onee another. — D. 777KLM, Aug 10 '17 at 09:29

score 2 · Accepted Answer · answered Aug 10 '17 at 10:17

I think the reason you're having this issue is because you're using orWhere rather than where so in theory the more filters you use the more results you will have returned (rather than limiting the results).

$launchsitesatellite = DB::table('satellites')
    ->where(function ($q) use ($request) {

        if (!$request->has('type') && !$request->has('rocket_type')) {
            $q->orWhere('satname', 'LIKE', '%' . $request->search . '%')
                ->orWhere('norad_cat_id', 'LIKE', '%' . $request->search . '%')
                ->orWhere('country', 'LIKE', '%' . $request->search . '%')
                ->orWhere('object_id', 'LIKE', '%' . $request->search . '%');
        } else {
            if ($request->has('type')) {
                $q->where($request->type, 'LIKE', '%' . $request->search . '%');
            }
            if ($request->has('object_type')) {
                $q->where('object_type', 'LIKE', '%' . $request->object_type . '%');
            }
            if ($request->has('launch_year')) {
                $q->where('launch', 'LIKE', '%' . $request->launch_year . '%');
            }
        }
    })
    ->where('site', $site_code)
    ->Paginate(300);

Also, just FYI, Laravel Query Builder comes with a when() method which is an alternative to using multiple if statements. So the main else section would look like:

$q
    ->when($request->has('type'), function ($q) use ($request) {
        $q->where($request->type, 'LIKE', '%' . $request->search . '%');
    })
    ->when($request->has('object_type'), function ($q) use ($request) {
        $q->where('object_type', 'LIKE', '%' . $request->object_type . '%');
    })
    ->when($request->has('launch_year'), function ($q) use ($request) {
        $q->where('launch', 'LIKE', '%' . $request->launch_year . '%');
    });

Obviously, you don't have to do this though (I just thought I'd mention it).

Hope this helps!

Just tried it, but unfortunately the when I select a second filter, it resets the first one. I guess the `where()` does not fix the issue. BTW, thanks for telling me about the `when()` method. Never knew it existed! — D. 777KLM, Aug 10 '17 at 10:24
@D.777KLM How are you selecting/submitting the filters? Are you just using links or is javascript involved as well? If it is javascript would you mind editing your question to show your js code? — Rwd, Aug 10 '17 at 10:39
I use a `select` to choose by what term I want filtered. I have posted the Javascript AJAX call in my question. — D. 777KLM, Aug 10 '17 at 10:44
@D.777KLM You're only submitting one value at a time so yes it will reset every time. — Rwd, Aug 10 '17 at 11:01
So I guess that is the problem why. Then I need to figure out a way to save the first filter and not reset it when I submit a second one unless the user resets it. — D. 777KLM, Aug 10 '17 at 11:04
@D.777KLM I would suggest either making your select multiple or using checkboxes (assuming you don't have that many). — Rwd, Aug 10 '17 at 11:11
I would be able to do checkboxs for one select as it only has three options, but my other select have quite a lot of options, so it would not be possible to be use select for them. I see what I can do. — D. 777KLM, Aug 10 '17 at 11:15

Laravel - AJAX Search allow Multiple Filters to be applied

1 Answers1

Linked