laravel latest record fetch with groupBy Clause

Question

In LARAVEL I have users with lots of answers with model test ids. now I want the last created answers with the group by model_test_id. here is the LARAVEL code I wrote but it gives me the first value it finds in the group and then orders by created_at. how can I get the recent data with the group clause?

$answers = auth()->user()->answers()->groupBy('model_test_id')->orderBy('created_at', 'DESC')->get();

answers
+----+--------------------------------------+---------+---------------+---------------------+
| id | uuid                                 | user_id | model_test_id | created_at          |
+----+--------------------------------------+---------+---------------+---------------------+
|  1 | 1ffcb80a-c426-4424-9fda-1cadf573289f |       1 |            11 | 2021-03-08 12:13:07 |
|  2 | 8d23ad83-0913-4beb-91f3-4851384c52d3 |       1 |            12 | 2021-03-08 12:18:33 |
| 18 | c60066e4-ea15-4b5a-82f0-d635b912bad1 |       1 |             5 | 2021-03-08 10:18:20 |
| 20 | 499e02b3-571f-444f-be34-cfc7499d3a77 |       1 |            11 | 2021-03-10 14:37:43 |
| 21 | 962f892c-83c3-4f1f-88cf-17f0a475ac07 |       1 |            12 | 2021-03-10 16:21:45 |
| 22 | ebd713f5-fdf2-4dc6-9be1-4f45a8ee71dd |       1 |             5 | 2021-03-10 17:03:07 |
+----+--------------------------------------+---------+---------------+---------------------+

output data
+----+--------------------------------------+---------+---------------+---------------------+
| id | uuid                                 | user_id | model_test_id | created_at          |
+----+--------------------------------------+---------+---------------+---------------------+
|  2 | 8d23ad83-0913-4beb-91f3-4851384c52d3 |       1 |            12 | 2021-03-08 12:18:33 |
|  1 | 1ffcb80a-c426-4424-9fda-1cadf573289f |       1 |            11 | 2021-03-08 12:13:07 |
| 18 | c60066e4-ea15-4b5a-82f0-d635b912bad1 |       1 |             5 | 2021-03-08 10:18:20 |
+----+--------------------------------------+---------+---------------+---------------------+

i want this output
+----+--------------------------------------+---------+---------------+---------------------+
| id | uuid                                 | user_id | model_test_id | created_at          |
+----+--------------------------------------+---------+---------------+---------------------+
| 22 | ebd713f5-fdf2-4dc6-9be1-4f45a8ee71dd |       1 |             5 | 2021-03-10 17:03:07 |
| 21 | 962f892c-83c3-4f1f-88cf-17f0a475ac07 |       1 |            12 | 2021-03-10 16:21:45 |
| 20 | 499e02b3-571f-444f-be34-cfc7499d3a77 |       1 |            11 | 2021-03-10 14:37:43 |
+----+--------------------------------------+---------+---------------+---------------------+

use the `max` aggregate function to get the max/latest date. For reference: https://stackoverflow.com/questions/5362160/mysql-order-by-inside-of-group-by — user3532758, Mar 15 '21 at 07:11

Zia Yamin · Answer 1 · 2021-03-15T07:08:44.657

0

you can first order them:

$answer = auth()->user()->answers()->orderBy('created_at', 'DESC')->get();

then group them.

$answers = DB::table(DB::raw("({$answer->toSql()}) as answers"))
->groupBy('model_test_id')
->get();

edited Mar 15 '21 at 07:08

answered Mar 15 '21 at 06:43

Zia Yamin

942
2
10
34

I don't want the first record. – kundefine Mar 15 '21 at 06:50
it give you the latest record, \so you want all of loggind user answer or some of them? – Zia Yamin Mar 15 '21 at 06:54
see the *output data* created_at column all the data are old but I want the recently created_at data as I mention in the output I want. – kundefine Mar 15 '21 at 06:59
what about changing the place of ``orderBy`` and ``groupBy``, first get data and order them, then group by them? – Zia Yamin Mar 15 '21 at 07:03
It is the same data I get previously. there is no change – kundefine Mar 15 '21 at 07:05
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/229922/discussion-between-zia-yamin-and-kundefine). – Zia Yamin Mar 15 '21 at 07:12

laravel latest record fetch with groupBy Clause

1 Answers1