Laravel查询生成器的SQL子查询联接问题

Question

我一直在理解Laravel查询生成器和纯SQL之间的融合。

我有2张桌子：

• user：包含具有主键user_id
的用户
• User_Action：这是一个表，其中包含由user_id和动作的datetime组成的PRIMARYs记录。

ex：

使用者

user_id
1

用户操作

user_id    datetime              action_type
1          2017-12-01 12:10:00   y
1          2017-12-01 12:00:00   x

我的需要：

现在我想检索一个用户列表，其中包含每个用户的最新操作。因此，我只需要在从user到user\u action的连接中获取一行，并且这一行必须是具有最新日期时间的一行。我的前任也是如此；我只想获取datetime2017-12-01 12:10:00带有左连接的记录（即使没有操作也检索用户）。

我尝试了以下方法：

$userActionSubquery = DB::table('user_action')
        ->select()
        ->orderBy('datetime', 'DESC')
        ->limit(1);
$query->leftJoinSub($userActionSubquery, 'user_action', function ($join) {
         $join->on('user_action.user_id', '=', 'user.user_id');
})->groupBy('user_id');

通过这样做，我不会从user_action表中得到任何东西，但是如果给定用户的操作是表中最新的，它就可以工作！我以为$join-

PS：生成的查询是

SELECT user.user_id FROM user left join (SELECT * FROM user_action ORDER BY datetime DESC LIMIT 1) as user_action on user_action.user_id = user.user_id WHERE user.tenant_id in ('7') GROUP BY `user_id`

我错过了成功的东西，谢谢你的帮助！




Answer 1

试试这个：

$sub = \DB::table('user_action')
    ->select([
        'user_id',
        \DB::raw('SUBSTRING_INDEX( GROUP_CONCAT( action_type ORDER BY datetime DESC ), ",", 1 ) AS last_action_type')
    ])
    ->groupBy('user_id');

$data = \DB::table('user')
    ->leftJoinSub($sub, 'tmp', function ($join) {
        $join->on('tmp.user_id', '=', 'user.id');
    })->get();

sql:

SELECT * FROM user LEFT JOIN ( SELECT user_id, SUBSTRING_INDEX( GROUP_CONCAT( action_type ORDER BY datetime DESC ), ',', 1 ) AS last_action_type FROM user_action GROUP BY user_id ) AS tmp ON tmp.user_id = user.id

它在我的测试环境中工作：

使用者

id   name
1    john
2    jack

用户操作

id   user_id   datetime              action_type
1    1         2020-09-24 23:51:00   login
2    1         2020-09-24 23:51:20   search
3    2         2020-09-24 23:30:00   login
4    2         2020-09-25 00:00:00   coding

后果

Illuminate\Support\Collection Object
(
    [items:protected] => Array
        (
            [0] => stdClass Object
                (
                    [id] => 1
                    [name] => john
                    [user_id] => 1
                    [last_action_type] => search
                )

            [1] => stdClass Object
                (
                    [id] => 2
                    [name] => jack
                    [user_id] => 2
                    [last_action_type] => coding
                )

        )

)