Write Custom Sql Query For Multiple Counts

I have the table in MySQL, containing group_name and username, like this: ID|group_name|username ---------------------- 1 | A | user1 ---------------------- 2 | B | u

Solution 1:

You can write your raw expression using havingRaw

DB::table('assigned_groups')
->where('username', $username)
->havingRaw("(count(case group_name when 'A' then 1 else null end) > 0 
    and count(case group_name when 'B' then 1 else null end) > 0)
    or count(case group_name when 'C' then 1 else null end) > 0")
->count();

or shorter using sum()

DB::table('assigned_groups')
->where('username', $username)
->havingRaw("(sum(group_name ='A') > 0 and sum(group_name = 'B') > 0) or sum(group_name = 'C') > 0")
->count();

Solution 2:

Try this:

return DB::table('assigned_groups')
    ->where('username', $username)
    ->andWhere(function($query) use ($groupAandB, $groupC) {
        $query->whereIn('group_name', $groupAandB)
              ->orWhereIn('group_name', $groupC);
    })
    ->count();

I actually am not sure if there's an orWhereIn method, but this structure should give you a good starting point.

Solution 3:

try this one :

$users = DB::select("select count() from user where username='$username' and (username in (select username from user where group_name in ('A','B') having count() >1 group by username) or group_name ='C')");