php - WHERE 子句与 Laravel 中另一个表的关系

Question

我的 Laravel 应用程序中有几个表，一个称为“shipments”，另一个称为“payment_distributions”。

在我的发货中，有名为 pro_number 和余额的列。

在我的 payment_distributions 中，有名为shipment_id 和 amount 的列。

现在我有一个 Controller ，其中包含这部分代码:

}elseif($_GET['paymentStatus']=="Unpaid"){
    if(empty($_GET['pro_number'])){
            $shipment = NULL;
        }else{
        $shipment = $_GET['pro_number'];
        }

        if($_GET['startDate']){
            $startDate = $_GET['startDate'];
        }
        if($_GET['endDate']){
            $endDate = $_GET['endDate'];
        }
                $start = $_GET['startDate'];
                $end = $_GET['endDate'];
                $status = $_GET['paymentStatus'];
    $date = \Carbon\Carbon::today()->subDays(0);

    $shipments = Shipment::sortable()
    ->where([
        ['due_date','<=',date($date)],
        ['pro_number', 'LIKE', '%' . $shipment . '%'],
        ['balance','>','SELECT SUM(payments_distributions.amount) FROM payments_distributions WHERE payments_distributions.shipment_id = pro_number'],
    ])
    ->whereBetween('date', [$startDate, $endDate])
    ->whereNotIn('shipment_billing_status', [2,3])
    ->paginate(25);
    return view('shipments.accounts', compact('shipments','start','end','status'));

}

现在我的问题出现了，如果我将以下代码放入 MYSQL 中，它就会起作用:

SELECT SUM(payments_distributions.amount) FROM payments_distributions WHERE payments_distributions.shipment_id = 214050

将返回 SUM( payment_distributions.amount ): 300.00

所以，如果你查看我的代码中的这一行，这是唯一不起作用的，所以如果可以的话，这行代码对每个人来说都合适吗？我想说的是，发货行中的余额字段必须大于 payment_distributions 表中的 amount 字段之和，其中 payment_distributions 表中的 payment_id 字段等于发货表中的 pro_number 字段。

 ['balance','>','SELECT SUM(payments_distributions.amount) FROM payments_distributions WHERE payments_distributions.shipment_id = pro_number'],
    ]

更新(整个功能)

public function accountsQuery(){


if(($_GET['paymentStatus'] == "No Settle") or ($_GET['paymentStatus'] == "No Charge")){
            if($_GET['paymentStatus'] == "No Settle"){
                $status = 3;
            }elseif($_GET['paymentStatus'] == "No Charge"){
                $status = 2;
            }
                if(empty($_GET['pro_number'])){
                $shipment = NULL;
                }else{
                $shipment = $_GET['pro_number'];
                }

                if($_GET['startDate']){
                    $startDate = $_GET['startDate'];
                }
                if($_GET['endDate']){
                    $endDate = $_GET['endDate'];
                }
                        $start = $_GET['startDate'];
                        $end = $_GET['endDate'];
                        $status = $_GET['paymentStatus'];
                        $shipments = \App\Shipment::sortable()->where([ 
                            ['pro_number', 'LIKE', '%' . $shipment . '%'],
                            ['shipment_billing_status', 'LIKE', '%' . $status . '%'],
                        ])
                            ->whereBetween('date', [$startDate, $endDate])
                            ->paginate(25);
                return view('shipments.accounts', compact('shipments','start','end','status'));

}elseif($_GET['paymentStatus']=="Billed"){
    if(empty($_GET['pro_number'])){
            $shipment = NULL;
        }else{
        $shipment = $_GET['pro_number'];
        }

        if($_GET['startDate']){
            $startDate = $_GET['startDate'];
        }
        if($_GET['endDate']){
            $endDate = $_GET['endDate'];
        }
                $start = $_GET['startDate'];
                $end = $_GET['endDate'];
                $status = $_GET['paymentStatus'];
    $date = \Carbon\Carbon::today()->subDays(30);
    //AND TO MENTION WHERE SUM OF PAYMENTS TO THIS SHIPMENT < BALANCE DUE
    $shipments = Shipment::sortable()
    ->where([
        ['date','>=', date($date)],
        ['pro_number', 'LIKE', '%' . $shipment . '%'],
    ])
    ->whereBetween('date', [$startDate, $endDate])
    ->paginate(25);
    return view('shipments.accounts', compact('shipments','start','end','status'));

}elseif($_GET['paymentStatus']=="Unpaid"){
    if(empty($_GET['pro_number'])){
            $shipment = NULL;
        }else{
        $shipment = $_GET['pro_number'];
        }

        if($_GET['startDate']){
            $startDate = $_GET['startDate'];
        }
        if($_GET['endDate']){
            $endDate = $_GET['endDate'];
        }
                $start = $_GET['startDate'];
                $end = $_GET['endDate'];
                $status = $_GET['paymentStatus'];
    $date = \Carbon\Carbon::today()->subDays(0);

    $shipments = Shipment::sortable()
    ->where([
        ['due_date','<=',date($date)],
        ['pro_number', 'LIKE', '%' . $shipment . '%'],
    ])
    ->whereRaw('balance > SELECT SUM(p.amount) FROM payments_distributions p WHERE p.shipment_id = pro_number')
    ->whereBetween('date', [$startDate, $endDate])
    ->whereNotIn('shipment_billing_status', [2,3])
    ->paginate(25);
    return view('shipments.accounts', compact('shipments','start','end','status'));

}  
}

Answer 1

您应该能够将该子查询用作 whereRaw 表达式的一部分:

->where([
    ['due_date','<=',date($date)],
    ['pro_number', 'LIKE', '%' . $shipment . '%']])
->whereRaw('balance > SELECT SUM(p.amount) FROM payments_distributions p WHERE p.shipment_id = pro_number')
->whereBetween('date', [$startDate, $endDate])
...

但是，我怀疑我们可以重写您的查询以消除 WHERE 子句中的相关子查询。