本文详解 Laravel 5.8+ 中将原生 MySQL 多表 JOIN 聚合查询转换为 Query Builder 或原生 SQL 执行时的关键陷阱，重点解决 whereBetween() 误用导致空结果问题，并提供安全、可维护的两种推荐方案。
本文详解 laravel 5.8+ 中将原生 mysql 多表 join 聚合查询转换为 query builder 或原生 sql 执行时的关键陷阱，重点解决 `wherebetween()` 误用导致空结果问题，并提供安全、可维护的两种推荐方案。
在 Laravel 开发中，将复杂的原生 SQL（尤其是含多表 JOIN、条件聚合与时间范围判断）准确迁移到 Eloquent 或 Query Builder 时，极易因语法细节疏忽导致查询无声失败——最典型的表现就是返回空数组 []，而相同 SQL 在 phpMyAdmin 中却能正常执行。
? 核心问题：whereBetween() 不支持字段间动态比较
你当前使用的：
->whereBetween('tp.paid_on', ['cr.created_at', 'cr.closed_at'])实际生成的 SQL 是：
`tp`.`paid_on` BETWEEN 'cr.created_at' AND 'cr.closed_at'
注意：'cr.created_at' 被当作字符串字面量而非数据库字段，因此永远无法匹配任何 paid_on 值，导致 WHERE 条件恒假，最终无数据返回。
✅ 正确做法是使用 whereRaw() 显式声明字段级比较：
->whereRaw('tp.paid_on BETWEEN cr.created_at AND cr.closed_at')这会生成预期的字段间比较：
`tp`.`paid_on` BETWEEN `cr`.`created_at` AND `cr`.`closed_at`
✅ 推荐方案一：Query Builder（修正版）
use Illuminate\Support\Facades\DB;
$startDate = '2022-06-01';
$results = DB::table('cash_registers as cr')
->select(
'b.name as business_location_name',
'u.first_name as cashier_first_name',
'u.last_name as cashier_last_name',
'cr.location_id',
DB::raw('SUM(CASE WHEN tp.is_return = "0" AND tp.method = "cash" THEN tp.amount ELSE 0 END) as totalCash'),
DB::raw('SUM(CASE WHEN tp.is_return = "1" AND tp.method = "cash" THEN tp.amount ELSE 0 END) as totalReturn'),
DB::raw('SUM(CASE WHEN tp.is_return = "0" AND tp.method = "card" THEN tp.amount ELSE 0 END) as 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('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') // ✅ 合并为单个 groupBy()，更清晰
->orderBy('cr.location_id', 'asc')
->orderBy('cr.user_id', 'asc')
->get();✅ 推荐方案二：原生 SQL + 参数绑定（更安全、更易调试）
当 Query Builder 难以清晰表达复杂逻辑时，直接使用 DB::select() 是更优选择——它完全保留 SQL 可读性，同时通过参数绑定防止注入：
use Illuminate\Support\Facades\DB;
$startDate = '2022-06-01';
$results = DB::select(
<<<SQL
SELECT
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) AS totalCash,
SUM(CASE WHEN tp.is_return = '1' AND tp.method = 'cash' THEN tp.amount ELSE 0 END) AS totalReturn,
SUM(CASE WHEN tp.is_return = '0' AND tp.method = 'card' THEN tp.amount ELSE 0 END) AS totalCard,
cr.created_at,
cr.closed_at
FROM cash_registers AS cr
LEFT JOIN transaction_payments AS tp ON cr.user_id = tp.created_by
LEFT JOIN users AS u ON u.id = cr.user_id
LEFT JOIN business_locations AS b ON b.id = cr.location_id
WHERE tp.paid_on BETWEEN cr.created_at AND cr.closed_at
AND cr.status = 'close'
AND cr.created_at LIKE ?
GROUP BY cr.location_id, cr.user_id
ORDER BY cr.location_id ASC, cr.user_id ASC
SQL,
[$startDate . '%'] // ✅ 安全绑定参数
);⚠️ 注意事项与最佳实践
- 避免硬编码字符串：is_return = '0' 中的 '0' 是字符串，确保数据库字段类型一致（如 TINYINT(1) 应改用 tp.is_return = 0）；
- JOIN 顺序与 NULL 处理：LEFT JOIN 下 tp.paid_on 可能为 NULL，BETWEEN 在 NULL 时返回 NULL（即不满足），若需包含无支付记录的收银台，应调整逻辑或使用 COALESCE；
- 性能提示：对 cr.created_at LIKE '2022-06-01%' 建议确保该字段有索引；BETWEEN 涉及函数计算，大数据量时考虑添加复合索引（如 (status, created_at, closed_at)）；
- 调试技巧：启用 Laravel 查询日志（DB::enableQueryLog()）或使用 DB::getQueryLog() 查看实际执行 SQL，快速定位差异。
掌握 whereRaw() 的适用场景与原生 SQL 的安全绑定，是写出健壮、可维护 Laravel 数据层代码的关键一步。
