Is there any way in laravel to get the latest records against multiple shipment

I need the all the updated rows from the following data, I have a repetition of shipment id in my table from this I only need updated rows of each shipment id.

In my table there are multiple shipment ids and all are repetitional like:

id    date         shipment_id
1  2020-09-09        12
2  2020-10-05        12
3  2020-10-23        12
4  2020-09-09        13
5  2020-10-05        13
6  2020-10-23        13

Output should be:

id    date         shipment_id
3  2020-10-23        12
6  2020-10-23        13

I need the latest record of every shipment_id

its the original data

CodePudding user response：

You can do this way (Group by with max value query)

   $data = Shipments::select('id', 'shipment_id', DB::raw('MAX(date) as latest_date'))
                            ->where('is_active', 1)
                            ->groupBy('shipment_id')
                            ->get();
   
    dd($data);

Ref : https://www.itsolutionstuff.com/post/laravel-group-by-with-max-value-query-exampleexample.html

CodePudding user response：

You should take a look into querying via the Eloquent ORM.

The query should look something like this:

Shipments::orderBy('id', 'desc') ->groupBy('shipment_id') ->get();

To speed up the query we can leverage your id(increment_id) hence the largest id, with a certain shipement_id, always will be the newest.