Laravel中$os_only=1时未支付/部分支付销售记录查询问题求助

原代码核心问题 #

1. 内连接丢失未支付记录：使用join()（内连接）会过滤掉没有对应支付记录的销售单，这类完全未支付的记录无法被查询到
2. 条件逻辑写反：havingRaw('sales.total_amount < paid')的判断逻辑错误，应该是支付总额小于订单总额才属于未/部分支付
3. 语法错误：groupBy('sales.invoice');后面多了分号，导致后续havingRaw报错
4. 搜索逻辑漏洞：orWhere后追加where('invoice_status', 1)会破坏查询逻辑，导致部分搜索条件被错误过滤

修复后的完整代码 #

$draw = intval($request->draw);
$start = intval($request->start);
$length = intval($request->length);
$order = $request->order;
$search = $request->search;
$search = $search['value'];
$col = 0;
$dir = "";

if (!empty($order)) {
    foreach ($order as $o) {
        $col = $o['column'];
        $dir = $o['dir'];
    }
}

if ($dir != "asc" && $dir != "desc") {
    $dir = "desc";
}

$valid_columns = [
    0 => '',
    1 => 'confirmed_at',
    2 => 'invoice',
    3 => 'client_name',
    4 => 'total_amount',
    5 => '',
    6 => '',
    7 => '',
    8 => '',
];

$order = isset($valid_columns[$col]) ? $valid_columns[$col] : null;

if ($os_only == 1) {
    // 使用左连接保留无支付记录的订单，用COALESCE处理无支付时的null值为0
    $sales = Sale::selectRaw('sales.*, COALESCE(sum(sales_payments.payment_amount), 0) as paid')
        ->leftJoin('sales_payments', 'sales.invoice', '=', 'sales_payments.invoice')
        ->where('invoice_status', 1)
        ->groupBy('sales.id') // 按销售单主键分组，避免MySQL严格模式报错
        ->havingRaw('COALESCE(sum(sales_payments.payment_amount), 0) < sales.total_amount');
} else {
    $sales = Sale::select('sales.*, clients.client_name')
        ->where('invoice_status', 1)
        ->join('clients', 'sales.client_id', '=', 'clients.uid');
}

if (!empty($search)) {
    // 用闭包包裹搜索条件，避免orWhere破坏invoice_status的过滤逻辑
    $sales->where(function ($query) use ($search, $valid_columns) {
        $x = 0;
        foreach ($valid_columns as $sterm) {
            if (!empty($sterm)) {
                if ($x == 0) {
                    $query->where($sterm, 'LIKE', '%' . $search . '%');
                } else {
                    $query->orWhere($sterm, 'LIKE', '%' . $search . '%');
                }
                $x++;
            }
        }
    });
}

if ($order != null) {
    $sales->orderBy($order, $dir);
} else {
    $sales->orderBy('uid', 'DESC');
}

$query = $sales->offset($start)->limit($length)->get();

关键修复点说明 #

• 左连接替代内连接：leftJoin()确保所有符合状态的销售单都被保留，即使没有支付记录
• COALESCE处理空值：将无支付记录时的sum()结果从null转为0，统一判断逻辑
• 修正having条件：判断支付总额小于订单总额，覆盖未支付（paid=0）和部分支付场景
• 搜索逻辑优化：用闭包包裹搜索条件，保证invoice_status=1始终生效，不会被orWhere干扰
• 语法错误修正：移除groupBy后的多余分号

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