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

---