在Laravel中使用groupby选择最新行

Question

I have a table entries similar as follows:

+---------+---------+----------+
| Test_id | User_id | Attempts |
+---------+---------+----------+
|      12 |       5 |        1 |
|      13 |       5 |        1 |
|      12 |       5 |        2 |
+---------+---------+----------+

Now I want to select the elements group by test_id and should get the latest entry.

I tried this query:

$tests_took = Testresult::where('course_id', $courseId)
                        ->where('user_id', Auth::id())
                        ->groupby('test_id')
                        ->orderBy('attempts', 'desc')
                        ->get();

When I display the result, I'm getting the first two rows only (only one row for one test_id - which I what I want.) But instead of the last row for the test_id=12, it shows the first row. I always want the biggest attempt to be displayed.

My current output is like:

|      12 |       5 |        1 |
|      13 |       5 |        1 |

But I want this:

|      12 |       5 |        2 |
|      13 |       5 |        1 |

How can I achieve this? to get the latest row as the first array element when I use groupby or is there any other way to do this?

Answer 1

ORDER BY and GROUP BY don't work very well together...

If you simply want the highest attempt per test_id i suggest using the MAX() function:

$tests_took = Testresult::select('test_id', 'user_id', DB::raw('MAX(attempts) AS max_attempts'))
                ->where('course_id', $courseId)
                ->where('user_id', Auth::id())
                ->groupby('test_id')
                ->get();