Laravel Eloquent Eager Load Sums

Question

I am using eager load to load data before loading a page to make the load time faster.

$websites = Website::with('valid_click_ads',
                          'facebook_ads',
                          'google_ads')->get();

I was wondering if there was a way to get column summations this way. Ex:

$websites = Website::with('valid_click_ads:sum(clicks),sum(revenue)',
                          'facebook_ads:sum(clicks),sum(impressions)',
                          'google_ads:sum(clicks),sum(impressions)')->get();

This would make that load time even better! Thank you!

Answer 1

There's nothing like this but you can use withCount method as a wrapper for sum this way :

$websites = Website::withCount(['valid_click_ads'=>function($query){
                         $query->select( DB::raw( "COALESCE(SUM(clicks),0)" ) );
                       },
                      'facebook_ads'=>function($query){
                         $query->select( DB::raw( "COALESCE(SUM(clicks),0)" ) );
                       },
                      'google_ads'=>function($query){
                         $query->select( DB::raw( "COALESCE(SUM(clicks),0)" ) );
                       }])->get();

COALESCE is a SQL function to return sum if exists else returns 0 instead of returning null.

UPDATE : If you want to retrieve separate summation columns you have to define an index for each of them and alias them like this :

$websites = Website::withCount(['valid_click_ads as firstRes'=>function($query){
                     $query->select( DB::raw( "COALESCE(SUM(col1),0)" ) );
                   },
                   'valid_click_ads as secondRes'=>function($query){
                     $query->select( DB::raw( "COALESCE(SUM(col2),0)" ) );
                   }
                  ])->get();

etc..