I have two table purchases and purchase_transactions

purchases table

purchase_transactions table

I want to query for purchase due list.

For that I make a query like below in Laravel Controller

DB::table("purchase_transactions")
    ->join("purchases", "purchases.purchases_id", "=", "purchase_transactions.purchases_id")
    ->select(
        "purchase_transactions.purchases_id",
        "purchases.total_amount as total_amount",
        "purchases.net_payable_amount as net_payable_amount",

        DB::raw("(SELECT SUM(purchase_transactions.paid_amount) FROM purchase_transactions
        WHERE purchase_transactions.purchases_id = purchases.purchases_id)"),

        "purchase_transactions.due_amount as due_amount",
    )
    ->where("purchase_transactions.id","=", $purchase_due_id[$i]->id)
    ->get();

But can't get the exact formatted result. Here is the query result

[
    [
        [
            {
                "purchases_id": "202203-011",
                "total_amount": 10,
                "net_payable_amount": 10,
                "(SELECT SUM(purchase_transactions.paid_amount) FROM purchase_transactions\n WHERE purchase_transactions.purchases_id = purchases.purchases_id)": 5,
                "due_amount": 5
            }
        ]
    ],
    [
        [
            {
                "purchases_id": "202203-010",
                "total_amount": 20,
                "net_payable_amount": 20,
                "(SELECT SUM(purchase_transactions.paid_amount) FROM purchase_transactions\n                                        WHERE purchase_transactions.purchases_id = purchases.purchases_id)": 15,
                "due_amount": 5
            }
        ]
    ]
]

Where is the problem? Anybody Help Please?

CodePudding user response：

You can alias the expression:

DB::raw("(SELECT SUM(purchase_transactions.paid_amount) FROM purchase_transactions WHERE purchase_transactions.purchases_id = purchases.purchases_id) as sum"),