Laravel Yajra Datatables searchable not working on desire column

Question

I have datatables column as such:

var showMasterUserTable = function () {
        masterIcon = $('#master_user_table').DataTable({
            processing: true,
            serverSide: true,
            responsive: true,
            ajax: {
                url: ROOT + 'master-voucher-bit-x/data',
            },
            columns: [
                {
                    data: 'DT_RowIndex',
                    name: 'DT_RowIndex',
                    searchable: false,
                    orderable: false
                },
                {
                    data: 'voucher_code',
                    name: 'voucher_code',
                },
                {
                    data: 'status',
                    name: 'status',
                },
                {
                    data: 'track',
                    name: 'track',
                },
                {
                    data: 'user_use',
                    name: 'user_use',
                    orderable: true
                },
                {
                    data: 'progress',
                    name: 'progress',
                },
                {
                    data: 'quiz_score',
                    name: 'quiz_score',
                },
                {
                    data: 'urlQr',
                    name: 'urlQr',
                }
            ]
        });
    };

As long as i know from the yajra and datatables docs that searchable and orderable is default to be true when it remains unwritten, i have encounter the issue where searchable only search for voucher_code column no matter if i set all the searchable to true. I want to search for user_use column instead. If i set all the searchable to false, the table data cannot be loaded. How should i overcome it? Here's my controller code:

    {
        $model =  VoucherBitX::select('voucher_bit_x.*', 'users.email')
        ->join('users', 'voucher_bit_x.user_id', '=', 'users.id')
        ->orderBy("voucher_bit_x.redeem_at","DESC");

        return DataTables::of($model)
                ->addColumn('status', function ($data) {
                    if($data->status > 0)
                        $status = '<div><span class="badge badge-success"> Available </span></div>';
                        else
                            $status = '<div><span class="badge badge-danger"> Not Available </span></div>';
                    return $status;
                })
                ->addColumn('urlQr', function ($data) {
                    $user = UserApp::find($data->user_id);
                    $a = "";
                    if(!empty($user) && isset($user->ref_id)){
                        $quiz = QuizScore::where("track_id",$data->track_id)->where("user_id",$data->user_id)->first();
                        if($quiz && $quiz->status){
                            $track = Track::find($data->track_id);
                            $urlQr = 'https://xxx.id/api/certificate/'.base64_encode(json_encode(["user_id"=>$user->id,"slug"=>$track->slug,"track_id"=>$track->id]));
                            $a = '<a href="'.$urlQr.'">Download Certificate</a>';
                        }
                    }
                    return $a;
                })
                ->addColumn('quiz_score', function ($data) {
                    $score = 0;
                    $quiz = QuizScore::where("track_id",$data->track_id)->where("user_id",$data->user_id)->first();
                        if($quiz){
                            $score = $quiz->score;
                        }
                    return $score;
                })
                ->addColumn('progress', function ($data) {
                    $progress = 0;
                    $solve = Track::userProgress($data->user_id,$data->track_id);
                        if(!empty($solve)){
                            $progress = $solve;
                        }
                    return $progress."%";
                })
                ->addColumn('user_use', function ($data) {
                    $user = UserApp::find($data->user_id);
                    if(!empty($user))
                        return $user->name." (".$user->email.")";
                    return '-';
                })
                ->addColumn('track', function ($data) {
                    $track = Track::find($data->track_id);
                    return isset($track->title)?$track->title:"";
                })->rawColumns(['quiz_score','status','user_use','track','urlQr'])
                ->addIndexColumn()->make(true);
    }

*Edit: I have realized that datatables returned a response that included used query like this:

New question: just where the hell that query json field configuration? On my eloquent query above there is no such thing as where and like query. Haven't found that things both in yajra and datatables documentation. What i want is to modify the where field to users.email instead voucher_bit_x.voucher_code

score 0 · Answer 1 · answered Sep 22 '20 at 07:17

0

use columns.searchable

Using this parameter, you can define if DataTables should include this column in the filterable data in the table. You may want to use this option to disable search on generated columns such as 'Edit' and 'Delete' buttons for example.

   $('#example').dataTable( {
     "columnDefs": 
     [
        { "searchable": false, "targets": 0 }
     ]
   });

This will disable the search of multiple columns as specified n the target. If you want multiple columns then try using

{ "searchable": false, "targets": [0,1,2] }

Where targets 0 1 and 2 are number of columns starting index from 0

answered Sep 22 '20 at 07:17

Deepesh Thapa

1,721
3
19
29

I think the query on controller is the one who completely messed the things.. would you like to check my edited part above? Thank you in advance – Vicky Sultan Sep 22 '20 at 08:14
What is your exact question? You asked us to check whether or not it is possible to disable certain columns from searching. And now you want us to look at your controller? Please be specific. – Deepesh Thapa Sep 22 '20 at 08:34
I'm sorry i think it was a misunderstanding. I want to search for certain column, but after i check the response, the ajax ```searchable``` is not the root cause of the problem. The problem is on the query itself, as i stated on my edited question. – Vicky Sultan Sep 22 '20 at 08:56
the where and like is part of the search function. If you type in anything in the search function datatables automatically uses like and where condition to filter out from the database. – Deepesh Thapa Sep 22 '20 at 08:58
That's it, how to modify the search function? What file that runs the configuration? – Vicky Sultan Sep 22 '20 at 09:25
They are automatically done by datatables. There is no need to modify datatables. What you can do is create a search plugin and use your version of plugin. These are very complex and could take days to implement. Whenever you do a search you have to use LIKE to filter out the records from MYSQL database - Thats what datatables did for you rather. – Deepesh Thapa Sep 22 '20 at 09:27
The thing is, i want to change the default column on WHERE query that had auto-generated by datatables (```where (LOWER(`voucher_bit_x`.`voucher_code`)```) into something else. What part should i change? On controller or ajax? – Vicky Sultan Sep 22 '20 at 09:34

Laravel Yajra Datatables searchable not working on desire column

1 Answers1