Laravel DataTables search in column generated with DB::raw

Question

I have the following issue in Laravel DataTables (yajra/laravel-datatables ):

I can't search (filter) in a column generated with DB::Raw.

Also I can't search with Global search function.

In my Laravel Controller:

$inputs = Input_::join('companies', 'companies.id', '=', 'inputs.company_id')
->leftjoin('inputs_details', 'inputs_details.input_id', '=', 'inputs.id')
->select(array('inputs.id as id',
    'inputs.created_at as created_at',
    'inputs.updated_at as updated_at',
    'inputs.input_at as input_at',
    'companies.name',
    'inputs.documents as documents',
    DB::raw("SUM(inputs_details.quantity*inputs_details.price_input*(1+inputs_details.VAT/100)) as input_total")
))
->groupBy('inputs.id')
->orderBy('inputs.input_at', 'DESC');

    return Datatables::of($inputs)
        ->remove_column('id')
        ->make(true);

In my View:

$('#inputs-table').DataTable({
    processing : true,
    serverSide : true,
    ajax: '{{ URL::to('employee/inputs/data/') }}',
    columns: [
        {data: 'created_at', name: 'inputs.created_at', orderable: false},
        {data: 'updated_at', name: 'inputs.updated_at', orderable: false},
        {data: 'input_at', name: 'inputs.input_at', orderable: false},
        {data: 'name', name: 'companies.name', orderable: false},
        {data: 'documents', name: 'inputs.documents', orderable: false},
        {data: 'input_total', name: 'input_total', orderable: false, searchable: true},
        {data: 'reports', name: 'reports', orderable: false, searchable: false},
        {data: 'actions', name: 'actions', orderable: false, searchable: false}
    ],
    initComplete: function () {
        this.api().columns().every(function () {
            var column = this;
            var input = document.createElement("input");
            $(input).appendTo($(column.footer()).empty())
            .on('change', function () {
                var val = $.fn.dataTable.util.escapeRegex($(this).val());
                column.search(val ? val : '', true, false).draw();
            });
        });
    }  });  

Answer 1

You have to put the filterColumn action in your datatable.

$inputs = Input_::join('companies', 'companies.id', '=', 'inputs.company_id')
    ->leftjoin('inputs_details', 'inputs_details.input_id', '=', 'inputs.id')
    ->select(array('inputs.id as id',
        'inputs.created_at as created_at',
        'inputs.updated_at as updated_at',
        'inputs.input_at as input_at',
        'companies.name',
        'inputs.documents as documents',
        DB::raw("SUM(inputs_details.quantity*inputs_details.price_input*(1+inputs_details.VAT/100)) as input_total")
    ))
    ->groupBy('inputs.id')
    ->orderBy('inputs.input_at', 'DESC');

return Datatables::of($inputs)
            ->remove_column('id')
            ->filterColumn('input_total', function($query, $keyword) {
                $query->whereRaw("SUM(inputs_details.quantity*inputs_details.price_input*(1+inputs_details.VAT/100)) = ?", $keyword);
            })
            ->make(true);