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