Need custom dropdown filter.

[shankhadevpadam]

Summary of problem or feature request

I need the data filter using custom drop down. I have the data which display in well form now i need a custom dropdown in header to filter a data by dropdown value [Note: i need to supply this dropdown value for filter in laravel query. This is custom value so which i need to filter the data using this custom value.]

Code snippet of problem

This code work for default filter but i have added the dropdown using jquery so i need to pass this value in the laravel query to filter the data.

$(function () {
    var t = $('#user').DataTable({
        order: [[ 0, 'desc' ]],
        processing: true,
        serverSide: true,
        ajax: {
            url: '{!! route('datatables.data') !!}',
            data: function(d) {
                d.name = $('input[name=name]').val();
                d.email = $('input[name=email]').val();
            }
        },
        columns: [
            { data: 'id', name: 'id' },
            { data: 'name', name: 'name' },
            { data: 'email', name: 'email' },
            { data: 'arrival_date', name: 'arrival_date', orderable: true, searchable: true },
            { data: 'pick_up', name: 'pick_up', orderable: true, searchable: true },
            { data: 'destination', name: 'destination', orderable: true, searchable: true},
            { data: 'trekking_start_date', name: 'trekking_start_date', orderable: true, searchable: true},
            
            { data: 'trekking_end_date', name: 'trekking_end_date', orderable: true, searchable: true},
            { data: 'action', name: 'action', orderable: false, searchable: false},
        ]
    });
});

$(document).ready(function() {
    $('<label style="margin-left: 10px;">Filter by ' +
        '<select class="form-control input-sm">'+
            '<option value="volvo">Completed Trip</option>'+
            '<option value="saab">Upcoming Trip</option>'+
        '</select>' + 
        '</label>').appendTo("#user_wrapper #user_length");
});

And my query code in laravel

public function getClients(Request $request)
    {
        if($request->ajax()) {
            return Datatables::of(DB::select(DB::Raw('SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM role_user) ORDER BY users.id DESC')))
                    //}))
                    ->addColumn('arrival_date', function($user){
                        if(isset(Helper::getFlightsByClientID($user->id)->arrival_date)) {
                            return date('M j, Y', strtotime(Helper::getFlightsByClientID($user->id)->arrival_date));
                        } else {
                            return 'N/A';
                        }
                    })
                    ->addColumn('pick_up', function($user){
                        if(isset(Helper::getFlightsByClientID($user->id)->pick_up)) {
                            return Helper::getFlightsByClientID($user->id)->pick_up == 1 ? 'Yes' : 'No'; 
                        } else {
                            return 'N/A';
                        }
                    })
                    ->addColumn('destination', function($user){
                        if(isset(Helper::getTreksByClientID($user->id)->trekking_region_id)) {
                            return Helper::getTrekByID(Helper::getTreksByClientID($user->id)->trekking_region_id)->name; 
                        } else {
                            return 'N/A';
                        }
                    })
                    ->addColumn('trekking_start_date', function($user){
                        if(isset(Helper::getTreksByClientID($user->id)->trekking_start_date)) {
                            return date('M j, Y', strtotime(Helper::getTreksByClientID($user->id)->trekking_start_date));
                        } else {
                            return 'N/A';
                        }
                    })
                    ->addColumn('trekking_end_date', function($user){
                        if(isset(Helper::getTreksByClientID($user->id)->trekking_end_date)) {
                            return date('M j, Y', strtotime(Helper::getTreksByClientID($user->id)->trekking_end_date));
                        } else {
                            return 'N/A';
                        }
                    })
                    ->addColumn('action', function($user){
                        $html = '';
                        $html .= '<a class="btn bg-green btn-flat btn-xs" href="'.route('user.clients.edit', $user->id).'">Edit</a>';

                        if(auth()->user()->hasRole('superadmin')) {
                            $html .= ' <a onclick="if(!confirm(\'Are u sure to delete?\')) return false;" class="btn bg-red btn-flat btn-xs" href="'.route('user.clients.delete', $user->id).'">Delete</a>';
                        }

                        return $html;
                                
                    })
                    ->make(true);
        } else {
            abort(400, 'Bad Request.');
        }
    }

System details

• Operating System Ubuntu 16.04
• PHP Version 7.0.22
• Laravel Version 5.4
• Laravel-Datatables Version 1.10.16

[ChaosPower]

@shankhadevpadam you may do this in numerous ways. But what I do is pass the query builder before datatables in Server Side.

$model = new Example();
$model->where('column_to_filter','=',$this->request->get('filter_by'));

return datatables()->eloquent($model);

You may of course do conditional IFs.

$model = new Example();

if($this->request->has('name')) {
     $model->where('name','=','predefined value');
}

return datatables()->eloquent($model);

If you do not want to do the example above, you may refer to the docs. Post Column Search, Custom Filter

[shankhadevpadam]

My question is how to bind dropdown like image above, i have binded it through custom jquery but i need to bind using the datatable and this value should be available when dropdown in change.[Please look my code snippet]

[yajra]

Add id on the select box.

<select class="form-control input-sm" id="custom-filter">

Then include it on the request params.

        ajax: {
            url: '{!! route('datatables.data') !!}',
            data: function(d) {
                d.name = $('input[name=name]').val();
                d.email = $('input[name=email]').val();
                d.filter = $('#custom-filter').val();
            }
        },

Then do the necessary filter on the server side.

if ($filter = request('filter')) {
    $query->where('filter', $filter);
}

[yajra]

You need to trigger draw when filter is changed. Maybe something like:

<select class="form-control input-sm" id="custom-filter" onchange="$('#user').DataTable().draw()">

Also, you are using collection. Convert

DB::select(DB::Raw('SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM role_user) ORDER BY users.id DESC'))

To something like:

DB::table('users')->whereNot(...);

[shankhadevpadam]

Thanks now working.

[yajra]

Welcome, don't forget to star the project if you find it useful. :) Thanks!

[shankhadevpadam]

The dropdown is work perfectly but the data display after the value change in dropdown not order correctly.

[yajra]

You want to order by date when filtered? You need to do this on the js level. Or set the default order to that column.

[shankhadevpadam]

Yes, Any example please.

[yajra]

Don't have any example. See official js api docs https://datatables.net/reference/api/ for ref. The trick is to perform a manual js order command before performing the table redraw function.

[sistemaswebbrasil]

@yajra How to implement equivalent in datatables as a service ?

            data: function(d) {
                d.name = $('input[name=name]').val();
                d.email = $('input[name=email]').val();
                d.filter = $('#custom-filter').val();
            }

[yajra]

@sistemaswebbrasil using minifiedAjax():

$script = '
data.name = $('input[name=name]').val();
data.email = $('input[name=email]').val();
data.filter = $('#custom-filter').val();
';

return $this
            ->builder()
            ...
            ->minifiedAjax($url = '', $script = null, $data = [])