DataTables warning: table id=datauser - Exception Message: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.nama' in 'where clause' (SQL: select count(*) as aggregate from (select '1' as
row_countfromusersleft jointb_operatorontb_operator.user_id=users.idleft jointb_mahasiswaontb_mahasiswa.user_id=users.idwhere (LOWER(users.username) LIKE %k% or LOWER(users.nama) LIKE %k% or LOWER(users.role) LIKE %k% or LOWER(users.updated_at) LIKE %k%) andusers.deleted_atis null) count_row_table)
I got the field 'nama' by IFNULL two table columns into one (AS nama), but weirdly DataTable search searching nama in the table users... the ordering and paginating working fine though. How do I fix DataTable search so it can look up 'nama' instead of 'users.nama'?
Controller:
$table = User::select(\DB::RAW('users.id, users.username, IFNULL(tb_operator.nama_operator,tb_mahasiswa.nama_mahasiswa) AS nama, users.role, users.updated_at'))
->join('tb_operator', 'tb_operator.user_id', '=', 'users.id', 'left')
->join('tb_mahasiswa', 'tb_mahasiswa.user_id', '=', 'users.id', 'left');
DataTable:
var datauser = $('#datauser').DataTable({
processing: true,
serverSide: true,
ajax: '{{ action('UserC@data') }}',
columns: [
{ data: 'DT_RowIndex', orderable: false, searchable: false },
{ data: 'username', name: 'users.username' },
{
data: 'nama',
render: function (data) {
return data;
}
},
{
data: 'role',
name: 'users.role',
render: function (data) {
badge = 'danger';
if (data == 'mahasiswa') {
badge = 'info';
} else if(data == 'publik') {
badge = 'warning';
} else if(data == 'operator') {
badge = 'success';
}
return '<span class="badge badge-' + badge + '">' + data + '</span>';
}
},
{ data: 'last_update', name: 'users.updated_at'},
{
data: 'action',
name: 'action',
searchable: false,
orderable: false,
render: function (action) {
return '<div class="text-center">' + action + '</div>';
}
}
]
});
