How to select multiple entries of a column from the rightjoined table in Laravel MySql?

Question

I have 2 tables: User and User_login_log

I wrote a select statement in Laravel to get perticular user's data and want to retrive their loged in months from User_login_log.

But, the problem is that I have multiple entries for a single user in User_login_log table. like:

User Id       Month_no
2022          2
2022          3
2022          4

If I right join the User_login_log table with User table, and write:

$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)...

then I only get 1 month_no in the retrived data.

got result:

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

I want result:

User_id       first_name        last_name        Month_no
2022           ****               ****           2,3,4
2023           ****               ****           (login months)

Answer 1

I think you should be using a left join along with a raw select and GROUP_CONCAT():

$result = User::leftjoin('user_login_logs', 'user_login_logs.user_id', 'users.id')
    ->selectRaw('users.id, users.first_name, users.last_name, GROUP_CONCAT(user_login_logs.month_no ORDER BY user_login_logs.month_no) AS Month_no')
    ->where('users.batch_id', session()->get('batch_id'))
    ->whereIn('users.id', $array)
    ->groupBy('users.id', 'users.first_name', 'users.last_name');