two columns where clause merge with comma

Question

I want to build a query.

here is table:

id  user_id  sn_no
1   22       00112233
2   22       987654325
3   22       65489732
4   25       984123123
5   25       9568456456
6   25       65456456

I want result like this:

{
   "user_id":22,
   "sn_no": "00112233,987654325,65489732"
}
{
   "user_id":25,
   "sn_no": "984123123,9568456456,65456456"
}

Can anyone please help to solve this issue?

I have tried: concat, GROUP_CONCAT but can not get the result.

Can you please help me add this in Join query?

$users = \DB::table('users')
            ->join('users_mcu', 'users.id', '=', 'users_mcu.user_id')
            ->join('country_user', 'users.id', '=', 'country_user.user_id')
            ->join('country_phase_color', 'country_user.country_id', '=', 'country_phase_color.id')
            ->select('users.id', 'users.first_name', 'users.last_name', 'users.company', 'users.designation', 'users.lang', 'users.phone', 'users.disp_graph', 'users.user_image', 'users.email', 'users.role', 'users.created_at', 'users.updated_at', 'country_user.country_id', 'country_phase_color.country_name')
            ->get();

Answer 1

Try this-

YourModel::select('user_id')
         ->selectRaw('GROUP_CONCAT(sn_no) as sn')
         ->groupBy('user_id')
         ->get();