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,
            });

Answer 1

I have got the same problem and I found a good solution to the problem. the problem I had is I need one column that contains the concatenated value of two relationship table columns that can be sortable, searchable. so after hours of work, I managed to build a working solution. so I will share it here for the maintain a better community. I will share the code with summarized explanation.

step 01:

I have table call rides with multiple relationships with drivers, customers, vehicles, and companies. and I wanted to create another concatenated column with drivers. name column + drivers.code column with the alias of drivers.

below 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');

step 02: after this query, most of your work is done.after this I have added below code to this ajax method of loading the data table data what this code will do the trick for the searching and sorting.

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 code 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 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>

that's it now u can search and sort nicely! happy cording for you and a good day