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

Question

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 :)

Akhzar Javed · Accepted Answer · 2020-11-30 05:56:20Z

2

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

answered Nov 30, 2020 at 5:56

Akhzar Javed

6246 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Andre Over a year ago

thanks for your answer, but this "->select" only select columns inside the location table

Akhzar Javed Over a year ago

i have used yajra datatable with relations and if i sort by relation column i get error most of the time because the ID becomes ambiguous for the datatable. using the select statement solves the problem for me.

Andre Over a year ago

ok, I added a "-> select ('id');" but now i get another error "trying to get property" businessTypeDescriptions' of non-object ", and if I remove it it works again

Akhzar Javed Over a year ago

you needed to add your main tablename.* Example User::with('posts')->select('users.*')

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related