how to get latest record from database table against each value in column Laravel-8?-CodePudding

I have been working on a project and a bit stuck in fetching record from table and need some help in this regard. Now The scenario is I have a table similar like below:

id col_1        col_2               created _at         updated_at
1      1        some value          timestamps          timestamps
2      1        some new value      timestamps          timestamps
3      2        some value          timestamps          timestamps  
4      2        some new value      timestamps          timestamps

Now as u see in col_1 there are values numbers repeating, every time a value against them is inserted. Now what I am working on and stuck in is fetching latest record against each values in col_1. So that my result look like this

id col_1        col_2               created _at         updated_at
2      1       some new value       timestamps          timestamps
4      2       some new value       timestamps          timestamps

Below is my piece of code what I tried but get me wrong data.

  $data = TableName::select(DB::raw('t.*'))
            ->from(DB::raw('(SELECT * FROM table_name) t'))
            ->groupBy('t.col_2')->latest()
            ->first();
        dd($data);
        return view('view_name')->with([
            'data' => $data
        ]);
    }

Any idea how can I achieve such thing in Laravel. Any help would be highly appreciated. Thanks

CodePudding user response：

I think the following should give you the results you want.

$data = DB::table('table_name')
  ->groupBy('col_1')
  ->latest()
  ->get();

If the TableName model is associated with the correct table (check if the query is correct when you do dd(TableName::query()->toSql());), then you can replace the query with

$data = TableName::groupBy('col_1')->latest()->get();

CodePudding user response：

In a technical sense, when you group rows, the columns that are not/cannot be grouped lose meaning. So in situations like this, you could or rather you should be looking to do this in two separate queries (especially if SQL full group only mode is on). One to group, the other one to join the actual record.

See if something like this works:

$grouped = Model::select('col_id as merged_col_id', DB::raw('max(id) as latest_id'))->groupBy('col_id'); //since `id` field looks autoincrementing, using max(id) here should give you the latest record from all merged columns.

return Model::select('id', 'col_id', 'val')->joinSub($grouped, 'grouped', function($join){
    $join->on('grouped.latest_id', '=','table_name.id');
})->get();

Take care to change Model and table_name appropriately.