CODEWITHSUNDEEP
phpmysqllaraveleloquentquery-optimization
Anibal Cardozo
Anibal Cardozo

Reputation: 83

How to sum a colum from a related model efficiently on Laravel

Ok I got this table

affiliates_referral_clicks
id | affiliate_id | clicks | date
1  | 1            | 10     | 2021-07-14
2  | 1            | 2      | 2021-07-11
3  | 2            | 1      | 2021-07-11
4  | 2            | 14     | 2021-07-10
...

Of course my Model Affiliate has a relationship with referralClicks

Affiliate.php

public function referralClicks(){
        return $this->hasMany(AffiliateReferralClick::class,'affiliate_id');
    }

Now I want to bring all Affiliates with the SUM of all their clicks that have a date between a given date. I implemented it like this

$affiliate = Affiliate::with(['referralClicks' => function($query) use($params) { 
                $query->whereDate('date','>=', $params['dateFrom'])
                    ->whereDate('date','<=', $params['dateTo']) 
                    ->select('clicks')   
                ;
            }])->get();

foreach ($affiliates as $affiliate){
       $affiliate->totalClicks = $affiliate->referralClicks->sum('clicks');
}

this works fine, but since the affiliates_referral_clicks table is waaaay too big and the request ends up being too slow, I think if you do the query without using Eloquent's helpers you can get a much faster query.

So my question would be...how can I do the same I just did but with raw querys (or whatever the most efficient way is)? Im using a MySQL DB I hope you guys can help me!

Upvotes: 1

Views: 144

Answers (1)

shaedrich
shaedrich

Reputation: 5735

Haven't tried that yet but that's how I'd solve this (if we assume, you only need the sum and nothing else from the relationship):

$affiliate = Affiliate::withSum(['referralClicks.clicks as totalClicks' => function($query) use($params) { 
    $query->whereDate('date','>=', $params['dateFrom'])
          ->whereDate('date','<=', $params['dateTo']) 
          ->select('clicks')   
    ;
}])->get();

Upvotes: 2

Related Questions