doujia4041 2018-11-18 15:52
浏览 48
已采纳

Laravel使用连接查询获取所有组以及每个组组成员的计数

I have two tables 'Group'('id', 'title', 'description') and 'Group_members'('id', 'group_id', 'user_id'). When ever a user joins a group, a row is created in 'group_members' table.

What I need is to fetch all groups along with the number of members in each group, something like:-

[
    {
        id: 1
        title: group1
        description: lorem ipsum
        member_count: 5
    }{
        id: 2
        title: group2
        description: lorem ipsum
        member_count: 7
    }
]

What I have done so far is,

    DB::table('group')
        ->join('group_members', 
        function($join){
            $join->on('group_members.group_id', '=', 'group.id')
            ->distinct()->pluck('group_members.user_id')->count();
        })
        ->get();

which returns error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on group.id = group_members.`group_id

Please Help

  • 写回答

1条回答 默认 最新

  • dtmm0148603 2018-11-18 16:20
    关注

    If you've got your relationship set up in your Group model like

    public function group_members()
    {
        return $this->hasMany('App\GroupMember');
    }
    

    and in GroupMember model like

    public function group()
    {
        return $this->belongsTo('App\Group');
    }
    

    you should be able to do

    $groups = Group::all();
    
    foreach ($groups as $group) {
        $group->member_count = $group->group_members()->count();
    }
    
    $groupsArray = $groups->toArray();
    

    Or

    $group = Group::where('column', 'value')->with('group_members')->first();
    $count = $group->group_members->count();
    

    Read up on Relationships and Collections in the docs


    You could also use a mutatus/accessor to return the member count as a property of the model so in your Group model

    getGroupMemberCountAttribute() {
        return $this->group_members()->count();
    }
    

    And use it like

    $group->group_member_count;
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 矩阵加法的规则是两个矩阵中对应位置的数的绝对值进行加和
  • ¥15 活动选择题。最多可以参加几个项目?
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)
  • ¥20 怎么在stm32门禁成品上增加查询记录功能
  • ¥15 Source insight编写代码后使用CCS5.2版本import之后,代码跳到注释行里面
  • ¥50 NT4.0系统 STOP:0X0000007B
  • ¥15 想问一下stata17中这段代码哪里有问题呀