Datatables using laravel-datatables-oracle getting Ajax error

Question

I am using http://datatables.net/and https://github.com/yajra/laravel-datatables-oracle. When I try search I get a popup with an "Ajax error" I seemed to have narrowed it down to being an issue when ever there is a join in the query.

Here is method used for the Ajax request (removed fields for simplicity):

$leads = Lead::leftJoin('lead_status', 'leads.status', '=', 'lead_status.id')
 ->select('leads.id', 'leads.email', 'lead_status.status');

This works fine as I said it is only when I try search do I get the error, I don't get it when sorting or paging through results.

When there is no join in the query then I don't get any issues.

Been on this for a while now and not sure what to do..

Surely this has nothing to do with datatables - the problem is related to your database query. Can you debug the results and compare them? — markpsmith

yajra yajra · Accepted Answer · 2015-05-08T12:09:14

When using a join statement, you have to specify in js the fully qualified name of field. You should use table.column pattern. See example code below:

$('#posts-table').DataTable({
        processing: true,
        serverSide: true,
        ajax: 'joins-data',
        columns: [
            {data: 'id', name: 'posts.id'},
            {data: 'title', name: 'posts.title'},
            {data: 'name', name: 'users.name'},
            {data: 'created_at', name: 'posts.created_at'},
            {data: 'updated_at', name: 'posts.updated_at'}
        ]
    });

And on your controller

public function getJoinsData()
    {
        $posts = Post::join('users', 'posts.user_id', '=', 'users.id')
            ->select(['posts.id', 'posts.title', 'users.name', 'users.email', 'posts.created_at', 'posts.updated_at']);

        return Datatables::of($posts)
            ->editColumn('title', '{!! str_limit($title, 60) !!}')
            ->editColumn('name', function ($model) {
                return \HTML::mailto($model->email, $model->name);
            })
            ->make(true);
    }

Datatables using laravel-datatables-oracle getting Ajax error

1 Answers