Laravel框架下MySQL如何查询Count为0及Province为null的数据

问题根因 #

原有查询使用隐式内连接，存在几个核心问题：

• 只会返回有匹配参与记录的省份分组，没有符合条件记录的省份不会出现在结果中，无法展示count为0的数据
• 内连接+WHERE筛选的写法会过滤掉关联失败的记录，同时SQL中null值无法用普通等值匹配关联，容易漏掉province为null的统计项
• 直接拼接日期参数到SQL存在SQL注入风险
• 原有前端模板存在HTML结构错误，<tbody>标签放在循环内部，且缺少<tr>行标签，不符合表格规范

调整步骤 #

1. 优化后端查询逻辑 #

优先使用独立的省份字典表作为统计驱动表（能保证所有法定省份都展示，哪怕无历史参与记录），如果没有独立省份字典表，可以从参与表中提取所有历史出现过的省份（含null）作为驱动集，改用左连接关联业务数据，注意将业务筛选条件放在连接ON子句中，避免左连接退化为内连接，同时将COUNT(*)改为统计关联表的非空主键，保证无匹配记录时统计值为0。

方案1：有省份字典表（推荐）

$province = DB::select(DB::raw("
    SELECT 
        p.province,
        COUNT(nfp.id) AS jumlah
    FROM provinces p -- 替换为实际的省份字典表名
    LEFT JOIN new_form_participants nfp 
        ON (nfp.province = p.province OR (p.province IS NULL AND nfp.province IS NULL))
        AND nfp.is_published = 1
    LEFT JOIN new_forms nf 
        ON nf.id = nfp.form_id
        AND nf.is_published = 1
        AND DATE(nf.updated_at) BETWEEN :reqfrom AND :reqto
    GROUP BY p.province
"), [
    'reqfrom' => $reqfrom,
    'reqto' => $reqto
]);

方案2：无独立省份字典表

从参与表提取全量历史省份作为驱动集：

$province = DB::select(DB::raw("
    SELECT 
        all_prov.province,
        COUNT(nfp.id) AS jumlah
    FROM (
        SELECT DISTINCT province FROM new_form_participants
    ) all_prov
    LEFT JOIN new_form_participants nfp 
        ON (nfp.province = all_prov.province OR (all_prov.province IS NULL AND nfp.province IS NULL))
        AND nfp.is_published = 1
    LEFT JOIN new_forms nf 
        ON nf.id = nfp.form_id
        AND nf.is_published = 1
        AND DATE(nf.updated_at) BETWEEN :reqfrom AND :reqto
    GROUP BY all_prov.province
"), [
    'reqfrom' => $reqfrom,
    'reqto' => $reqto
]);

2. 修正前端Blade模板 #

将<tbody>标签移到循环外部，补充缺失的<tr>行标签，对province为null的情况做友好展示：

<table>
    <thead>
        <tr>
            <th width="50px" class="text-right">序号</th>
            <th class="text-left">省份</th>
            <th class="text-left">参与数量</th>
        </tr>
    </thead>
    <tbody>
    @foreach ($province as $key => $data)
        <tr>
            <th width="50px" class="text-right">{{ $key + 1 }}</th>
            <th class="text-left">{{ $data->province ?? '未填写省份' }}</th>
            <th class="text-left">{{ $data->jumlah }}</th>
        </tr>
    @endforeach
    </tbody>
</table>

关键调整说明 #

• 左连接驱动逻辑保证所有省份都会返回，无匹配记录时jumlah值为0
• 单独添加null值的关联匹配条件，避免漏掉省份未填写的记录
• 使用参数绑定替代SQL字符串拼接，规避SQL注入风险
• 修正HTML表格结构，避免前端渲染异常

内容的提问来源于stack exchange，提问作者Eranda Dava