Laravel5.8查询返回空数组但原生MySQL执行正常的问题咨询

查询返回空数组的核心错误出在whereBetween条件的写法上：

• Laravel查询构造器默认会将传入whereBetween的数组值作为字符串字面量通过PDO参数绑定传入SQL，不会自动识别为数据库字段名。
• 你写的->whereBetween('tp.paid_on',['cr.created_at', 'cr.closed_at'])最终生成的SQL条件等价于tp.paid_on BETWEEN 'cr.created_at' AND 'cr.closed_at'，也就是拿时间字段和两个固定字符串做区间匹配，自然查不到任何符合条件的数据，和你在phpMyAdmin执行的原生SQL逻辑完全不一致。

将需要引用数据库字段的区间判断改为whereRaw手动写条件，避免参数绑定把字段名转成字符串，修正后的完整代码如下：

$startDate = "2022-06-01";
$results3 = DB::table('cash_registers as cr')
    ->select(DB::raw('b.name as business_location_name , u.first_name as cashier_first_name , u.last_name as cashier_last_name , cr.location_id , SUM(CASE WHEN tp.is_return = "0" AND tp.method = "cash" THEN tp.amount ELSE 0 END) totalCash, SUM(CASE WHEN tp.is_return = "1" AND tp.method = "cash" THEN tp.amount ELSE 0 END) totalReturn , SUM(CASE WHEN tp.is_return = "0" AND tp.method = "card" THEN tp.amount ELSE 0 END) totalCard , cr.created_at , cr.closed_at'))
    ->leftJoin('transaction_payments as tp','tp.created_by', '=', 'cr.user_id')
    ->leftJoin('users as u','u.id', '=', 'cr.user_id')
    ->leftJoin('business_locations as b','b.id', '=', 'cr.location_id')
    // 核心修正：用whereRaw写字段间的区间判断，不做字符串转义
    ->whereRaw('tp.paid_on BETWEEN cr.created_at AND cr.closed_at')
    ->where('cr.status', '=' , 'close')
    ->where("cr.created_at", "like", $startDate.'%')
    ->groupBy('cr.location_id', 'cr.user_id')
    ->orderBy('cr.location_id', 'asc')
    ->orderBy('cr.user_id', 'asc')
    ->get();

• 如果你的MySQL开启了ONLY_FULL_GROUP_BY模式，当前SELECT子句中b.name、u.first_name、u.last_name、cr.created_at、cr.closed_at几个字段没有出现在GROUP BY中，会抛出SQL语法错误，此时可以要么将这些字段加入GROUP BY，要么使用聚合函数包裹（比如ANY_VALUE(b.name) as business_location_name）适配SQL模式。
• 对datetime类型的cr.created_at做前缀LIKE匹配虽然能达到按日期筛选的效果，也可以替换为->whereDate('cr.created_at', $startDate)，语义更清晰。

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