CODEWITHSUNDEEP
laravel
TCS
TCS

Reputation: 779

Laravel multiple relationships with calculations

Trying to substraction beyween two relationship results. But couldn't think, how it can be possible.

$customer_index = Customer::where('firm_id', $firm_id)
    ->withCount(['orders as orders_total' => function($q){
        $q->select(DB::raw("SUM(total-discount) as paidsum"));
        $q->where('status', 1);
    },
    'receipts as receipts_total' => function($q){
        $q->select(DB::raw("SUM(price) as paidsum"));
    }])
    ->select('*', '(orders_total-receipts_total) as result')
    ->orderBy('id', 'desc');

It doesn't work of course. But how can be possible to work? I need to calculate; orders total. and calculate receipts total, and make substraction betweek them. And return this as a column in my json response.

Upvotes: 1

Views: 340

Answers (1)

Kevin Bui
Kevin Bui

Reputation: 3045

You might want to try this:

$results = Customer::where('firm_id', $firm_id)
    ->with([
        'orders' => function ($query) {
            $query->where('status', 1);
        },
        'receipts'
    ])
    ->latest('id')
    ->get()
    ->each(function ($customer) {
        $customer->orders_total = $customer->orders->sum->total - $customer->orders->sum->discount;
        $customer->receipts_total = $customer->receipts->sum->price;
        $customer->result = $customer->orders_total - $customer->receipts_total;
    });

Upvotes: 3

Related Questions