Filter multiple results from database in view

Hi @daugaard47 ,

I assume you here because you already choose to use Laravel framework. However, with front-end it's a bit tricky because there is no any bad or good way to implement it. It depends from Dev flavor and possibility's "How to" do it. I am pretty much sure it will be the best for you to use VueJs together with axios (a new way of Ajax) because here you will find all necessary resources. Current Laravel version uses Laravel-Mix and it already have some dependency packaged prepared to work "straight from the box" like Jeffrey says. My suggestion is to dive into this VueCast and explore VueJs possibilities to accomplish your problem.

There is another option which do not consume so much time and it is built in to Laravel - Blade template engine. Try not to overwhelm yourself with too many "ways" to do it. It will take time to implement everything so just try to narrow it at the beginning and add in small steps everything that you think is necessary. I hope it will guide you in some way this information. I think that here and using google search engine you will find all the answers.

Pretty solid advice. I've been putting off learning Vue for some time now. Guess it might be a good time to dive into it. Thanks for the quick response.

So I've been trying to figure this out. I got it to filter with Location & Gender By doing the following:

On my Filter form

  <form action="{{ route('child-sponsorship-filter') }}" method="get">
  @csrf
    Location
            <select name="location">
              <option value="">No Preference</option>
                @foreach ($country as $c)
              <option value="{{$c->country_name}}">{{$c->country_name}}</option>
                @endforeach
            </select>
        
        
          Gender
            <select name="gender">
              <option value="">No Preference</option>
              <option value="Male">Male</option>
              <option value="Female">Female</option>
            </select>
        
                <button type="submit">Filter Results</button>
            
  </form>

On my KidController

    public function index(){
            $country = DB::table('countries')->orderBy('country_name')->get();
            $sponsorKid = Kid::orderBy('first_name')->get();
            return view('child-sponsorship.index',compact('sponsorKid','country')); 
    }


    public function filter(Request $request){
            $location = $request->location;
            $gender = $request->gender;
            // $filter = [$location->location, $gender->gender];
            //dd($location);
        
        $country = DB::table('countries')->orderBy('country_name')->get();

        if(empty($gender) && empty($location)){
            $sponsorKid = Kid::orderBy('id')->get();
        }elseif(!empty($gender) && !empty($location)){
           $sponsorKid = DB::table('kids')->where('current_country', $location)->where('gender', $gender)->orderby('id')->get(); 
        }elseif($gender != true){
            $sponsorKid = DB::table('kids')->where('current_country', $location)->orderby('id')->get();
        }elseif($location != true){
            $sponsorKid = DB::table('kids')->where('gender', $gender)->orderby('id')->get();
        }else{
            $sponsorKid = Kid::orderBy('id')->get();
        }
        return view('child-sponsorship.index',compact('sponsorKid','country')); 
    }

Kids Table

+----+------------+--------+-----------------+
| id | first_name | gender | current_country |
+----+------------+--------+-----------------+
|  1 | Emma       | Female | Haiti           |
|  2 | Lea        | Female | Nepal           |
|  3 | Matthew    | Male   | India           |
|  4 | Andrew     | Male   | Nepal           |
|  5 | CHRIS      | Male   | Haiti           |
+----+------------+--------+-----------------+

Countries Table

+----+--------------+
| id | country_name |
+----+--------------+
|  1 | Haiti        |
|  2 | India        |
|  3 | Nepal        |
+----+--------------+

Routes in use

Route::get('/child-sponsorship','KidController@index')->name('child-sponsorship');
Route::get('/child-sponsorship/filter','KidController@filter')->name('child-sponsorship-filter');

ISSUES

1. Do to the @csrf in the form I get the token in the URL. How can I remove that?
2. After selecting a filter: For example: Location, Haiti. I can't seem to get the current filter to stay in place on the select box after page refresh. It returns back to it's default state after page refresh.
3. Can someone show me how I can update the filters via ajax without the page refresh.

Can someone show me how I can update the filters via ajax without the page refresh

