Eloquent eager loading default order by cause SQL error

[1212087]

Summary of problem

I'm using laravel datatable 8.x, i've create new datatable query with eager loading, but datatable return this SQL error:

{"draw":1,"recordsTotal":2,"recordsFiltered":0,"data":[],"error":"Exception Message:\n\nSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '* asc limit 10 offset 0' at line 1 (SQL: select `categories`.* from `categories` order by `categories`.* asc limit 10 offset 0)"}

As code below, i have no orderBy in my code, but Datatable render default order by 'category.*', this is my select, not order.

Code snippet of problem

JS:

let categoryListAjaxSrc = $('#tableCategoryList').data('src');
$('#tableCategoryList').dataTable({
    processing: true,
    serverSide: true,
    ajax: categoryListAjaxSrc,
    columns: [
        {
            data: 'language.title',
            name: 'language.title'
        }
    ]
});
}

PHP:

public function getCategoryDatatableByType(string $type)
{
    $user_language = Auth::user()->lang_id;
    $model = Category::with([
        'language' => function ($subQuery) use ($user_language) {
           $subQuery->where('lang_id', $user_language);
        }
    ])
    ->select('categories.*');

    return DataTables::eloquent($model)
            ->make(true);
}

System details

• Operating System: Windows 10
• PHP Version: 7.1
• Laravel Version: 5.6.25
• Laravel-Datatables Version: v8.7.0

[yajra]

It seems like the package can't handle sorting well with a complex eager loaded relations. In this regard, I suggest you try to manually select each column and check again.

// from
->select('categories.*');
// to
->select('categories.name', 'categories.id', ...');

[1212087]

let categoryListAjaxSrc = $('#tableCategoryList').data('src');
$('#tableCategoryList').dataTable({
    processing: true,
    serverSide: true,
    ajax: categoryListAjaxSrc,
    columns: [
        {
            data: 'language.title',
            name: 'language.title'
        }, {
        data: null,
        render: function () {
            return `<button class="btn btn-xs btn-danger"><i class="fa fa-trash"></i></button>`;
        },
        searchable: false
    }]
});
}

i dont no why, but i've updated js code and everything work fine now

[github-actions]

This issue is stale because it has been open for 30 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions]

This issue was closed because it has been inactive for 7 days since being marked as stale.