DavidPetrov
4 months ago

Select sum of realtion's attributes in query

Posted 4 months ago by DavidPetrov

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

Please sign in or create an account to participate in this conversation.