Here's a good starter guide, can build from there.

https://scotch.io/@emmanueladegor/dependent-select-box-for-laravel-beginners213

Once you learn the basic ajax, it will start hopping along.

There are also some Youtube videos on dependent dropdowns.

@JLRDW - Okay I'm getting closer I think.

Routes:

    Route::get('/child-sponsorship','KidController@index')->name('child-sponsorship');
    Route::get('/child-sponsorship/filter','KidController@filter')->name('child-sponsorship-filter');

Index View (Show filter form and sponsor children)

                 <form id="myForm">
                                <label>Location</label>
                                <select id="location" name="location">
                                    <option value="">No Preference</option>
                                    @foreach ($country as $c)
                                    <option value="{{$c->country_name}}">{{$c->country_name}}</option>
                                    @endforeach
                                </select>

                                <label>Gender</label>
                                <select id="gender" name="gender">
                                    <option value="">No Preference</option>
                                    <option value="Male">Male</option>
                                    <option value="Female">Female</option>
                                </select>

                                <button id="ajaxSubmit">Filter Results</button>
                </form>
    @foreach ($sponsorKid as $sk)
    ////.......
    @endforeach 
-----------------------------------------------------
// JS

@section('filter_js')

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>

<script>
   jQuery(document).ready(function(){
    jQuery('#ajaxSubmit').click(function(e){
     e.preventDefault();
     $.ajaxSetup({
      headers: {
          'X-CSRF-TOKEN': $('meta[name="_token"]').attr('content')
      }
  });
     jQuery.ajax({
        url: "{{ route('child-sponsorship-filter') }}",
        method: "get",
        data: {
           location: jQuery('#location').val(),
           gender: jQuery('#gender').val(),
       },
       success: function(result){
           console.log(result);
       }});
 });
});
</script>
@endsection

According to my Debug bar tool I'm getting the correct query:

select * from `kids` where `current_country` = 'Nepal' and `gender` = 'Female' order by `id` asc

But it is NOT showing correctly on the page....? I'm also getting the correct path in my console:

jquery.min.js:19 XHR finished loading: GET "http://xxxxxx.oo/child-sponsorship/filter?location=Nepal&gender=Female".

What am I doing wrong here?

These are my 2 functions in KidController (index and filter)

        public function index(){ 

        $country = DB::table('countries')->distinct('country_name')->orderBy('country_name')->get();
        $sponsorKid = Kid::distinct()->orderBy('first_name')->get();
        return view('child-sponsorship.index',compact('sponsorKid','country'));
    } 

        public function filter(Request $request){ 
             $location = $request->location;
            $gender = $request->gender;
 
        $country = DB::table('countries')->orderBy('country_name')->get();
        if(empty($gender) && empty($location)){
            $sponsorKid = Kid::orderBy('id')->get();
        }elseif(!empty($gender) && !empty($location)){
           $sponsorKid = DB::table('kids')->where('current_country', $location)->where('gender', $gender)->orderby('id')->get(); 
        }elseif($gender != true){
            $sponsorKid = DB::table('kids')->where('current_country', $location)->orderby('id')->get();
        }elseif($location != true){
            $sponsorKid = DB::table('kids')->where('gender', $gender)->orderby('id')->get();
        }else{
            $sponsorKid = Kid::orderBy('id')->get();
        }
        return view('child-sponsorship.index',compact('sponsorKid','country'));

    }

I'm returning the same view as I don't want it to refresh to a new page.

When using ajax, you will have parts of the page built already. The idea is filling those parts, like a division with data without page reload.

As example watch this video, just a rough draft of an app. See at about 30 seconds into, I have a modal popup. It's already there with css display:none, when needed, it's display:block

https://drive.google.com/file/d/0B1_PFw--3o74TC16eXRBYXZBNFk/view

So when returning ajax data you:

return Response::json($whatever);

And in the JS deal with it weather axios, jquery, etc

Pace yourself, see some youtube videos, this stuff does take a while to learn.

I have two very basic starter guides also:

