CODEWITHSUNDEEP
mysqlsqllaravelsearch
Jinal Gajera
Jinal Gajera

Reputation: 35

issue in Laravel DB query with search data and count

I want to search username in user's table and I have count members in member's table. And group's table is main table. members table in multiple entry of group members. but I can't be Search Username and count of Members both in one query. my query is below

$group = Group::select('groups.*', DB::raw('count(group_id) as totalMembers'), 'users.name as ownerName', 'users.id as userId')
            ->leftJoin('members', 'members.group_id', '=', 'groups.id')
            ->leftJoin('users', 'users.id', '=', 'members.user_id')
            ->where('members.memberStatus', 'Joined')
            ->where(function ($query) use ($request) {
                $query->where('name', "like", "%" . $request->searchText . "%");
                $query->orWhere('groupName', "like", "%" . $request->searchText . "%");            
            })
            ->groupBy('groups.id')
            ->get();

In this query members of count is not getting proper, but search is working my table structure is below

users table enter image description here

members table enter image description here

group tables enter image description here

Upvotes: 1

Views: 127

Answers (1)

VIKAS KATARIYA
VIKAS KATARIYA

Reputation: 6005

Try this subquery

$group = Group::select(['groups.*', 
                        'users.name as ownerName', 
                        'users.id as userId',
                        \DB::raw("(SELECT count(members.group_id) FROM members
                            WHERE members.group_id = groups.id
                            GROUP BY members.group_id) as totalMembers")])
            ->leftJoin('members', 'members.group_id', '=', 'groups.id')
            ->leftJoin('users', 'users.id', '=', 'members.user_id')
            ->where('members.memberStatus', 'Joined')
            ->where(function ($query) use ($request) {
                $query->where('name', "like", "%" . $request->searchText . "%");
                $query->orWhere('groupName', "like", "%" . $request->searchText . "%");            
            })
            ->groupBy('groups.id')
            ->get();

OR

DB::raw('count(members.group_id) as totalMembers

Upvotes: 1

Related Questions