Get latest data of a specific column using eloquent

Question

How do i get the last/latest data of each specific user_id.

Submission table

id	user_id	status	created_at	updated_at
1	3	redo	somedatetime	somedatetime => `dont want this`
2	3	redo	somedatetime	somedatetime
3	4	redo	somedatetime	somedatetime
4	5	redo	somedatetime	somedatetime

ID 2 is not a redundancy. I'm keeping track of the records. I need to grab id 2,3,4. I've tried using

Submission::where('status','redo')->distinct('user_id')->get();

but it returns all the value

UPDATE

For reference purpose here is the real structure of the table

as you can see user_id 78 has 2 redos which is 89 and 90. Im trying to get the latest/last one which is 90.

try something like this `Submission::where('status','redo')->latest()->groupBy('user_id')->get();` — Joseph, Jan 06 '21 at 11:09
are you want to get the latest record for a given user or the last record for all of the users? — Joseph, Jan 06 '21 at 11:25
Does this answer your question? [Laravel Eloquent select all rows with max created\_at](https://stackoverflow.com/questions/47959102/laravel-eloquent-select-all-rows-with-max-created-at) or [Laravel get last row value form many table in one to Many relationship](https://stackoverflow.com/questions/50883935/laravel-get-last-row-value-form-many-table-in-one-to-many-relationship/50884160#50884160) — M Khalid Junaid, Jan 07 '21 at 19:00

score 2 · Answer 1 · answered Jan 06 '21 at 12:09

you can get the last ids then get the complete records:

 $max_ids = Submission::query()
       ->where('status','redo')

        ->select('user_id', DB::raw('max(id) max_ids'))
        ->groupBy('user_id')->pluck('max_ids');

    $lasts = Submission::whereIn('id', $max_ids)->get();

lagbox · Answer 2 · 2021-01-06T12:18:51.757

2

You can try this with a sub query:

Submission::from(Submission::orderBy('id', 'desc'), 't')
    ->groupBy('user_id')
    ->get();

Probably will have to make adjustments, not tested, but the idea is a sub query would get the ordering then the main query would do the grouping.

edited Jan 06 '21 at 12:18

answered Jan 06 '21 at 12:14

lagbox

48,571
8
72
83

could you explain what is the `t` letter in the second attribute of `from` method? i like this answer you are a genus – Joseph Jan 06 '21 at 12:18

Get latest data of a specific column using eloquent

2 Answers2