Group_concat - laravel eloquent

Question

please I want to use group_concat in a query using eloquent and not raw queries.

here is the code which i tried to execute and did't work for me:

commands::join('products', 'products.id', '=','commands.idproduct')
->select('commands.username','**group_concat(products.name)**')
->group by ('commands. username')
->get();

Thanks in advance :)

Try using DB::raw() to wrap 'raw' SQL you want in a query: `->select('commands.username',DB::raw('group_concat(products.name)'))` — alexrussell, Sep 15 '14 at 12:17
thanks for your reply @alexrussell but the problem is that when I add the DB::raw and I run again the application I get this error: Symfony \ Component \ Debug \ Exception \ FatalErrorException Class 'Storage\Commands\DB' not found — user3481058, Sep 15 '14 at 12:38
Ahh you're in a namespace - either add `use DB;` after your namespace declaration or use `DB` in your code: `\DB::raw('group_concat(products.name)')` — alexrussell, Sep 15 '14 at 13:13

score 12 · Accepted Answer · edited Sep 11 '20 at 13:34

12

I just used:

use DB;

and in my query I used

DB::raw('group_concat(products.name)')

edited Sep 11 '20 at 13:34

Mayank Pandeyz

25,704
4
40
59

answered Sep 15 '14 at 13:17

user3481058

313
2
4
18

You should mark this answer as the correct answer so that people know. – alexrussell Sep 15 '14 at 14:37

score 6 · Answer 2 · answered Dec 01 '14 at 09:17

6

Best example for it..

 
ModelName::select('ID', DB::raw('CONCAT(First_Name, " ", Last_Name) AS full_name'))
           ->get()
           ->toArray();

Result 
   Jon Doe,Jeffery Way,Tailer,taylor otwell

answered Dec 01 '14 at 09:17

Uttam Panara

541
2
10
28

score 6 · Answer 3 · answered Jan 24 '17 at 11:15

This worked for me

$list = TableName::where('user_id', 'user_001'
        ->groupBy('user_id')
        ->groupBy('subscription_id')
        ->select('user_id','subscription_id','type')
        ->selectRaw('GROUP_CONCAT(holiday) as holidays')
        ->get();

or

use Illuminate\Support\Facades\DB;

$sql = 'SELECT GROUP_CONCAT(holiday) as holidays, user_id,subscription_id, type FROM TableName 
        where vendor_id = 'user_001' GROUP BY user_id, subscription_id;';
$list = DB::select($sql, []);

score 2 · Answer 4 · answered Jun 10 '16 at 00:26

2

or just replace

->select('commands.username','**group_concat(products.name)**')

with

->selectRaw('commands.username, **group_concat(products.name)**')

answered Jun 10 '16 at 00:26

cabs

710
7
14

score 0 · Answer 5 · answered Jan 13 '16 at 05:35

0

This worked for me: (9.0+)

DB::raw('string_agg(products.name, \',\') as products')

You will need to use Illuminate\Support\Facades\DB; for this.

answered Jan 13 '16 at 05:35

Nisar P

316
2
9

score -3 · Answer 6 · answered Mar 27 '17 at 06:08

$data=\DB::table('paging_config') ->leftjoin('paging_groups', 'paging_config.page_group', '=', 'paging_groups.page_number') ->leftjoin('spk_mnt', 'paging_groups.ext', '=', 'spk_mnt.stn_no') ->select('paging_config.page_group','paging_groups.ext', 'spk_mnt.stn_status') ->selectRaw('GROUP_CONCAT(DISTINCT description) as description') ->where('paging_config.page_group','=','paging_config.page_group') ->groupBy('paging_config.description')

        ->get();

Group_concat - laravel eloquent

6 Answers6

Linked