mysql - 带有左联接的Laravel选择查询

表-待定用户详细信息

UserId  | Username
------------------
1       |  jijo
2       |  libin

表-用户追踪

FollowupId  |  UserId  |  Status
---------------------------------
1           |  1       |  Negative
2           |  1       |  Neutral
3           |  1       |  Positive

我的控制器是

$result= DB::table('tbl_user_details')
                        ->leftjoin('tbl_user_followups','tbl_user_details.UserId','=','tbl_user_followups.UserId')
                        ->select('tbl_user_details.*','tbl_user_followups.*')

                        ->orderBy('tbl_user_followups.FollowupId','DESC')
                        ->groupBy('tbl_user_details.UserId')
                        ->get();

我想得到如下输出

UserId  | Username | FollowupId  |  Status
----------------------------------------
1       |  jijo    |    3        |  Positive
2       |  libin

有人可以建议在我的控制器中进行编辑吗？??

最佳答案

不确定我是否理解对了问题：你想要最后一次“跟进”的用户列表吗？如果是这样，那么：

SELECT d.userid,
       d.username,
       det.followupid,
       det.status
FROM   tbl_user_details d
       LEFT JOIN (SELECT userid,
                         followupid,
                         status
                  FROM   tbl_user_followups f
                  WHERE  followupid IN (SELECT Max(followupid)
                                        FROM   tbl_user_followups
                                        GROUP  BY userid)) det
              ON d.userid = det.userid
ORDER  BY det.followupid DESC;


 order by det.followupid desc;

请注意，“order by det.followupid”不会对它们进行正确排序，因为我们希望某些用户具有“null”followupid。