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     | user2
----------------------
3 |    C     | user1

...

And I have some logical expression that looks like this:
(A & B) || C

Which means, that if I’m searching by some user, this user should be in both groups A and B, or in C.

I have to check users with custom expressions in Laravel, and my query will look like this:

return DB::table('assigned_groups')->where($expression)->where('username', $username)->count();

Where $expression is my logical expression written in raw SQL, I guess. And I have to check whether some $username can be found assigned to needed groups at least once.

For now I just have a piece of pseudocode for $expression like this:

select count(*)
having (
    (count(select(*) where group_name = A) > 0 
    and count(select(*) where group_name = B) > 0)
    or count(select(*) where group_name = C) > 0
)

How do I write this expression correctly? How should I change my Laravel query and $expression itself?

UPD: now my SQL looks like this, and it’s almost something

SELECT count(*) FROM `assigned_groups`
where username = 'user1'
having (
    (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
)

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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();

Method 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.

Method 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’)”);


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x