How to get the latest rows for each user ID in laravel?

Question

id  user_id  name    qty     datetime
--- ---------  ----    ----    -----------
1   1           a      5       2019-12-01 12:26:01
2   2           b      3       2019-12-13 12:26:02
3   1           c      4       2019-12-13 12:26:03
4   2           a      2       2019-12-25 12:26:04
5   1           c      2       2019-12-21 12:26:06

i Want the this data

id  user_id  name    qty     datetime
--- ---------  ----    ----    -----------
5   1           c      2       2019-12-21 12:26:06
4   2           a      2       2019-12-25 12:26:04

using laravel and also if possible then what will be the sql query for it

Answer 1

Models:

Users: id, name, email, etc...
Orders: user_id, qty, name, datetime etc..

Model Query:

Orders::orderBy('datetime', 'desc')->get()->unique('user_id');

DB Query

DB::table('orders')->orderBy('datetime', 'desc')->get()->unique('user_id');

Answer 2

In pure SQL, you can filter with a correlated subquery:

select t.*
from mytable t
where t.datetime = (
    select max(t1.datetime) from mytable t1 where t1.user_id = t.user_id
)

Answer 3

or an uncorrelated subquery...

select x.*
from mytable x
join (
    select user_id, max(t1.datetime) datetime from mytable group by user_id
) y
on y.user_id = x.user_id
and y.datetime = x.datetime