Laravel Datatable default search is not working with custom filters

Question

I am using yajra datatable in laravel.I have implemented the table.It is working fine except search. I have a custom filter too. Below code is showing the Datatable initialization and ajax request. User Type Filter is working fine.

<script>
    $(document).ready(function() {
            var table = $('#dataList').DataTable({
                processing: true,
                serverSide: true,
                ajax: {
                    url: "{{ route('users.user.index') }}",
                    data: function (d) {
                        d.user_type = $('#user_type_filter').val();
                    }
                },
                columns: [
                    {data: 'DT_RowIndex', name: 'DT_RowIndex'},
                    {data: 'name', name: 'name'},
                    {data: 'email', name: 'email'},
                    {data: 'phone', name: 'phone'},
                    {data: 'action', name: 'action', orderable: false, searchable: false},
                ]
            });
        $('#search-form').on('submit', function(e) {
            table.draw();
            e.preventDefault();
        });            
    } );
</script>

Backend index page to filter the user type

public function index(Request $request)
{
    /**
     * Ajax call by datatable for listing of the users.
     */
    if ($request->ajax()) {
        $data = User::with('userType')->get();
        $datatable =  DataTables::of($data)
            ->filter(function ($instance) use ($request) {                    
                if ($request->has('user_type') && $request->get('user_type')) {
                    $instance->collection = $instance->collection->filter(function ($row) use ($request) {
                        //return Str::contains($row['phone'], $request->get('phone')) ? true : false;
                        return $row['user_type_id'] == $request->get('user_type');
                    });
                }  
               if ($request->input('search.value') != "") {
                     $instance->collection = $instance->collection->filter(function ($row) use ($request) {
                        return Str::contains($row['name'], $request->input('search.value')) ? true : false;
                    });
                }                                      
            })             
            ->addIndexColumn()
            ->addColumn('action', function ($user) {
                return view('users.datatable', compact('user'));
            })
            ->rawColumns(['action'])
            ->make(true);
        return $datatable;
    }

    $user_type = UserType::pluck('user_type','id')->all();
    $users = User::with('userType')->paginate(25);

    return view('users.index', compact('users','user_type'));
}

This code is generating the search only on current page.

Answer 1

you should try like this

   filterColumn('name', function($query, $keyword) {
                    $sql = "name like ?";
                    $query->whereRaw($sql, ["%{$keyword}%"]);
                })

refer this link https://datatables.yajrabox.com/fluent/custom-filter

Answer 2

I have fixed the issue with relation by changing to below code

if ($request->input('search.value') != "") {
     $instance->collection = $instance->collection->filter(function ($row) use ($request) {
        return Str::contains($row['user']['name'], $request->input('search.value')) ? true : false;
    });
} 

I wanted to access the relation user and its name by changing the $row['user']['name']

Answer 3

The answer given by B L Praveen is almost crisp, but what if you wish to search the complete the data table instead of just one column. Below is my solution:

Add below code to your pre-added custom filters:

if ($request->input('search.value') != "") {
if((!Str::contains(strtolower($row['name']), strtolower($request->input('search.value')))) && 
    (!Str::contains(strtolower($row['id']), strtolower($request->input('search.value')))) && 
    (!Str::contains(strtolower($row['created_at']), strtolower($request->input('search.value')))) && 
    (!Str::contains(strtolower($row['nuvei_transaction_id']), strtolower($request->input('search.value')))) && 
    (!Str::contains(strtolower($row['itemcount']), strtolower($request->input('search.value')))) && 
    (!Str::contains(strtolower($row['product_name']), strtolower($request->input('search.value')))) && 
    (!Str::contains(strtolower($row['status']), strtolower($request->input('search.value')))) && 
    (!Str::contains(strtolower($row['order_status']), strtolower($request->input('search.value')))) && 
    (!Str::contains(strtolower($row['order_status_fr']), strtolower($request->input('search.value')))) && 
    (!Str::contains(strtolower($row['product_name_fr']), strtolower($request->input('search.value')))) && 
    (!Str::contains(strtolower($row['sales']), strtolower($request->input('search.value')))) && 
    (!Str::contains(strtolower($row['taxes']), strtolower($request->input('search.value')))) && 
    (!Str::contains(strtolower($row['total']), strtolower($request->input('search.value')))) ){
    $present++;
}}

Answer 4

i get the same problem, so i make fake filter custom with eloquent "when" and i not use the function filter()

$data = $this->anggotaRepo->scope()
              ->when($request->get('status') , function ($query) use ($request) {
                            $query->where('status_id', $request->get('status'));
                        })
              ->when($request->get('nik') , function ($query) use ($request) {
                            $query->where('nik', $request->get('nik'));
                        })
              ->when($request->get('anggota') , function ($query) use ($request) {

                        $query->where('id', $request->get('anggota'));
                    })->get();