Eloquent latest of each group

Question

I have Activity model with following table:

id: unsignedBigInteger (PK)
special_identifier: unsignedBigInteger
additional_data: json
created_at: datetime

I'm trying to get the latest of the each special_identifier. Consider the scenario below:

//activities table data 
//(id, special_identifier,additional_data, created_at)

1,785,'lorem',2021-10-15 17:56:55
2,683,'ipsum',2021-09-12 16:45:55
3,683,'dolor',2021-08-11 12:34:12
4,683,'sit',2021-09-20 15:32:44
5,683,'amet',2021-09-20 18:32:44
6,785,'consectetur',2021-10-16 23:42:17

Expected collection:

683=>{
5,683,'amet',2021-09-20 18:32:44 (Activity Model instance !)
},
785=>{
6,785,'consectetur',2021-10-16 23:42:17 (Activity Model instance !)
}

I wrote some hideous piece of code for this but it's neither efficient nor eye-candy.

 $activities=Activity::query()->orderBy('created_at','DESC')->get()->groupBy('special_identifier');
        $latestActivity=collect();
        foreach($activities as $activityGroup){
            $latestActivity->push([
                'special_identifier'=>$activityGroup[0]['special_identifier'],
                'additional_data'=>$activityGroup[0]['additional_data']
                'created_at'=>$activityGroup[0]['created_at']->format('d-m-Y H:i:s'),

            ]);
        }

What is the 'eloquent' way of doing this ? In essence, i want the last record for each special_identifier. Last record in terms of created_at column. DB raw queries are also accepted to some extent, but preference is eloquent if possible.

Thank you

Eloquent latest of each group

0 Answers