我有两个表共享一个关系用户和日志表。我试图查询用户表,并从日志中获得最高id值的记录
到目前为止,这是我返回的重复条目:
$students = User::with([
'course' => function ($query) {
$query->get(['id', 'name']);
}
])
->join('log', 'users.id', '=', 'log.user_id')
->where('log.event', 1)
->orderBy('log.id', 'desc')
->where('users.verified', 1)
->get(['users.*', 'log.id AS logid']);
理想情况下,我希望从日志表中为每个用户
最后插入的记录groupBy('user_id')
返回第一个记录
$students = User::with([
'course' => function ($query) {
$query->get(['id', 'name']);
}
])
->join('log', 'users.id', '=', 'log.user_id')
->where('log.event', 1)
->whereRaw('log.id = (select max(`id`) from log where `user_id` = users.id )')
->where('users.verified', 1)
->get(['users.*', 'log.id AS logid', 'log.user_id']);
我觉得你很接近,只有你需要使用极限。
->where('users.verified', 1)
->take(1) // add this to get only a single record
->get(['users.*', 'log.id AS logid']);
在没有groupBy
$students = User::with([
'course' => function ($query) {
$query->get(['id', 'name']);
}
])
->join('log', 'users.id', '=', 'log.user_id')
->where('log.event', 1)
->orderBy('log.id', 'desc')
->where('users.verified', 1)
->where('log.id',DB::raw("SELECT MAX(id) FROM log WHERE log.user_id = users.id")) // get the max id value
->get(['users.*', 'log.id AS logid']);