laravel group by fetch latest record from table

Question

I have one table where I want to retrieve data group by the user but I want the latest entry in result how can I do that by using eloquent.

here is an eloquent query I am using.

Product::whereNotNull('user_id')
         ->orderBy('id','desc')
         ->groupBy('user_id')
         ->get();

here is my table

Id   Name   user_id
-------------------------
1    A       1
2    b       1
3    c       2
4    d       2
5    e       3
6    f       3

result my query is giving me

Id   Name   user_id
-------------------------
1    A       1
3    c       2
5    e       3

result i want

Id   Name   user_id
-------------------------
2    b       1
4    d       2
6    f       3

Possible duplicate of [Order By before Group By using Eloquent (Laravel)](https://stackoverflow.com/questions/31857961/order-by-before-group-by-using-eloquent-laravel) — Dilip Hirapara, Nov 06 '19 at 12:20

score 2 · Accepted Answer · answered Nov 06 '19 at 12:18

Product::whereRaw('id IN (select MAX(id) FROM products GROUP BY user_id)')
         ->whereNotNull('user_id')
         ->orderBy('id','desc')
         ->get();

You will need a nested query for that, i don't think you can avoid it but this solution should work.

GroupBy happens before OrderBy so you have to get your last record before you do your ordering

Yasin Patel · Answer 2 · 2019-11-06T12:34:55.857

1

Try this :

$subquery = Product::orderBy('id','DESC');

$products = DB::table(DB::raw("({$subquery->toSql()}) as subquery"))
   ->whereNotNull('user_id')
   ->groupBy('user_id')
   ->get();

2nd way : use unique() method in collection (The unique method returns all of the unique models in the collection):

$products = Product::whereNotNull('user_id')
             ->orderBy('id','desc')
             ->get()
             ->unique('user_id');

edited Nov 06 '19 at 12:34

answered Nov 06 '19 at 12:14

Yasin Patel

5,624
8
31
53

hey, Yasin, your first solution worked but not returned eloquent object and the second solution is not working...... – Jigar Nov 06 '19 at 12:32

score 0 · Answer 3 · answered Nov 06 '19 at 12:27

Check out Laravel Docs

latest / oldest
The latest and oldest methods allow you to easily order results by date. By default, result will be ordered by the created_at column. Or, you may pass the column name that you wish to sort by:

$product = Product::latest()->first();

laravel group by fetch latest record from table

3 Answers3