Laravel MySQL如何将右连接表中用户多登录月份合并查询？

我有两张表：User 和 User_login_log。在Laravel中编写查询语句，想要获取特定用户的数据，并从User_login_log表中获取他们的登录月份。但User_login_log表中单个用户存在多条记录，例如：

User Id       Month_no
2022          2
2022          3
2022          4

我使用右连接编写了如下代码：

$result = User::rightjoin('user_login_logs', 'user_login_logs.user_id', 'users.id')
            ->select('users.id', 'users.first_name', 'users.last_name','user_login_logs.month_no')
            ->where( 'users.batch_id', session()->get('batch_id'))
            ->whereIn('users.id', $array)...

但查询结果中每个用户仅能获取到一个month_no：

User_id       first_name        last_name        Month_no
2022           ****               ****           2
2023           ****               ****           1

期望的结果是将同一用户的登录月份合并为逗号分隔的形式：

User_id       first_name        last_name        Month_no
2022           ****               ****           2,3,4
2023           ****               ****           (登录月份)

要实现同一用户登录月份的逗号拼接，需要结合数据库聚合函数与分组查询，以下是不同数据库环境的实现方式：

MySQL环境 #

使用GROUP_CONCAT聚合函数拼接月份，同时按用户字段分组确保单用户单条记录：

$result = User::rightJoin('user_login_logs', 'user_login_logs.user_id', '=', 'users.id')
    ->select(
        'users.id',
        'users.first_name',
        'users.last_name',
        DB::raw('GROUP_CONCAT(user_login_logs.month_no SEPARATOR ",") as month_no')
    )
    ->where('users.batch_id', session()->get('batch_id'))
    ->whereIn('users.id', $array)
    ->groupBy('users.id', 'users.first_name', 'users.last_name')
    ->get();

• GROUP_CONCAT会自动将同一用户的所有month_no用逗号拼接成字符串
• groupBy指定用户的唯一标识和姓名字段，避免重复数据

PostgreSQL环境 #

PostgreSQL需使用STRING_AGG函数，注意要将数字类型的month_no转为文本：

$result = User::rightJoin('user_login_logs', 'user_login_logs.user_id', '=', 'users.id')
    ->select(
        'users.id',
        'users.first_name',
        'users.last_name',
        DB::raw('STRING_AGG(user_login_logs.month_no::text, ",") as month_no')
    )
    ->where('users.batch_id', session()->get('batch_id'))
    ->whereIn('users.id', $array)
    ->groupBy('users.id', 'users.first_name', 'users.last_name')
    ->get();

处理无登录记录的用户 #

如果要给没有登录日志的用户设置默认显示（比如“无登录记录”），可以用COALESCE函数包裹聚合函数，以MySQL为例：

DB::raw('COALESCE(GROUP_CONCAT(user_login_logs.month_no SEPARATOR ","), "无登录记录") as month_no')

内容的提问来源于stack exchange，提问作者om sutariya