Select sum of realtion's attributes in query

I really can't figure it out although it's fairly simple... I've got two tables: orders and revenues and I want to add a global scope to my Order model to load the sum of its paid revenues as an attribute. I tried joining and selecting the sum but nothing seems to change. The closest I've come to building the query was the following:

static::addGlobalScope('withPaidFromCustomer', function($builder){
            $builder->leftJoin(DB::raw('select order_id, is_paid, sum(price) as paid_from_customer FROM revenues'), function($j){
                $j->on('revenues.order_id', '=', 'orders.id')->where('revenues.is_paid', true);
            });
        });

I hope it's clear. I just want to get the paid_from_customer attribute in my model after querying. Thansk in advance!

EDIT: The reason I'm not using mutators for this is that I need the aggregate for future query builder extensions, so an actual model is never loaded. I've opened an alternative thread on the real problem that's preventing me from implementing the global scope here: https://laracasts.com/discuss/channels/eloquent/weird-behaviour-on-paginating-with-join-and-having-clause

Adcade

7 years ago

Level 1

Maybe using appends could work.

In your Order model, add`

protected $appends = ['paid_from_customer'];

public function getPaidFromCustomerAttribute()
{
    return Revenue::where([['order_id', $this->id],['is_paid', true]])->sum('price'); 
}

(the return query may need to be adjusted to your structure)

DavidPetrov

7 years ago

Best Answer

Level 2

@ADCADE - Thank you for the suggestion, I do actually already have an attribute getter set up for the purpose. Didn't think whether the $appends clause would change anything but it appears (as stated in the documentation) that it only modifies the loaded model serialization to array (adds the custom attributes), so it can't affect the query builder. P.S. Sorry I really had forgotten to mention in my thread that I need this for query builder's sake. That's why I wanted to use a global scope in order to have selected the aggregate for extended queries for the Order model. So I really don't need anything related to the loaded model instance here, it's all about query optimization.

EDIT: Question remains unanswered, a query workaround and more complex raw expressions solved the issue.

Please or to participate in this conversation.