https://laracasts.com/discuss/channels/guides/jquery-ajax-post-example

and

https://laracasts.com/discuss/channels/guides/jquery-get-response

Again very basic.

@JLRDW - So basically, am I almost there..? Just need to figure out how to return the return Response::json($value) ? I've literally been trying to figure this out for 2 days now. If you could help me fix my code up I would greatly appreciate it.

See in the tutorial they use:

return json_encode($states);

Laravel uses

return Response::json($states);

If already an array, fine, if result is an object you may need to array like:

public function testg()
    {
        $dogid = Request::input('somevar');
        $dog = Dog::select('dogname', 'comments')
                ->where('dogid', '<', $dogid)
                ->get()
                ->toArray();
        return Response::json($dog);
        
    }

Then in the example tutorial they append the dom in the jquery, see this part:

$.each(data, function(key, value){

     $('select[name="state"]').append('<option value="'+ key +'">' + value + '</option>');

   });

So if using ajax you don't return view, you deal with the data in the javascript. You may want to work that states tutorial for a better understanding, hard to explain in a post.

Search for jquery appending to the dom in google for perhaps better examples.

Me, I have almost stopped using dropdowns, I like modal popup tables, just me and my preference.

Good luck, just remember it is a learning curve, not easy at first.

Okay, Thanks!

Found this also:

https://gist.github.com/msurguy/5138788

Remember, the dropdowns are there already, you are just putting in the option values in the javascript from database results.

User picks a country in dropdown 1, that one is filled already.

For second dropdown you use the selection from dropdown 1 to query the results for dropdown 2, and fill with the data returned in the query, and so on.

Do not start with everything, just have a view with 2 dropdowns and practice till you get it.

@jlrdw Can you reach out to me at daugaard47@gmail.com Would like to speak with you directly if you don't mind. Thanks

That first page you see on World vision has multiple things happening,

The dropdowns are using ajax in the background to update not only the "next dropdown", but main page content is being updated as well.

Namely via different regions of the page (divs).

If filtering is also a hold up, practice some complex filtering first without ajax.

There's articles like

https://laraveldaily.com/filtering-query-results-with-different-conditions-on-the-same-page/

Post like: https://laracasts.com/discuss/channels/eloquent/how-can-i-write-multiple-where-clause-query-using-laravel-eloquent

Hint Jeffrey doesn't have a power search like google so use google and search like this:

laravel  filter with multiple conditions

or

laravel query with multiple conditions

So one get to the point where you know how to query for multiple conditions.

Then get to the point where you know how do display ajax results, DO NOT worry about filtering so much at first.

Then start combining both.

Again this is not a quick process, takes a while.

Many times the LIKE clause is used, i.e.,

        $query = Dog::where('dogname', 'like', $dogsch);
        if ($aval == "n") {
            $query->where('adopted', '=', 1);
        } else if ($aval == "y") {
            $query->where('adopted', '=', 0);
        }
        $dogs = $query->orderBy('lastedit', 'DESC')->paginate(5);

        //$orders->appends(['query' => $keyword]);
        $params = array('psch' => $dogsearch, 'aval' => $aval);

Params are passed via querystring in this case like

If (!empty(Request::input('psch'))) {
            $dogsearch = Request::input('psch');
        } else {
            $dogsearch = "";  //show all
        }
        If (!empty(Request::input('aval'))) {
            $aval = Request::input('aval');
        } else {
            $aval = "";  //show a;;
        }

For the LIKE clause:

$dogsch = $dogsearch . "%";

Looks like this video:

https://drive.google.com/file/d/0B1_PFw--3o74YjVreHNBOWU2aEE/view

@JLRDW - I get it. It's hard and takes time to learn. I need to hire a developer to help me now. Thanks for your help. Will definitely come back to this and learn it correctly, but need a solution now.

@daugaard47 You might find it useful to review the talk given by Jonathan Reinink on "Advanced Querying with Eloquent" https://laracon.net/2018

He builds up a multi-part query in that talk.