CODEWITHSUNDEEP
phplaravellaravel-5eloquenteager-loading
jqner
jqner

Reputation: 11

Laravel eager loading with conditional select not work

I am trying to select sum of my column value with eager loading.

My query is 

$transactions = Service::with(['transactions' => function($query) use ($queryData){
        $query->whereIn('status',['Success','Pending','Successful','Processing']);
        $query->whereDate('created_at','>=', $queryData);
        $query->addSelect(DB::raw("SUM(deducted) as adjustment"));
}]);

But I am not getting any sum with my response.

my resulted response look like 

"id": "3",
"name": "Service Name",
"transactions": [ ]

But It should be 

"id": "3",
"name": "Service Name",
"adjustment": 30

Upvotes: 0

Views: 296

Answers (2)

Watercayman
Watercayman

Reputation: 8168

You may wish to use the sum function on the collection rather than the raw SQL inside the query. Still only hit the DB once:

I don't know where the deducted sits, but if on the model (which it looks like from the above query) this will give you a sum of those transactions queried:

$adjustment= $transactions->sum('deducted');

or if on a relation

$adjustment= $transactions->sum(function ($transaction) {
    return $transaction->transaction?->sum('deducted');
});

You can also possibly get the related field with dot notation as well:

$adjustment= $transactions->sum('transaction.deducted');

These allow you to keep your object collection ($transactions, and the name and id fields as you noted necessary) and still get the sum from the original query.

Upvotes: 0

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

Use withCount():

$transactions = Service::withCount(['transactions as adjustment' => function($query) use ($queryData){
    $query->whereIn('status',['Success','Pending','Successful','Processing']);
    $query->whereDate('created_at','>=', $queryData);
    $query->select(DB::raw('SUM(deducted)'));
}]);

Upvotes: 1

Related Questions