Laravel查询如何统计包含不匹配值的结果（含日期场景）

你的问题本质上是因为原查询的whereIn只会过滤出数据库中存在的start值，后续的groupBy自然也只会返回这些存在的记录。要包含传入数组中所有值（哪怕没有匹配项，count显示0），我们需要先构造一个包含所有目标值的虚拟临时表，再和统计结果做左连接，确保所有目标值都能被保留。

下面分场景给出具体实现，同时兼顾安全性和通用性：

一、基础实现思路 #

核心步骤：

• 生成包含所有目标值的临时表（用UNION ALL拼接多个SELECT语句）
• 将临时表与原查询的统计结果做左连接（左连接会保留左表的所有行，不管右表有没有匹配）
• 用COALESCE函数把右表中无匹配时的null转为0

二、数字数组场景示例 #

比如你传入的[100,200,300,400]，可以这样写：

$targetStarts = [100, 200, 300, 400];

// 构造包含所有目标值的临时表SQL
$tempTableSql = collect($targetStarts)
    ->map(fn($start) => "SELECT $start as start")
    ->implode(' UNION ALL ');

// 左连接临时表与统计结果
$result = DB::table(DB::raw("($tempTableSql) as target_starts"))
    ->leftJoin(
        DB::raw("(
            SELECT count(start) as count, start
            FROM availabilities
            WHERE start IN (" . implode(',', $targetStarts) . ")
            GROUP BY start
        ) as availability_counts"),
        'target_starts.start',
        '=',
        'availability_counts.start'
    )
    ->select('target_starts.start', DB::raw('COALESCE(availability_counts.count, 0) as count'))
    ->get();

执行后就能得到你想要的结果：

三、日期数组场景示例 #

对于日期字符串数组['2018-09-10', '2018-09-18', '2018-09-16','2018-09-15']，逻辑完全一致，只是需要给字符串值加单引号：

$targetDates = ['2018-09-10', '2018-09-18', '2018-09-16','2018-09-15'];

// 构造日期临时表（注意字符串加单引号）
$tempTableSql = collect($targetDates)
    ->map(fn($date) => "SELECT '$date' as start")
    ->implode(' UNION ALL ');

$result = DB::table(DB::raw("($tempTableSql) as target_dates"))
    ->leftJoin(
        DB::raw("(
            SELECT count(start) as count, start
            FROM availabilities
            WHERE start IN ('" . implode("','", $targetDates) . "')
            GROUP BY start
        ) as availability_counts"),
        'target_dates.start',
        '=',
        'availability_counts.start'
    )
    ->select('target_dates.start', DB::raw('COALESCE(availability_counts.count, 0) as count'))
    ->get();

执行后会返回所有日期的count，包括没有匹配的日期显示0。

四、通用安全封装（避免SQL注入） #

如果目标值来自用户输入，上面的直接拼接SQL有注入风险，推荐用参数绑定的方式封装成通用函数：

function getAvailabilityCounts(array $targetValues, string $column = 'start')
{
    $placeholderCount = count($targetValues);
    if ($placeholderCount === 0) {
        return collect();
    }

    // 生成临时表的占位符和绑定参数
    $tempPlaceholders = implode(' UNION ALL ', array_fill(0, $placeholderCount, "SELECT ? as $column"));
    $bindings = $targetValues;

    // 生成统计子查询的占位符
    $inPlaceholders = implode(',', array_fill(0, $placeholderCount, '?'));

    return DB::table(DB::raw("($tempPlaceholders) as target_values"), $bindings)
        ->leftJoin(
            DB::raw("(
                SELECT count($column) as count, $column
                FROM availabilities
                WHERE $column IN ($inPlaceholders)
                GROUP BY $column
            ) as counts"),
            "target_values.$column",
            '=',
            "counts.$column"
        )
        ->select("target_values.$column", DB::raw('COALESCE(counts.count, 0) as count'))
        ->addBinding($bindings, 'join') // 给子查询绑定参数
        ->get();
}

// 使用示例
// 数字场景
$numberResult = getAvailabilityCounts([100,200,300,400]);
// 日期场景
$dateResult = getAvailabilityCounts(['2018-09-10', '2018-09-18', '2018-09-16','2018-09-15']);

这个函数可以兼容数字、日期等不同类型的目标值，同时通过参数绑定避免了SQL注入风险。

内容的提问来源于stack exchange，提问作者Antonio Gocaj