Converting collection sum to efficient query

Hello all!

Firstly I have a "companies" table that has a "has many" relationships with a "purchases" table. The "purchases" has a has a "has many" relationship to an "items" table. On each item I store the cost of that item at the time of purchase. I have a method on my companies "getTotalSpent" which works out how much the company has spent across all purchases. My current method works but I think there is probably a more efficient way.

public function getTotalSpent(): int 
{
		return $this->purchases()
         		->with('items')            
            	->get()
		        ->pluck('items')
            	->flatten()
            	->unique()
          		->sum('cost');
}

Any help to optimise this would be appreciated. Thanks

webrobert

3 years ago

Best Answer

Level 51

you can just get the sum using an aggregate function

https://laravel.com/docs/10.x/eloquent-relationships#other-aggregate-functions

$this->purchases = Purchase->withSum('items as total','cost') ...
// or
Purchase->withSum([
    'items' => fn ($q) => $q->distinct(), // not sure you need this?
    'cost'
])
...

then

return $this->purchases->sum('total');

jlcain3

3 years ago

Level 3

@webrobert Thanks for coming back on this. I will give it a try later today :)

jlcain3

3 years ago

Level 3

It works, thank you!

1 like

Please or to participate in this conversation.