Laravel Datatables order by relationship column does not work

Question

I have one User who can be assigned to many Company. I'm trying to render a table using Laravel Datatables & jQuery Datatables. It renders nicely and when clicking on the order icon in the table header, it sorts data by that column, except, it doesn't work for the relationship column company_name. This is my code in the controller:

$users = User::with(['roles','companies'])
        ->where('users.id', '!=', Auth::id())
        ->whereHas('roles', function($q){$q->whereId(Role::ROLE_6);});

...

return Datatables::of($users)
->editColumn('company', function (User $user) {
                return $user->hasCompanies()? $user->companies->first()->company_name : trans('lang.company.not_assigned');
            })
->orderColumn('company', 'company')
->make(true);

And this is my javascript for datatables:

otable = $('#datatable_fixed').DataTable({
                "ajax": {
                    url: 'users/datatable',
                    type: 'POST',
                },
                "pageLength": 15,
                "processing": true,
                "stateSave": true,
                "serverSide": true,
                "bDestroy": true,
                columns: [
                    {data: 'first_name', name: 'first_name'},
                    {data: 'last_name', name: 'last_name'},
                    {data: 'company', name: 'company.company_name'},
                    {data: 'email', name: 'email'},
                    {data: 'status', name: 'status'},
                ],
                dom: 'Bfrtip',
                searching: false,
                "order": [[0, 'asc']],
                "autoWidth": true,
            });

Maxiemaxilla

Answer 1

I have a table called rides with multiple relationships with drivers, customers, vehicles, and companies. I needed one column that contains the concatenated value of two relationship table columns that can be sortable and searchable.

This query does the above work for me

$rides = Ride::select(['rides.*', DB::raw('CONCAT(drivers.code," - ",drivers.name) AS driver')])
    ->with(['drivers','customers','vehicles','companies'])
    ->join('drivers','rides.driver_id','=', 'drivers.id');

After this I have added below code to this ajax method of loading the data table data.

var table = $('#myTable').DataTable({
    "processing":true,
    "serverSide":true,
    "ajax": "{{route('ajax.view.rides')}}",
    "columns": [
        {data: 'driver', name: 'driver', searchable:false},
        {data: 'drivers.code', name:'drivers.code', searchable: true, sortable : true, visible:false},
        {data: 'drivers.name', name:'drivers.name', searchable: true, sortable : true, visible:false},
       
    ],
    responsive:true,
    order:[0,'desc']
});

The concatenated column that we have added on our query make the sorting possible

{data: 'driver', name: 'driver', searchable:false},

This column definition will make the searching possible with the both columns

{data: 'drivers.code', name:'drivers.code', searchable: true, sortable : true, visible:false},
{data: 'drivers.name', name:'drivers.name', searchable: true, sortable : true, visible:false},

Now you need to add two extra <th> tags inside the HTML table thread element.

<thead>
  <tr>
    <th>Driver</th> <!-- this one will display the concatenated column -->
    <th>Driver</th> <!-- this one is for the hidden hidden column that enables the search on one column -->
    <th>Driver</th> <!-- this one is for the hidden hidden column that enables the search on one column -->
  </tr>
</thead>

Malvie

Answer 2

This works for me

$model = Expenses::with('employee')
    ->where('bookingoffice_id', Auth::user()->bookingoffice)
    ->where('capitalsmb.expenses.active', 1)
    ->select('expenses.*');

Stoush

I faced an issue, adding ->select('expenses.*') will also create an N+1 problem which will start showing the same data multiple times, also mentioned in this comment, github.com/yajra/laravel-datatables/issues/… – Affix