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.