Datatables: how to preserve id when sorting through a relation from another table (yajra, laravel)

I’m using datatables from yajra datatables and I have a problem.

I have a datatable where I obtain certain columns from other tables, for example the column “customer” is obtained through a relation from another table

But when I press the sort by customer button, the IDs change to the ID of the table where the relation belongs

This generates errors in the buttons that I have on the right since these IDs do not exist in the “meos” table, only in the “locations” table that is in the relation

How can I make it sort while keeping the same ID it had?

I want to always keep as order criteria the ID of the table I am with, which is the “meos” table belonging to the “meo” class

This is my query

public function query()
{
    $languageId = Auth::user()->language_id;
    return Meo::with(['businessType' => function ($query) use ($languageId) {
        $query->with(['businessTypeDescriptions' => function ($subQuery) use ($languageId) {
            $subQuery->where('language_id', '=', $languageId);
        }]);
    }])->with('location');
}

this is my function getcolumns

protected function getColumns()
{
    return [
        Column::make('id')->addClass('text-center')->title(__('digestReport.columns.id')),
        Column::make('location.location_name')->addClass('text-center')->title(__('digestReport.columns.customer')),
        Column::make('businessType')->addClass('text-center')->title(__('digestReport.columns.business_type'))->searchable(false),
        Column::computed('action')->exportable(false)->printable(false)->width(160)->addClass('text-center')->title(__('digestReport.columns.actions')),

    ];

}

please, I need help.

Thanks 🙂

Answer

Try below code. Add ->select() statement

public function query()
{
    $languageId = Auth::user()->language_id;
    return Meo::with(['businessType' => function ($query) use ($languageId) {
        $query->with(['businessTypeDescriptions' => function ($subQuery) use ($languageId) {
            $subQuery->where('language_id', '=', $languageId);
        }]);
    }])->with('location')->select('meo-table.*');
}

Replace meo-table with your database table for Meo::class