Subqueries collection sum or multiple queries?

Hello, I have subquery, that I need the get sum for two fields in it. The first example is with one query and sum on collection and the second one is with three queries. Which is better to use for performance with huge amount of data:

Get the collection and sum with the function?
Get only the IDs and sum with another query?

Example code with collection:

 $valuationGropBidsAccepted = ValuationGroup::withWhereHas('bids', function ($query) use ($user) {
            $query
                ->where('status', '>', 2)
                ->where('user_uuid', $user->uuid);
        })
            ->withSum('valuations', 'area')
            ->withSum('bids', 'value')
            ->get();

        $acceptedBidsArea = $valuationGropBidsAccepted->sum(function ($group) {
            return $group->valuations_sum_area;
        });

Example with queries:

           $query
               ->where('status', '>', 2)
               ->where('user_uuid', $user->uuid);
       })
		->select(DB::RAW(' SUM(`value`) as sum'));

$sum = Bid::where('user_uuid',  $user->uuid)->select(DB::RAW(' SUM(`value`) as sum')) ;

Need need to know which is better to use for performance. Thank you!

LaryAI

3 years ago

Level 58

Based on the provided code, it seems like the first option of using a subquery and summing the collection would be more efficient. This is because it involves only one query to the database, whereas the second option involves three separate queries.

However, to be sure, it would be best to test both options with a large amount of data and measure the performance.

Here is an updated version of the first example code with some minor improvements:

$valuationGroupBidsAccepted = ValuationGroup::with(['bids' => function ($query) use ($user) {
        $query->where('status', '>', 2)
              ->where('user_uuid', $user->uuid);
    }])
    ->withSum('valuations', 'area')
    ->withSum('bids', 'value')
    ->get();

$acceptedBidsValueSum = $valuationGroupBidsAccepted->sum('bids_sum_value');
$acceptedBidsAreaSum = $valuationGroupBidsAccepted->sum('valuations_sum_area');

Note that I've used the with method to eager load the bids relationship, which should improve performance. I've also simplified the sum calls by passing the column name directly instead of using a closure.

Please or to participate in this conversation.