Laravel Eloquent & SQL: Grouping and Selecting Rows with Maximum Date

Question

I'm facing an issue with grouping and selecting rows based on the maximum date in Laravel using Eloquent. I have a table named AlertsLog with columns id, price, and created_at. The goal is to group rows by price and select the entry with the latest created_at for each group. I've tried several approaches, including groupBy, max, and subquery, but I'm still missing some expected results.

here is what my sql dummy data looks like

[
  {"id": 1, "price": 1010, "created_at": "2023-11-09 01:22 PM"},
  {"id": 2, "price": 1010, "created_at": "2023-11-09 01:35 PM"},//the price 
  {"id": 3, "price": 1007, "created_at": "2023-11-09 01:40 PM"},//the price has changed
  {"id": 4, "price": 1007, "created_at": "2023-11-09 01:45 PM"},
  {"id": 5, "price": 1010, "created_at": "2023-11-09 01:50 PM"},//the price has changed
  {"id": 6, "price": 1015, "created_at": "2023-11-09 01:55 PM"},//the price has changed
  {"id": 7, "price": 1015, "created_at": "2023-11-09 02:00 PM"} //the latest price
]

what i want to show to is

[
 {"id":2,"price":1010,"created_at":"2023-11-09 01:35 PM"},
 {"id":4,"price":1007,"created_at":"2023-11-09 01:45 PM"},
 {"id":5,"price":1010,"created_at":"2023-11-09 01:50 PM"},
 {"id":7,"price":1015,"created_at":"2023-11-09 02:00 PM"}
]

What i have tried so far using group by:

$uniqueAlerts = DB::table('alerts_logs')
        ->whereIn('id', function ($query) {
            $query->select(DB::raw('MAX(id)'))
                ->from('alerts_logs')
                ->groupBy('price');
        })
        ->orderBy('created_at', 'desc')
        ->get();

using Unique:

$alerts = AlertsLog::orderBy('created_at', 'desc')->get();

return $uniqueAlerts = $alerts->unique('price')->sort(function ($a, $b) {
    return $b->created_at <=> $a->created_at;
})->values()->toArray();

and even using loops but they all are producing same result that include ids 4,5,7 but missing 2 ,

Thanks!

Answer 1

i found one solution with self join, but in result return 1,2,5,6 ...

see my solution, and may be can improve it:

AlertsLog::from('AlertsLog as a')
        ->leftJoin('AlertsLog as b', function ($join) {
            $join->on('a.id', '=', DB::raw('(b.id + 1)'));
        })
        ->whereColumn('a.price', '!=', 'b.price')
        //->orWhereNull('b.id')
        ->select('a.*')
        ->orderBy('a.id')
        ->get();

the mysql query is :

select `a`.*
from `AlertsLog` as `a`
         left join `AlertsLog` as `b` on `a`.`id` = (b.id + 1)
where `a`.`price` != `b`.`price`
order by `a`.`id` asc