ahmadbadpey's avatar

filterColumn does not work in yajra/laravel-datatables

to show list of Users I use https://github.com/yajra/laravel-datatables

In addition to Datatables own search box to filter results, I have a combobox to filter user based on their level.

This is My Combobox :

<div class="form-group" id="levelFilterWrapper">
                                <label for="levelFilter">Userlevel :</label>
                                <select id="levelFilter" name="levelFilter" class="form-control">
                                    <option value="all">All</option>
                                    <option value="admin">admin</option>
                                    <option value="user">user</option>
                                    <option value="centerManager">centerAdmin</option>
                                </select>
                            </div>

And this is My jQuery Code :

var allUsersTable =
                $('#allUsersTable').DataTable({
                    processing: true,
                    serverSide: true,
                    "bSort": false,
                    "responsive": true,
                    ajax: {
                        url :   '{!! route('usersData') !!}',
                        data: function(d){
                            d.level =   $('#levelFilter').val()
                        }
                    },
                    columns: [
                        {data: 'checkbox', name: 'checkbox', "width": "20px"},
                        {data: 'userPic', name: 'userPic', 'className': 'text-center'},
                        {data: 'email', name: 'email'},
                        {data: 'created_at', name: 'created_at'},
                        {data: 'level', name: 'level', 'className': 'text-center'},
                        {data: 'actions', name: 'actions', 'className': 'text-center'}
                    ]
                });

        $('#levelFilter').change(function (e) {


            allUsersTable.draw();
        });

And This is My yajra Datatables laravel Code :

$users = User::select(['user_id', 'name', 'family', 'email', 'created_at', 'level']);

        $datatable = app('datatables')->of($users)
            ->filter(function ($query) use ($request) {
                if (!empty($request->get('level')) and $request->get('level') != 'all') {
                    return $query->whereLevel($request->get('level'));
                } else {
                    return $query;
                }
            })
            ->orderBy('created_at', 'desc')
            ->addColumn('checkbox', '<input type="checkbox" name="item_id[]" value="{{$user_id}}" id="Check_{{$user_id}}" class="minimal">')
            ->addColumn('userPic', function ($user) {
                return '<img src="http://www.gravatar.com/avatar/' . md5($user->email) . '?d=mm&s=30" class="img-thumbnail img-responsive">';
            })
            ->editColumn('email', function ($user) {
                return '
                        ' . $user->name . ' ' . $user->family . '
                        <div class="en-text"><a href="mailto:' . $user->email . '" target="_blank">' . $user->email . '</a></div>
                    ';
            })

            ->editColumn('created_at', function ($user) {
                return JDF::jdate('l j F Y - G:i', $user->created_at);
            })
            ->addColumn('actions', function ($user) {
                return '
                        <a href="/admin/user/' . $user->user_id . '/edit" class="btn btn-primary btn-sm btn-flat fa fa-pencil-square-o" data-toggle="tooltip" data-placement="top" title="Edit And View"></a>
                        <a href="#" class="btn btn-danger btn-sm btn-flat fa fa-trash-o delItem" data-toggle="tooltip" data-placement="top" title="Delete" data-item-id="' . $user->user_id . '"></a>
                    ';
            });

        if ($keyword = $request->get('search')['value']) {
            $datatable->filterColumn('name', 'where', '=', "%$keyword%");
        }

        return $datatable->make(true);

filtering via levelFilter combobox works fine But I want when Admin select a user level form that, filtering via DataTable search box , search in that selected level. But after type some characters in search box , again shows exactly same results from combobox filtering and seems search box filtering does not work in this case

0 likes
2 replies
N9ne's avatar

Did you find a solution to this?

ahmadbadpey's avatar

Yes

using ->filter() will disable global search which causes searching on text box not to work. If you want to use filter, then you need to write each query for each field.

On the other hand, you can just extract the level filter on query level to make this work like:

and solution :

    $users = User::select(['user_id', 'name', 'family', 'email', 'created_at', 'level']);
if (!empty($request->get('level')) and $request->get('level') != 'all') {
    $users = $users->whereLevel($request->get('level'));
}

    $datatable = app('datatables')->of($users)
        ->orderBy('created_at', 'desc')
        ->addColumn('checkbox', '<input type="checkbox" name="item_id[]" value="{{$user_id}}" id="Check_{{$user_id}}" class="minimal">')
        ->addColumn('userPic', function ($user) {
            return '<img src="http://www.gravatar.com/avatar/' . md5($user->email) . '?d=mm&s=30" class="img-thumbnail img-responsive">';
        })
        ->editColumn('email', function ($user) {
            return '
                    ' . $user->name . ' ' . $user->family . '
                    <div class="en-text"><a href="mailto:' . $user->email . '" target="_blank">' . $user->email . '</a></div>
                ';
        })

        ->editColumn('created_at', function ($user) {
            return JDF::jdate('l j F Y - G:i', $user->created_at);
        })
        ->addColumn('actions', function ($user) {
            return '
                    <a href="/admin/user/' . $user->user_id . '/edit" class="btn btn-primary btn-sm btn-flat fa fa-pencil-square-o" data-toggle="tooltip" data-placement="top" title="Edit And View"></a>
                    <a href="#" class="btn btn-danger btn-sm btn-flat fa fa-trash-o delItem" data-toggle="tooltip" data-placement="top" title="Delete" data-item-id="' . $user->user_id . '"></a>
                ';
        });

    return $datatable->make(true);

Please or to participate in this conversation.