custom query in laravel datatable

Question

i am trying to do a query on a table likes so

tbl_bottle

name | type | location
bot1    A       USA
bot2    B       
bot3    C       USA
bot4    A        UK
bot5    A        UK

so when i load front end it will show this

name | type | location
bot1    A       USA
bot1    B       
bot3    C       USA
bot4    A        UK
bot5    A        UK

but when i type bot1 in search it should give me :

name | type | location
bot1    A       USA
bot1    B       

but what i get instead is

name | type | location
bot1    A       USA
bot1    B       
bot3    C       USA

this is what i have in my controller

     $bottle= tbl_bottle::select(
                'name',
                'type',
                'location'            
            )->where('location','=','USA')->OrWhere('location','=',' ');

return DataTables::of($bottle)
            ->addColumn('action', function ($bottle) {
                return '<a href="#" class="btn btn-xs btn-primary got=to" id="' . $members->name. '">View Details</a>';
            })->make(true);

so the datatable displays this correctly but when i try to search it does not work correctly what i mean is when i search

so in my front end all i have is

<table id="tbl_bottles" class="table">
<th>Name</th>
<th>Type</th>
<th>Location</th>
<th>Action</th>
</table>
    <script type="text/javascript">
$(document).ready(function () {
    $('#tbl_bottles').DataTable({
        "processing": true,
        "serverSide": true,
        "ajax": "{{ route('ajax.getBottles') }}",
        "columns": [
            { "data": 'name'},
            {"data": "type"},
            {"data": "location"},
            {"data":"action",orderable:false,searchable:false}
        ],
    });
});
</script>

the search input gets plugged in by the datatables the documentation iam using is https://github.com/yajra/laravel-datatables

Answer 1

If you want to search by bottle name, then first you have to send the search key word (bottle name) along with request, so that you can get it in the Method.

Send the search key word in your view:

<form>
  <input type="text" name="key_word" />
  <button type="submit">Submit</button>
</form> 

In your controller, get the key word

Get the key word and assign it to a variable, and then when you query the bottles add the variable in the where clause like I showed below:

public function getBottles(Request $request){
  $keyWord = $request->key_word; // get the key word and assign it to a variable 
  $bottle= tbl_bottle::select(
                'name',
                'type',
                'location'            
            )->where('name','=', $keyWord)->

return DataTables::of($bottle)
            ->addColumn('action', function ($bottle) {
                return '<a href="#" class="btn btn-xs btn-primary got=to" id="' . $members->name. '">View Details</a>';
            })->make(true);
}

Before you straight away take a user input into process, you have to validate it, but those things are besides this question boundary. My answer is only for what you have asked for.