Laravel - Yajra Datatable sorting and searching for relationship column

Question

I am struggling to make sorting and searching for working on the relationship columns in Laravel/Yajra Datatables.

I have a Metro model that has a relationship with Station. Here is my code.

metros table columns

id, name, number, origin_id, destination_id, created_at, updated_at

stations table columns

id, name, code, info, photo, created_at, updated_at

Metro Model

/**
 * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
 */
public function origin()
{
    return $this->belongsTo(Station::class, 'origin_id');
}

/**
 * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
 */
public function destination()
{
    return $this->belongsTo(Station::class, 'destination_id');
}

MetroController JSON

/**
 * DataTable json data request
 *
 * @param \App\Metro $metro
 *
 * @return mixed
 * @throws \Exception
 */
public function jsonData(Metro $metro)
{
    return DataTables::of($metro::select('id', 'name', 'number', 'origin_id', 'destination_id', 'created_at', 'updated_at'))
                     ->addColumn('action', function ($metro) {

                         $show    = route('admin.metros.show', $metro);
                         $edit    = route('admin.metros.edit', $metro);
                         $destroy = route('admin.metros.destroy', $metro);
                         $attr    = 'onclick=metroDelete(' . $metro->id . ')';

                         return view('admin.components.action', compact([
                             'show',
                             'edit',
                             'destroy',
                             'attr',
                         ]));

                     })
                     ->editColumn('origin_id', function ($metro) {
                         return '<a href="'.route('admin.stations.show', $metro->origin->id).'">' . $metro->origin->name . '</a>';
                     })
                     ->editColumn('destination_id', function ($metro) {
                         return '<a href="'.route('admin.stations.show', $metro->destination->id).'">' . $metro->destination->name . '</a>';
                     })
                     ->editColumn('created_at', function ($metro) {
                         return $metro->created_at->diffForHumans();
                     })
                     ->escapeColumns([])
                     ->make(TRUE);
}

View - Javascript

$(document).ready(function () {

    let metroDataTable = $('#metroDataTable');

    metroDataTable.DataTable({
        serverSide  : true,
        processing  : true,
        autoWidth   : false,
        ajax        : '{!! route('admin.metros.json') !!}',
        lengthMenu  : [[25, 50, 75, 100, -1], [25, 50, 75, 100, "All"]],
        columns     : [
            {data: 'id', name: '{{__('admin.metro.id')}}', width: '50px'},
            {data: 'name', name: '{{__('admin.metro.name')}}', width: '100px'},
            {data: 'number', name: '{{__('admin.metro.number')}}', width: '100px'},
            {data: 'origin_id', name: '{{__('admin.metro.origin')}}', width: '200px'},
            {data: 'destination_id', name: '{{__('admin.metro.destination')}}', width: '200px'},
            {data: 'created_at', name: 'created_at'},
            {
                data      : 'action',
                name      : '{{__('admin.action')}}',
                orderable : false,
                searchable: false,
                className : 'text-center'
            },
        ],
        initComplete: function () {
            this.api().columns().every(function () {
                var column = this;
                var input  = document.createElement("input");
                $(input).appendTo($(column.footer()).empty())
                    .on('change', function () {
                        column.search($(this).val(), false, false, true).draw();
                    });
            });
        }

    }); // end of DataTables

}); // end of jQuery Document

When I try to sort the Origin or Destination column or search for the column it is giving me the following error.

Question:

How can I make 'originanddestination` columns sortable and searchable?

Answer 1

You don't need to translate the name in the columns option, this value is only used by the dataTables API, so you should not translate it:

Change

{data: 'origin_id', name: '{{__('admin.metro.origin')}}', width: '200px'},

to

// If data and name are the same you can remove the name altogether
{data: 'origin_id', width: '200px'},

In order to allow filtering / sorting on the origin name you would have to do the following.

Eager load the relationship in your controller:

// Not sure why are passing a model instance here
return DataTables::of($metro::with('origin', 'destination')

// This should be the same but in my opinion cleaner
return DataTables::of(Metro::with('origin', 'destination')

Change the name attribute in the columns option:

{data: 'origin_id', name: 'origin.name', width: '200px'},

You can read more about relationships for dataTables here.