getting multiple rows group by column

user_8941262 0
Viewed 124

I have sql table of this structure,

id  |  type   | name 
1   | type1  | name1
2   | type1  | name2
3   | type2  | name3
4   | type2  | name4

I want to get all the names grouped by the type like this

"type1" : [name1,name2]
"type2" : [name3,name4]

I am using Laravel eloquent, I tried keyBy('type') but it gives only one row of each type.
How can get all the names of one type?

eloquent laravel mysql
2 Answers

Seems like you're looking for the group_concat aggregate function:

SELECT   type, CONCAT('[', GROUP_CONCAT(name), ']')
FROM     mytable
GROUP BY type
user_24227760

As far as I know, there is no direct way to solve this problem.
I had the same problem, and solved it using foreach loop:

$data = DB::connection('myConn')
    ->table('myTable')
    ->get()->toArray();
    
$res = [];
foreach ($data as $entry) {
    if (!isset($res[$entry->type])) {
        $res[$entry->type] = [];
    }
    array_push($res[$entry->type], $entry->name);
}
user_10394880
Related