Get the latest records from database using Laravel-CodePudding

I've a sample data

id    name      city       status      timestamp
1     John      NYC        active      2022-05-17 12:28:00
2     Kevin     W.DC       active      2022-05-17 12:29:00
3     Ross      NYC       in_active    2022-05-17 12:28:00
4     John      NYC       in_active    2022-05-17 12:29:00
5     Kevin     W.DC      in_active    2022-05-17 12:29:53

I'm trying to get the latest records of users in that cities

name      city       status      timestamp
John      NYC       in_active    2022-05-17 12:29:00
Kevin     W.DC      in_active    2022-05-17 12:29:53
Ross      NYC       in_active    2022-05-17 12:28:00

I'm trying to build this logic in repository function:

$model = TableName::with([])
          ->select(['name', 'city', 'status', 'timestamp'])
          ->get();

Now my model contains all the records, how can I obtain the required records?

CodePudding user response：

In MySQL 8 you can use ROW_NUMBER:

with cte as 
(  select *,row_number() over(partition by name order by `timestamp` desc) as row_num
   from test
 ) select name,city,status,timestamp
   from cte
   where row_num=1;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=923ecbbbe50bdf31b42525a709270dfa

CodePudding user response：

you can use unique() like this:

User::latest()->get()->unique('city')

CodePudding user response：

if you want "an eloquent way" check One Of Many relation


//User model
public function latestTableName(){
  return $this->hasOne(TableName::class)->latestOfMany('timestamp');
}

//controller
$model = User::with(['latestTableName'])->get()->pluck('latestTableName');

and then return resource with desired fields