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

miken32 · Accepted Answer · 2021-09-02 20:28:09Z

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>

miken32 · Accepted Answer · 2021-09-02 20:28:54Z

0

This works for me

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

edited Sep 2, 2021 at 20:28

miken32

42.5k16 gold badges127 silver badges177 bronze badges

answered Aug 22, 2020 at 0:09

Umar Farooq

405 bronze badges

1 Comment

CodingEra Over a year ago

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/…

Collectives™ on Stack Overflow

Laravel Datatables order by relationship column does not work

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related