datatables filter not working

[t0n1zz]

so i am using laravel 5.4 and using datatables ajax with my eloquent is have join table and so on, now i want to do searching/filter it just not working got internal server errror 500 any idea why?

here is my controller code`

public function index()
    {
        try{
            return view('admins.'.$this->kelaspath.'.index');
        }catch (Exception $e){
            return Redirect::back()->withInput()->with('errormessage',$e->getMessage());
        }
    }

    public function data_kegiatan()
    {
        $data = Kegiatan::with('tempat','sasaranhub.sasaran','prasyarat.kegiatan','total_peserta');
        $datatable = Datatables::of($data)
        ->editColumn('kota', function($data){
            return $data->tempat ? $data->tempat->kota : $data->kota;
        })
        ->editColumn('tempat', function($data){
            return $data->tempat ? $data->tempat->name : '';
        })
        ->editColumn('tanggal', function($data){
            return $data->tanggal ? with(new Date($data->tanggal))->format('d F Y') : '';
        })
        ->editColumn('tanggal2', function($data){
            return $data->tanggal2 ? with(new Date($data->tanggal2))->format('d F Y') : '';
        })
        ->editColumn('status', function($data){
            if($data->status == 1){
                $status = '<a class="btn btn-default btn-sm nopointer"><i class="fa fa-pause"></i> <span class="hidden-xs">MENUNGGU</span></a>';
            }elseif($data->status == 2){
                $status = '<a class="btn btn-warning btn-sm nopointer"><i class="fa fa-circle-o"></i> <span class="hidden-xs">PENDAFTARAN TERBUKA</span></a>';
            }elseif($data->status == 3){
                $status = '<a class="btn btn-warning btn-sm disabled"><i class="fa fa-ban"></i> <span class="hidden-xs">PENDAFTARAN TERTUTUP</span></a>';
            }elseif($data->status == 4){
                $status = '<a class="btn btn-primary btn-sm disabled"><i class="fa fa-dot-circle-o"></i> <span class="hidden-xs">BERJALAN</span></a>';
            }elseif($data->status == 5){
                $status = '<a class="btn btn-primary btn-sm nopointer"><i class="fa fa-times"></i> <span class="hidden-xs">TERLAKSANA</span></a>';
            }elseif($data->status == 6){
                $status = '<a class="btn btn-danger btn-sm nopointer"><i class="fa fa-times"></i> <span class="hidden-xs">BATAL</span></a>';
            }else{
                $status = "-";
            }
            return $status;
        })
        ->editColumn('sasaranhub', function($data){
            $sasaran = '';
            if(!empty($data->sasaranhub)){
                foreach ($data->sasaranhub as $sr) {
                    $sasaran .= '<a class="btn btn-info btn-xs nopointer marginbottom" >' . $sr->sasaran->name . '</a> ';
                }
            }
            return $sasaran;
        })
        ->editColumn('prasyarat', function($data){
            $prasyarat = '';
            if(!empty($data->prasyarat)){
                foreach ($data->prasyarat as $pr) {
                    $prasyarat .= '<a class="btn btn-info btn-xs nopointer marginbottom">' . $pr->kegiatan->kode . ' - ' . $pr->kegiatan->name . '</a> ';
                }
            }
            return $prasyarat;
        })
        ->addColumn('durasi',function($data){
            $mulai = new \Carbon\Carbon($data->tanggal);
            $startTimeStamp = strtotime($mulai->subDays(1));
            $endTimeStamp = strtotime($data->tanggal2);
            $timeDiff = abs($endTimeStamp - $startTimeStamp);
            $numberDays = $timeDiff/86400;
            $numberDays = intval($numberDays);

            return $numberDays;
        })
        ->rawColumns(['sasaranhub', 'prasyarat','status'])->make(true);


        return $datatable;   
    }

and here is it my datatable jquery

var table = $('#dataTables-example').DataTable({
        dom: '<"top"B>t<"bottom"p>',
        processing: true,
        serverSide: true,
        ajax: '{!! route('admins.kegiatan.data_kegiatan') !!}',
        responsive:{
            details:{
                type: 'column',
                target: -1
            }
        },
        columns: [
            { data: 'id', name: 'kegiatan.id', visible : false, searchable : false },
            { data: 'status', name: 'kegiatan.status',visible : false, searchable : false  },
            { data: 'name', name: 'kegiatan.name', className: 'warptext'},
            { data: 'kota', name: 'kegiatan.kota'},
            { data: 'tempat', name: 'kegiatan.tempat',className: 'warptext'},
            { data: 'tanggal', name: 'kegiatan.tanggal'},
            { data: 'tanggal2', name: 'kegiatan.tanggal2' },
            { data: 'durasi', name: 'durasi', render: function(data){
                return data ? data + ' hari' : '-';
            }},
            
            { data: 'status', name: 'kegiatan.status'},
            { data: 'sasaranhub', name: 'kegiatan.sasaranhub' },
            { data: 'prasyarat', name: 'kegiatan.prasyarat', render:function(data){
                return data ? data : '-';
            } },
            
            { data: 'min', name: 'kegiatan.min' },
            { data: 'max', name: 'kegiatan.maks' },
            { data: 'max', name: 'kegiatan.terdaftar'},
            { data: null, orderable: 'false', searchable: 'false', className:'control', defaultContent: ''}
        ],
        buttons: [
            {
                extend:'colvis',
                columns: ':gt(1)',
                text: '<i class="fa fa-table"></i>'
            },
            {
                extend:'colvisGroup',
                text: 'Semua',
                hide: [0,1],
                show: ':hidden'
            }
        ],
        language: {
            buttons : {},
            select:{
                rows:{
                    _: "",
                    0: "",
                    1: ""
                }
            },
            "sProcessing":   "Sedang proses...",
            "sLengthMenu":   "Tampilan _MENU_ entri",
            "sZeroRecords":  "Tidak ditemukan data yang sesuai",
            "sInfo":         "Tampilan _START_ sampai _END_ dari _TOTAL_ entri",
            "sInfoEmpty":    "Tampilan 0 hingga 0 dari 0 entri",
            "sInfoFiltered": "(disaring dari _MAX_ entri keseluruhan)",
            "sInfoPostFix":  "",
        }
    });
    $('#searchtext').keyup(function(){
        table.search($(this).val()).draw() ;
    });

and also here is my table html

<div class="input-group tabletools">
	<div class="input-group-addon"><i class="fa fa-search"></i></div>
	<input type="text" id="searchtext" class="form-control" placeholder="Kata kunci pencarian...">
</div>
<table class="table table-hover" id="dataTables-example" cellspacing="0" width="100%">
	<thead class="bg-light-blue-active color-palette">
	<tr>
		<th hidden>id</th>
		<th hidden>status</th>
		<th data-priority="1">Nama </th>
		<th>Kota</th>
		<th>Tempat</th>
		<th class="sort" data-priority="4">Mulai</th>
		<th data-priority="3">Selesai</th>
		<th>Durasi</th>
		<th data-priority="2">Status</th>
		<th class="none">Sasaran</th>
		<th class="none">Prasyarat</th>
		<th class="none">Min</th>
		<th class="none">Maks</th>
		<th class="none">Terdaftar</th>
		<th></th>
	</tr>
	</thead>
</table>

[DMeganoski]

The problem is that you are adding columns to the table which do not exist in the database, and you do not have them marked searchable: false. It is trying to search those columns that do not exist. If you enable debugging, it should have said something very similar SQL ERROR: column 'example.durasi' not found (you can find this response in your browser's web tools since it is called via ajax)

[t0n1zz]

oh.. so how to add searchable: false? where do i need to add mark? welll i never use datatables with ajax and most of the times i just use DOM datatables and it search pretty much fine with the same query/eloquent like up there....

[DMeganoski]

You would define it in the javascript, where the columns are defined.

If you look at the last column defined there, it already has this property.

{ data: null, orderable: 'false', searchable: 'false', className:'control', defaultContent: ''}

You would add searchable:false (you probably want to disable ordering as well) property to any columns not on the database. It would still be possible to search those columns with the filter() function, but you would have to handle that logic manually.