Laravel Datatables order by relationship column does not work
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>
Mark
Updated on June 05, 2022Comments
-
Mark almost 2 years
I have one
User
who can be assigned to manyCompany
. 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 columncompany_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, });