Donations Appreciated

You have not lived today until you have done something for someone who can never repay you. - John Bunyan

Click here to lend your support to: Laravel Datatables and make a donation at pledgie.com !

FluentController.php - Advance Filtering


Custom Filter

Id Name Email # of Post Created At Updated At

FluentController.php

    public function getAdvanceFilter()
    {
        return view('datatables.fluent.advance-filter');
    }

    public function getAdvanceFilterData(Request $request)
    {
        $users = DB::table('users')->select([
                DB::raw("CONCAT(users.id,'-',users.id) as id"),
                'users.name',
                'users.email',
                DB::raw('count(posts.user_id) AS count'),
                'users.created_at',
                'users.updated_at'
        ])->leftJoin('posts','posts.user_id','=','users.id')
        ->groupBy('users.id');

        $datatables =  Datatables::of($users);
        if ($request->get('post')) {
            $datatables->having('count', $request->get('operator'), $request->get('post')); // having count search
        }

        if ($name = $request->get('name')) {
            $datatables->where('users.name', 'like', "$name%"); // additional users.name search
        }

        // Global search function
        if ($keyword = $request->get('search')['value']) {
            // override users.name global search
            $datatables->filterColumn('users.name', 'where', 'like', "$keyword%");

            // override users.id global search - demo for concat
            $datatables->filterColumn('users.id', 'whereRaw', "CONCAT(users.id,'-',users.id) like ? ", ["%$keyword%"]);
        }

        return $datatables->make(true);
    }

Javascript

    var oTable = $('#users-table').DataTable({
        processing: true,
        serverSide: true,
        ajax: {
            url: 'https://datatables.yajrabox.com/fluent/advance-filter-data',
            data: function (d) {
                d.name = $('input[name=name]').val();
                d.operator = $('select[name=operator]').val();
                d.post = $('input[name=post]').val();
            }
        },
        columns: [
            {data: 'id', name: 'users.id'},
            {data: 'name', name: 'users.name'},
            {data: 'email', name: 'users.email'},
            {data: 'count', name: 'count', searchable: false},
            {data: 'created_at', name: 'users.created_at'},
            {data: 'updated_at', name: 'users.updated_at'}
        ]
    });

    $('#search-form').on('submit', function(e) {
        oTable.draw();
        e.preventDefault();
    });