get count in joining multiple table

Published 4 months ago by tushar09

I have 3 tables named user, group and user_has_group. I am joining these tables using eloquent. Everything is working just fine, but I need something more. I need to figure it out that which group has how many users. Here the I code I have used:

$result = UserHasGroup
                ::join('group', 'user_has_group.group_id', '=', 'group.id')
                ->join('user', 'group.created_by', '=', 'user.id')
                ->where('user_has_group.user_id', '=', $user -> id)
                ->select("user.name as created_by",
                    'group.created_at',
                    'user_has_group.user_id',
                    'group.name as group_name')
                ->getQuery()
                ->get();

and here's the response:

{
    "successful": [
        {
            "created_by": "TUSHAR13",
            "created_at": "2018-05-11 18:04:38",
            "user_id": "5",
            "group_name": "DSGJA"
        },
        {
            "created_by": "TUSHAR13",
            "created_at": "2018-05-11 18:10:17",
            "user_id": "5",
            "group_name": "V76OL"
        },
        {
            "created_by": "TUSHAR13",
            "created_at": "2018-05-10 00:00:00",
            "user_id": "5",
            "group_name": "qwerr"
        }
    ]
}

Now I can't understand where to add groupby and count query. I need help.

lanatel

You can do it another way. If user can belong to many groups and group can have a lot of users you can define a many to many relationship https://laravel.com/docs/5.6/eloquent-relationships#many-to-many and then just call withCount() method

$group = Group::withCount('users')->first();
$count = $group->users_count;

Please sign in or create an account to participate in this conversation.