Sorting by eloquent relationship in Laravel using DataTables

I’m trying to use DataTables to display list of Application model. Application model has operations:

public function operations(){
    return $this->hasMany('AppOperation', 'application_id', 'id');
}

Operations model has state:

public function state(){
    return $this->belongsTo('AppState', 'status_id', 'id');
}

and operation author (user):

public function user(){
    return $this->hasOne('AppUser', 'id', 'user_id');
}

To enable datatable i’m using this code:

                        $(function() {
                        $('#applications-table').DataTable({
                            language: {
                                "url": "/js/Polish.json"
                            },
                            processing: true,
                            serverSide: true,
                            ajax: '{!! route('applications.data') !!}',
                            columns: [
                                { data: 'number', name: 'number', className: "vertical_middle", searchable: true},
                                { data: 'operations.0.correspondence', name: 'operations.correspondence', className: "vertical_middle", orderable: false, searchable: false},
                                { data: 'operations.0.comment', name: 'operations.comment', className: "vertical_middle", orderable: false, searchable: true},
                                { data: 'operations.0.prognosed', name: 'operations.prognosed', className: "vertical_middle", searchable: false},
                                { data: 'operations.0.state.name', name: 'operations.state.name', className: "vertical_middle", searchable: false},
                                { data: 'operations.0.created_at', name: 'operations.created_at', className: "vertical_middle", type: "date", searchable: true},

                            ]
                        });

                    });

ApplicationController is serving data using this code

        $model = Application::with(array(
        'operations' => function ($query) {
            $query->orderByDesc('operations.created_at')->with('state')->with('user');
        }
    ));

    return Datatables::of($model)->make(true);

And finally a problem – table is rendering properly, but when I try to sort by another column than the first one I receive warning:

DataTables warning: table id=applications-table – Requested unknown parameter ‘operations.0.correspondence’ for row 0.

Honestly, I have no idea how to make it work. Thank you in advance.

Answer

Fixed by using DB::table

    $applications = DB::table('applications as a')
    ->join('operations as o', function($join){
        $join->on('o.application_id', '=', 'a.id')
            ->on('o.id', '=', DB::raw("(SELECT max(id) from operations WHERE operations.application_id = a.id)"));
    })
    ->join('users as u', 'o.user_id', '=', 'u.id')
    ->join('states as s', 'o.status_id', '=', 's.id')
    ->select(['a.id as a_id','a.number','o.*', 'u.name as u_name', 'u.surname as u_surname', 's.name as s_name']);

return Datatables::of($applications)->make(true);

Advertisement

Answer