Error when searching one of the columns in DataTable (serverSide: true)

Viewed 196

enter image description here

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_count from users left join tb_operator on tb_operator.user_id = users.id left join tb_mahasiswa on tb_mahasiswa.user_id = users.id where (LOWER(users.username) LIKE %k% or LOWER(users.nama) LIKE %k% or LOWER(users.role) LIKE %k% or LOWER(users.updated_at) LIKE %k%) and users.deleted_at is 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>';
                } 
            }
        ]
    });
0 Answers
Related