Laravel OrderBy leftJoin?

  • 0
    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 the change in payment status. I need to sort the orders table by the last related orders entry in the logs.

    For example, yesterday we changed the payment status 2 times and today 1 time, you need to sort by the date of the entry in the logs, which 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');
            }
        }


    Nevertheless, I get the results in confusion, sorting happens because the output has changed with such a scoop, but it is still not the one I expect to get. Send me in the right direction please, because I'm really in some confusion ...
    Thank you!
    Laravel Arya Clayton, Jan 22, 2020

  • 1 Answers
  • 0
    It is 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 be no data, i.e. will be grouped by NULL.



    What does latest () - & gt; limit (1) do inside join conditions?



    And what is your general goal? Sort orders by last event date? Unfortunately, sorting grouping doesn't work that way. First, it groups by logs.entity_id, choosing an arbitrary entry from the logs, and then sorts the resulting lines.



    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 Laravel syntax yourself :)
    Anonymous

Your Answer
To place the code, please use CodePen or similar tool. Thanks you!