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

What do you mean by "so the datatable displays this correctly but when i try to search it does not work correctly"? — Kazmi, Aug 14 '19 at 05:39
Edit your Question and post this query with the `Method` it belongs to in your `Controller`. — Kazmi, Aug 14 '19 at 05:52
`$bottle= tbl_bottle::select( 'name', 'type', 'location' )->where('location','=','USA')->OrWhere('location','=',' ');` in this query their is no where condition on name column. — somsgod, Aug 14 '19 at 06:28
@Kaz i have edited my question again please see if its clear enough — 6563cc10d2, Aug 14 '19 at 06:47
@6563cc10d2, where is the search input element where you type your search key words? — Kazmi, Aug 14 '19 at 06:55
@Kaz when you use datatables it will automatically be generated — 6563cc10d2, Aug 14 '19 at 06:56
@Kaz have you done a Datatable with Laravel https://github.com/yajra/laravel-datatables — 6563cc10d2, Aug 14 '19 at 06:57
Search Keyword Value of datatable can be retrieved on Controller using this param ``search[value]``. So `$search_keyword = $request->input('search')['value'];` Then use `->where('name','=', $search_keyword );` — somsgod, Aug 14 '19 at 09:57

Kazmi · Answer 1 · 2019-08-14T06:48:09.947

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.

@6563cc10d2, as you said that the search key words attributes will automatically be injected into your jQuery method, do a `dd($request->all());`, in the controller's method and let me know the output of it. — Kazmi, Aug 14 '19 at 07:17

score 0 · Accepted Answer · answered Aug 19 '19 at 21:04

0

so this worked for me

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

return DataTables::of($bottle)->make(true);

answered Aug 19 '19 at 21:04

6563cc10d2

193
1
2
8

score 0 · Answer 3 · edited Jun 01 '20 at 11:59

Use this code (for raw query)

$searchValue = $request->input('search')['value']; // Search value from datatable
//-- END DEFAULT DATATABLE QUERY PARAMETER

//-- START DYNAMIC QUERY BINDING
$conditions = '1 = 1';
if (!empty($searchValue)) {
    $conditions .= " AND name LIKE '%" . trim($searchValue) . "%'";
}
//-- END DYNAMIC QUERY BINDING

//-- WE MUST HAVE COUNT ALL RECORDS WITHOUT ANY FILTERS
$countAll = \App\tbl_bottle::count();

//-- CREATE DEFAULT LARAVEL PAGING
$paginate = \App\tbl_bottle::select('*')
    ->whereRaw($conditions)
    ->paginate($limit, ["*"], 'page', $page);

Or you can see my blog post Laravel datatables for more details. Its provide sorting and searching too.

can you add relevant section from the link here? in that case the information stays here despite the link staying up or going — Akber Iqbal, Jun 01 '20 at 07:57

custom query in laravel datatable

3 Answers3