Get most recent row with group by and Laravel
Solution 1
The problem is that the result set will be first grouped then ordered. You can use nested select to get what you want.
SQL Query:
SELECT t.* FROM (SELECT * FROM messages ORDER BY created_at DESC) t GROUP BY t.from
With Laravel:
$messages = Message::select(DB::raw('t.*'))
->from(DB::raw('(SELECT * FROM messages ORDER BY created_at DESC) t'))
->groupBy('t.from')
->get();
You just need to add your where()
clauses.
Solution 2
You may replace groupBy with distinct, as it works in my case.
$messages = Message::where('to', Auth::id())
->orderBy('created_at', 'DESC')
->distinct('from')
->paginate(10);
Hope this helps.
Solution 3
If anyone is still looking for a short and Eloquent answer for using groupBy()
with latest()
just add unique()
after get()
like this...
$unreadMessages = Message::where('receiver_id', Auth::user()->id)
->where('read_at', null)
->latest()
->get()
->unique('user_id');
Solution 4
To get most recent record for each from
you can use a self join
DB::table('message as m')
->select('m.*')
->leftJoin('message as m1', function ($join) {
$join->on('m.from', '=', 'm1.from')
->whereRaw(DB::raw('m.created_at < m1.created_at'));
})
->whereNull('m1.from')
->orderBy('m.created_at', 'DESC')
->paginate(10);
In SQL it will look like
select m.*
from message m
left join message m1 on m.from = m1.from
and m.created_at < m1.created_at
where m1.from is null
order by m.created_at desc
mattesj
Updated on July 20, 2022Comments
-
mattesj almost 2 years
Even though there are multiple questions like this I can't get my query to return the row with the most recent date with a group by.
I have the following table..
| message_id | from | to | created_at | status ---------------------------------------------- | 1 | 1 | 2 | 2017-04-06 | 1 | 2 | 1 | 2 | 2017-04-07 | 0 | 3 | 3 | 4 | 2017-04-06 | 1 | 4 | 3 | 4 | 2017-04-07 | 0 ----------------------------------------------
and I'm tryin to get the rows with most recent date.
| message_id | from | to | created_at | status ---------------------------------------------- | 2 | 1 | 2 | 2017-04-07 | 0 | 4 | 3 | 4 | 2017-04-07 | 0
Currently this query returns the rows with the last recent date.
$messages = Message::where('to', Auth::id()) ->groupBy('from') ->orderBy('created_at', 'DESC') ->paginate(10);
-
Neel almost 6 yearsThis is the best answer after seeing all other answers in SO on similar questions.
-
Ivanka Todorova almost 4 years@YubarajShrestha, are you getting any kind of errors or the result you're expecting is not correct?
-
Yubaraj Shrestha almost 4 yearsEvery time I use a group by it returns the oldest one, all I want is the latest. There's no error.
-
Ivanka Todorova almost 4 years@YubarajShrestha can you post your query?
-
Daddi Al Amoudi about 3 yearsthis is the best answer for my similar question.
-
Maneesh Rao almost 3 yearsQuery looks great. but when I tried to add where condition above leftJoin statement. it is not giving me any result. any help?
-
M Khalid Junaid almost 3 years@ManeeshRao Can you please ask a new question and elaborate your issue with relevant details, as in comments I cannot suggest without seeing the original code or condition, thanks
-
Guido Rus about 2 yearsUnique is a collection method and not a querybuilder method. This is highly inefficient.
-
Shadow almost 2 yearsThis solution is against the sql standard and will fail if only full group by sql mode is set, which has been the default setting for years!
-
Albert Ruelan almost 2 yearsThis is the best answer for heavy duty queries. Thanks bud