Laravel-datatables: Ordering on columns from related model not working

[myshootko]

Summary of problem or feature request

I am quite new in Laravel and I am not able to make work sorting for the column loaded from 2nd level relationship using this plugin as a service. Can you please help?

Code snippet of problem

public function query(Message $model)
    {
        return $model->newQuery()->with(['recipients.user', 'sender']);
    }

return datatables()
            ->eloquent($query)
->editColumn('message.sender', function(Message $message) {
                return $message->sender->first_name . ' ' . $message->sender->last_name;
            })
            ->filterColumn('message.sender', function ($query, $keyword) {
                $query->whereHas("sender", function ($q) use ($keyword) {
                    $q->whereRaw('(first_name LIKE ? OR last_name LIKE ? OR email LIKE ?)', ["%$keyword%", "%$keyword%", "%$keyword%"]);
                });
            })
            ->orderColumn('message.sender', function ($query, $order) {
                $query->with(['sender' => function($q) use ($order) {
                    $q->orderByRaw('CONCAT(first_name, " ", last_name) ' . $order);
                }]);
            })

message.sender is belongsTo relationship to User model. Even though it is not throwing any error or exception, it is not sorting the message.sender column.

System details

• Operating System - Windows 10
• PHP Version - 7.4.6
• Laravel Version - 7
• Laravel-Datatables Version - yajra/laravel-datatables 1.5.0 (yajra/laravel-datatables-oracle 9.10.2)

[yajra]

I think you don't need to override the ordering since the package can handle belongsTo relationship. Try removing:

->orderColumn('message.sender', function ($query, $order) {
                $query->with(['sender' => function($q) use ($order) {
                    $q->orderByRaw('CONCAT(first_name, " ", last_name) ' . $order);
                }]);
            })

And on your columns, use {name: 'sender.first_name', data: 'sender.name'}

See https://datatables.yajrabox.com/relation/belongs-to for ref.

[myshootko]

Hi Yajra,

I am using your plugin "as a service", so I am defining those columns like this:

Column::make('message.sender')->title('Sender'),

When I remove the orderColumn method call, I get this error:

Exception Message:↵↵SQLSTATE[42S22]: Column not found: 1054 Unknown column 'message.sender' in 'order clause' (SQL: select * from messages where messages.deleted_at is null order by message.sender asc limit 25 offset 0)

BUT

I found somewhere on the internet that there were some issues with sorting when you used eager loading in Laravel and that hack for this should be usage of direct joins instead of "with" method call. So I did following:

->orderColumn('message.sender', function ($query, $order) { $query->select('messages.*', 'users.last_name') ->leftJoin('users', 'users.id', 'messages.sender_id') ->orderBy('users.last_name', $order); })
and now it works properlyl.

[yajra]

Glad you sorted this out. The most stable solution is indeed to use join statements. The magic on relation sometimes fails and behind the scene, the package still do join statements for it. :)

[myshootko]

Anyways, thanks for the hints and all your great work on this plugin.