Laravel OrderBy leftJoin?

Question

Laravel

Good evening! Guys, I have a table with orders, there is also a table with logs in which we store all sorts of different things. Including the date of change of payment status. I need to sort the orders table by the last logs entry related to orders.

For example, yesterday they changed the payment status 2 times and today 1 time, you need to sort by the date of the entry in the logs that was made today. There is a small scope for this, in general, I did everything based on the laravel documentation.

public function scopeFilteredByPStatus($query)
    {
        if (request()->get('pstatus')) {
            return $query->select('orders.*')
                ->leftJoin('logs', function ($join) {
                    return $join->on('orders.id', '=', 'logs.entity_id')->latest()->limit(1);
                })
                ->groupBy('logs.entity_id')
                ->where('orders.payment_status', request()->get('pstatus'))
                ->orderBy(DB::raw('logs.created_at'), 'DESC');
        }
    }

However, I get mixed results, sorting happens because the output has changed with such a scope, but it is still not what I expect to get. Point me in the right direction please, because I'm really confused...
Thank you!

Answer 1

It's a little unclear why you are doing a GROUP BY on a table that is added via a LEFT JOIN. After all, LEFT JOIN means that there may not be data, i.e. will group by NULL.
What does latest()->limit(1) do inside join'a conditions?
And what is your overall goal? Sort orders by last event date? Unfortunately, grouping and sorting don't work that way. It first groups by logs.entity_id, selecting an arbitrary entry from logs, and then sorts the resulting rows.
You need something like this:

SELECT orders.*
FROM orders
LEFT JOIN (
    SELECT entity_id, MAX(created_at) created_at
    FROM logs
    GROUP BY entity_id
) t ON orders.id = t.entity_id
ORDER BY t.created_at DESC

Shift to the Laravel syntax yourself :)