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();

DB::raw("(GROUP_CONCAT(sn_no SEPARATOR ',')) as `sn_no` did you use group concat like this — Pankaj Bisht, Feb 25 '20 at 07:01

Rashed Hasan · Accepted Answer · 2020-02-25T10:45:58.320

4

Try this-

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

edited Feb 25 '20 at 10:45

answered Feb 25 '20 at 07:15

Rashed Hasan

3,721
11
40
82

Please check the updated answer. Actually I don't know the column name `sn_no` in which table... please be confirm about `sn_no` – Rashed Hasan Feb 25 '20 at 10:10
sn_no is stored in user_mcu table – vdr Feb 25 '20 at 10:29
IT says -------users.id' isn't in GROUP BY – vdr Feb 25 '20 at 10:34
First check with `->select('*', DB::raw("(GROUP_CONCAT(users_mcu.sn_no SEPARATOR ',')) as sn"))` – Rashed Hasan Feb 25 '20 at 10:36
let me explain you something in detail : there are 4 tables users, users_mcu, country_user, country_phase_color. I want user data with country_phase_color.country_name, country_user.country_id, so this works fine for me now there is a table for users_mcu which i have mention in top of the message, where user_id is there, now i want that list in same query with comma saperated of sn_no – vdr Feb 25 '20 at 10:38
HI, Do you know how can i merge 2 array to 1 with same user_id – vdr Feb 26 '20 at 08:16
You can use `array_merge()` please see the question https://stackoverflow.com/questions/14283532/how-to-merge-laravel-objects-in-controller . On other hand if you face any problem please create a new question. – Rashed Hasan Feb 26 '20 at 09:09

Dilip Hirapara · Answer 2 · 2020-02-25T07:05:40.523

1

Try this.

$data = \DB::table('tablename')->select('user_id', \DB::raw('group_concat(sn_no) as sn_nos'))->groupBy('user_id')->get();

as if you want to change SEPARATOR then you can use SEPARATOR for it otherwise the default will be ','

\DB::table('tablename')->select('user_id',DB::raw("(GROUP_CONCAT(sn_no SEPARATOR '@')) as `sn_nos`"))->groupBy('user_id')->get();

edited Feb 25 '20 at 07:05

answered Feb 25 '20 at 06:57

Dilip Hirapara

14,810
3
27
49

score 1 · Answer 3 · answered Feb 25 '20 at 07:20

1

use mysql group_concat function and laravel groupBy and raw function

DB::tabe('tableName')
   ->select('id','user_id',\DB::raw('GROUP_CONCAT(`sn_no`) as sn_no_t'))
   ->groupBy('user_id')
   ->get()

answered Feb 25 '20 at 07:20

Jignesh Joisar

13,720
5
57
57

VIKAS KATARIYA · Answer 4 · 2020-02-25T09:26:37.743

0

Try this

use DB;

DB::table('tableName')
        ->select('id','user_id','DB::raw("(GROUP_CONCAT(sn_no SEPARATOR ',')) as `sn_nos`")')
        ->groupBy('user_id')
        ->get();

edited Feb 25 '20 at 09:26

answered Feb 25 '20 at 07:39

VIKAS KATARIYA

5,867
3
17
34

score -1 · Answer 5 · answered Feb 25 '20 at 07:21

-1

You can try this

Model::select('user_id')
       ->selectRaw('GROUP_CONCAT(sn_nos) as sn_nos')
       ->groupBy('user_id')
       ->get();

answered Feb 25 '20 at 07:21

bhavinjr

1,663
13
19

two columns where clause merge with comma

5 Answers5