GroupBy not Sorting by latest

Posted: 2018-03-15 10:39:52

Just to make this easy to find. I had a report to write which had numerous daily rows of the same related model data. So in this cause I was tracking servers and security reports.

My reports where daily like this

id server_id failed type
1 1 1 security
2 1 1 testing
3 1 0 testing

But a groupBy here on server_id would return ID 1 so it would appear as if it was still failing.

Adding

$results = $results->whereRaw('ID IN ( select MAX(id) from reports GROUP BY server_id)');

or double group by:

$results = $results->whereRaw('ID IN ( select MAX(id) from reports GROUP BY server_id, type)');

Did the trick to sort and group by that latest record, which latest did not do for me.