Comma separate values with same number of rows

Question

DATABASE STRUCTURE

items: id, name

users: id, username

user_items: id, user_id, item_id

I currently have a count() on user_items that shows the users with the most number of items:

john: 13
bill: 9
lily: 9
kent: 9
seth: 8
vick: 8
anna: 7

But I want to display it in this fashion:

13: john
 9: bill, lily, kent
 8: seth, vick
 7: anna

How can I achieve this with laravel's query system in blade?

Answer 1

Not sure about Laravel, but this is the pure MySQL version:

SELECT itemCount, GROUP_CONCAT(username)
FROM
(
   SELECT tblU.user_name, COUNT([DISTINCT] tblUI.item_id) AS itemCount
   FROM users AS tblU 
   INNER JOIN user_items AS tblUI ON tblU.id = tblUI.user_id
   GROUP BY tblU.user_name
) AS uic
GROUP BY itemCount

Note, DISTINCT is optional, hence the square brackets. Also, if you want the users without any items, you could make the JOIN a LEFT JOIN.

Answer 2

In Controller get like this

$users = DB::table('user_items as ui')
    ->select('count',DB::raw("group_concat(name SEPARATOR ', ') as names"))
    ->from(DB::raw('(select count(*) as count,u.username as name from user_items ui  join users u on u.id=ui.user_id  group by u.id) a'))
    ->groupBy('count')
    ->orderBy('count','desc')
    ->get();

$data['users']=$users;

return View::make('hello',$data);

In view using blade

@foreach($users as $user)
  <li>{{$user->count.':'. $user->names}}</li>
@endforeach

If you want users with no item to be displayed ,your query can be

$users = DB::table('users as u')
            ->select('count',DB::raw("group_concat(name SEPARATOR ', ') as names"))
            ->from(DB::raw('(select count(ui.item_id) as count,u.username as name from users u  left join user_items ui on u.id=ui.user_id  group by u.id) a'))
            ->groupBy('count')
            ->orderBy('count','desc')
            ->get();

To display names seperately add following code before $data['users']=$users;

foreach($users as $user){
            $user->names=explode(', ',$user->names);
    }

And in blade you can

<ul>
    @foreach($users as $user)
        <li>
            <span>{{$user->count}}:</span>

            @foreach($user->names as $index=>$name)
                <span><a href="#">{{$name}}</a> @if($index!=count($user->names)-1) ,@endif </span>
            @endforeach
        </li>
    @endforeach
</ul>

Answer 3

The simplest may be to process the DB result with PHP afterwards:

$result = [];
foreach ($db_result as $name => $item_count) {
    $result[$item_count][] = $name;
}

Then, to obtain your expected output, you might want to follow with an implode():

foreach ($result as &$names) {
    $names = implode(', ', $names);
}