如何使用雄辩的Laravel 5.2通过join从表返回最高的id值 - How to return highest id value from a table via join with eloquent Laravel 5.2

How to return highest id value from a table via join with eloquent Laravel 5.2

我有两个表共享一个关系用户和日志表。我试图查询用户表，并从日志中获得最高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

的条件下，再加1试试下面的解

$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']);