CODEWITHSUNDEEP
phpmysqllaravellaravel-5
Matthew
Matthew

Reputation: 1655

WHERE clause with relationship to another table in Laravel

I've got a couple of tables in my Laravel application, one called shipments and the other called payments_distributions.

In my shipments, I have columns called pro_number and balance.

In my payments_distributions I have columns called shipment_id and amount.

Now I have a controller which has this portion of code in it:

}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'));

}

Now my issue arises in that the following code works if I put it into MYSQL:

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

Will return SUM(payments_distributions.amount): 300.00

So, if you go to the line in my code, this is the only thing that doesn't work, so if you could, does this line of code look right to everyone? All I'm trying to say that the balance field from a shipment row must be larger than the sum of amount fields in the payment_distributions table where the shipment_id field in the payment_distributions table equals the pro_number field in the shipments table.

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

Update (the entire function)

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'));

}  
}

Upvotes: 0

Views: 998

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521389

You should be able to use that subquery as part of a whereRaw expression:

->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])
...

However, I suspect that we can probably rewrite your query to do away with this correlated subquery in the WHERE clause.

Upvotes: 1

Related Questions