用相同的行数逗号分隔值 [英] Comma separate values with same number of rows

问题描述

项目:id, name

用户:id, username

user_items :id, user_id, item_id

我目前在user_items上有一个count()，显示的用户数最多:

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

如何在刀片服务器中使用laravel的查询系统来实现这一目标?

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

推荐答案

在Controller中得到这样的

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

在使用刀片的视图中

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

要显示名称，请分别在$data['users']=$users;

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

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

在刀片中您可以

<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>

这篇关于用相同的行数逗号分隔值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！