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

marioforever97

Link to test case:
Local project

Debugger code (debug.datatables.net):
var n = document.createElement('script');
n.setAttribute('language', 'JavaScript');
n.setAttribute('src', 'https://debug.datatables.net/debug.js');
document.body.appendChild(n);

Error messages shown:

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)

Description of problem:
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>';
                } 
            }
        ]
    });

allan

Hi,

I don't immediately recognise your server-side script - is that something you have written? Can you modify the SQL you are creating to match what DataTables is submitting?

Also you are using columns.name for all columns apart from the nama column. Is there a reason for that? Is your server-side script using the name property at all? If not, you could use that for the mapping.

Allan

marioforever97

is that something you have written?

Yes, is that not a server-side script? we always use those controllers and they work.
Here is the whole function:

public function data()
    {
        $userlogin      = User::getLoginInfo();

        if ($userlogin['role'] == 'operator'){
            if ($userlogin['data']['tipe_operator'] == 'jurusan'){
                $id_jurusan = $userlogin['data']['id_jurusan'];
                $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')
                    ->where('tb_operator.id_jurusan', $id_jurusan)
                    ->orWhere('tb_mahasiswa.id_jurusan', $id_jurusan);
            } else {
                $id_fakultas = $userlogin['data']['id_fakultas'];
                $arr_jurusan = Jurusan::where('id_fakultas', $id_fakultas)->pluck('id_jurusan')->toArray();
                $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')
                    ->where('tb_operator.id_fakultas', $id_fakultas)
                    ->orWhereIn('tb_operator.id_jurusan', array_unique($arr_jurusan))
                    ->orWhereIn('tb_mahasiswa.id_jurusan', array_unique($arr_jurusan));
            }
        } else {
            $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');
        }

        return Datatables::of($table)
            ->addColumn('action', function ($table) {
                return '
                        <button type="button" class="btn btn-sm btn-alt-secondary js-tooltip-enabled" onclick="userDetail(' . $table->id . ')" style="height: 20px;" data-toggle="tooltip" title="Detail">
                            <i class="fa fa-eye"></i>
                        </button>
                        <button type="button" class="btn btn-sm btn-alt-secondary js-tooltip-enabled" onclick="editUser(' . $table->id . ')" style="height: 20px;" data-toggle="tooltip" title="Edit">
                            <i class="fa fa-pencil"></i>
                        </button>
                        <button type="button" class="btn btn-sm btn-alt-secondary js-tooltip-enabled" onclick="deleteUser(' . $table->id . ')" style="height: 20px;" data-toggle="tooltip" title="Delete">
                            <i class="fa fa-times"></i>
                        </button>
                        ';
            })
            ->addColumn('last_update', function ($table) {
                $updated = $table->updated_at->diffForHumans();
                return $updated;
            })
            ->addIndexColumn()
            ->rawColumns(['action'])
            ->make(true);
    }

Can you modify the SQL you are creating to match what DataTables is submitting?

I can't unfortunately

Also you are using columns.name for all columns apart from the nama column. Is there a reason for that?

Yes I use name on columns I want to search and order, it's already implemented by previous developer and it works fine

Is your server-side script using the name property at all?

Yes I think, but the new field 'nama' might not be implemented there. I don't know what directory my project server-side script is located? Is the file truly located in the server computer or it is in my computer?

If not, you could use that for the mapping.

Welp I don't know what mapping looks like

It would be great if the SQL query I had in the controller can be implemented to columns.name too like what I do to columns.data, is there a way to do this?

allan

Yes, is that not a server-side script?

We use the terminology "server-side script" to mean any code running on the server. I was asking who wrote the library code for it, since it doesn't appear to be using a library we provide, and therefore we can't really debug it.

Worth noting that we also use the term "server-side processing" which means that the server is doing all the filtering, paging and sorting. That appears to be what you are using here (enabled by the serverSide option). Do you have tens of thousands or more rows? If not, then you shouldn't need server-side processing.

I don't know what directory my project server-side script is located? Is the file truly located in the server computer or it is in my computer?

I have no idea . I don't know your project code, so that would be something you would need to lookup in your code base.

I would suggest debugging your script by getting it to output the SQL that it is generating. From there you will hopefully be able to see how it is generating that SQL and how you can modify that generation to suit your needs.

Allan