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