Laravel中使用CASE语句统计关联项目：如何实现反向条件计数？

直接上可实现需求的查询代码，通过左连接+CASE语句一次性统计两类项目数量：

DB门面写法 #

$stats = DB::table('projects')
    ->leftJoin('project_professional', 'projects.id', '=', 'project_professional.project_id')
    ->leftJoin('professionals', 'project_professional.professional_id', '=', 'professionals.id')
    ->selectRaw("
        COUNT(DISTINCT CASE WHEN professionals.contact_verified = 1 AND professionals.professional_active = 1 THEN projects.id END) as yes_count,
        COUNT(DISTINCT CASE WHEN professionals.id IS NULL OR NOT (professionals.contact_verified = 1 AND professionals.professional_active = 1) THEN projects.id END) as no_count
    ")
    ->first();

模型查询写法 #

如果习惯用Project模型进行查询，代码如下：

$stats = Project::query()
    ->leftJoin('project_professional', 'projects.id', '=', 'project_professional.project_id')
    ->leftJoin('professionals', 'project_professional.professional_id', '=', 'professionals.id')
    ->selectRaw("
        COUNT(DISTINCT CASE WHEN professionals.contact_verified = 1 AND professionals.professional_active = 1 THEN projects.id END) as yes_count,
        COUNT(DISTINCT CASE WHEN professionals.id IS NULL OR NOT (professionals.contact_verified = 1 AND professionals.professional_active = 1) THEN projects.id END) as no_count
    ")
    ->first();

关键逻辑说明 #

1. 左连接的作用：确保所有项目都被纳入统计，哪怕没有关联任何专业人员
2. Yes条件计数：当项目关联了至少一个contact_verified=1且professional_active=1的专业人员时，记录该项目ID，用COUNT(DISTINCT)避免同一项目因关联多个符合条件的专业人员被重复计数
3. No条件计数：覆盖两种场景：
   • 项目完全没有关联任何专业人员（professionals.id IS NULL）
   • 项目关联的专业人员都不符合已验证+活跃的条件（NOT (professionals.contact_verified = 1 AND professionals.professional_active = 1)）
     同样用COUNT(DISTINCT)保证每个项目只统计一次

执行后，$stats->yes_count即为符合条件的项目总数，$stats->no_count即为反向条件的项目总数。

内容的提问来源于stack exchange，提问作者Rubal Gulati