编写自定义SQL查询以获取多个计数 [英] Write custom SQL query for multiple counts

问题描述

我在MySQL中有一个包含group_name和username的表，如下所示:

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

这意味着，如果我正在由某个用户搜索，则该用户应同时在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.

我必须在Laravel中使用自定义表达式检查用户，我的查询将如下所示:

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

我猜想$expression是我用原始SQL编写的逻辑表达式.而且我必须检查是否至少可以找到一些$username分配给所需组的信息.

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.

现在我只有一段$ expression的伪代码，如下所示:

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
)

如何正确编写此表达式?应如何更改Laravel查询和$expression本身?

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

UPD:现在我的SQL看起来像这样，几乎差不多了

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
)

推荐答案

您可以使用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();

或更短使用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();

这篇关于编写自定义SQL查询以获取多个计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！