user3143952 user3143952 - 3 months ago 12
MySQL Question

Laravel Datatables Sorting Appended Fields

My Model

Items
is related to
Rooms
, which is related to
Buildings
, which is related to
Locations
.

Short:
Items
belongsTo
Rooms
belongsTo
Buildings
belongsTo
Locations


At the index function of the
ItemController
I want to show a table of
Items
. I use Laravel Datatables. It works for 'simple' tables, but I ran into the problem of sorting/searching custom/appended fields, because they are not in the table of course.

Basically I want to join the
Room
,
Building
and
Location
Name for each
Item
in the displaying table.

This is my
Items
Model:

protected $appends = [
'building_title',
'location_title',
'room_title',
];

public function getLocationTitleAttribute() {
return $this->room->building->location->title;
}

public function getBuildingTitleAttribute() {
return $this->room->building->title;
}

public function getRoomTitleAttribute() {
return $this->room->title;
}


This is my Controller:

public function anyData()
{
return Datatables::of(Item::query())->make(true);
}


What is the best approach to enable sorting/filtering for appended fields?
Thank you for reading.

Answer

This is my solution (no appended fields were used):

public function anyData()
{
    $items = Item::join('rooms', 'items.room_id', '=', 'rooms.id')
        ->join('buildings', 'rooms.building_id', '=', 'buildings.id')
        ->join('locations', 'buildings.location_id', '=', 'locations.id')
        ->select([
            'items.id',
            'items.title',
            'items.label_number',
            'items.fibu_number',
            'rooms.title as room_title',
            'buildings.title as building_title',
            'locations.title as location_title'
        ]);
    return Datatables::of($items)->make(true);
}