Laravel Eloquent Eager Load with Sums

1
votes

I am trying to figure out the best way to eager load a lot of data so that my project can load quickly and easily. As you can see below there are lots of relationships between our objects, and each level will need sums calculated and displayed many times. I am wondering how to quickly load and display this data.

Relationships: 

Website has campaigns, groups, keywords, valid_click_ads, facebook_ads, google_ads, yahoo_ads
Campaign has groups, keywords, valid_click_ads, facebook_ads, google_ads, yahoo_ads
Group has keywords, valid_click_ads, facebook_ads, google_ads, yahoo_ads
Keywords has valid_click_ads

Here is the data I need. I am looking to get this data on basically every relationship level (all data for WHERE created_at = today)

Object -> name
          sum(valid_click_ads->revenue)
          sum(facebook_ads->spend)
          sum(yahoo_ads->spend)
          sum(google_ads->spend)
          sum(valid_click_ads->tq WHERE tq != -1)
          count(tq)
          sum(CASE(tq = -1, 0, 1))
          sum(facebook_ads->impressions)
          sum(yahoo_ads->impressions)
          sum(google_ads->impressions)
          sum(facebook_ads->clicks)
          sum(yahoo_ads->clicks)
          sum(google_ads->clicks)
          sum(valid_click_ads->clicks)

I would be happy to provide more information if necessary.

2
laraveleloquenteager-loading
You can create new relationships that do the sum() as part of their definition. See stackoverflow.com/questions/27680367/…. Caching may be an option, as well. - ceejayoz
Do you have an example of how this would work with larger groups of relationships and data? And from within a specific date range? - LucyTurtle
You can apply all the Eloquent functions to a relationship's function. You could add a whereBetween clause to the relationship's definition for date ranges without issues. They still have the potential to be computationally expensive queries that slow your DB - you may eventually consider having these sorts of analytics roll-ups be stored in another manner. - ceejayoz
How else would I store this data? - LucyTurtle
@ceejayoz You answer actually helped me a lot. Thank you! - LucyTurtle

2 Answers

3
votes

If I understand your problem correctly, you can use something like this:

Website::withCount(['valid_click_ads' => function($query) {
    $query->select(DB::raw('sum(revenue)'))
        ->where('created_at', ...);
}])->get();
0
votes

Using this article, I was able to put together exactly what I needed like so...

Website, Campaign, and Group Models:

public function getValidClickSumsAttribute() {
    if ( ! $this->relationLoaded('validClickSums')) 
        $this->load('validClickSums');

    $related = $this->getRelation('validClickSums');

    return $related;
}

public function facebookSums() {
    return $this->hasOne('App\Models\FacebookAd')->selectRaw('sum(spend) as spend, sum(clicks) as clicks, website_id')->groupBy('website_id');
}

public function getFacebookSumsAttribute() {
    if ( ! $this->relationLoaded('facebookSums')) 
        $this->load('facebookSums');

    $related = $this->getRelation('facebookSums');

    return $related;
}

public function googleSums() {
    return $this->hasOne('App\Models\GoogleAd')->selectRaw('sum(spend) as spend, sum(clicks) as clicks, website_id')->groupBy('website_id');
}

public function getGoogleSumsAttribute() {
    if ( ! $this->relationLoaded('googleSums')) 
        $this->load('googleSums');

    $related = $this->getRelation('googleSums');

    return $related;
}

public function yahooSums() {
    return $this->hasOne('App\Models\YahooAd')->selectRaw('sum(spend) as spend, sum(clicks) as clicks, website_id')->groupBy('website_id');
}

public function getYahooSumsAttribute() {
    if ( ! $this->relationLoaded('yahooSums')) 
        $this->load('yahooSums');

    $related = $this->getRelation('yahooSums');

    return $related;
}


Keyword Model:

public function validClickSums() {
    return $this->hasOne('App\Models\ValidClickAd')->selectRaw('sum(impressions) as impressions, sum(revenue) as revenue, sum(clicks) as clicks, count(tq) as unscored_tq_count, sum(case when tq >= 0 then 1 else 0 end) as scored_tq_count, sum(case when tq >= 0 then tq else 0 end) as tq, keyword_id')->groupBy('keyword_id');
}

public function getValidClickSumsAttribute() {
    if ( ! $this->relationLoaded('validClickSums')) 
        $this->load('validClickSums');

    $related = $this->getRelation('validClickSums');

    return $related;
}


Controller:

    $websites = Website::select('name', 'id')->
        with(
            array(
                'validClickSums' => function( $query ) {
                    $query->where( 'user_id', '=', 2 );
                },
                'facebookSums' => function( $query ) {
                    $query->where( 'user_id', '=', 2 );
                },
                'googleSums' => function( $query ) {
                    $query->where( 'user_id', '=', 2 );
                },
                'yahooSums' => function( $query ) {
                    $query->where( 'user_id', '=', 2 );
                },
                'campaigns' => function( $query ) {
                    $query->where( 'user_id', '=', 2 )->with(
                        array(
                            'validClickSums' => function( $query ) {
                                $query->where( 'user_id', '=', 2 );
                            },
                            'facebookSums' => function( $query ) {
                                $query->where( 'user_id', '=', 2 );
                            },
                            'googleSums' => function( $query ) {
                                $query->where( 'user_id', '=', 2 );
                            },
                            'yahooSums' => function( $query ) {
                                $query->where( 'user_id', '=', 2 );
                            },
                            'traffic_source',
                            'groups' => function( $query ) {
                                $query->with(
                                    array(
                                        'validClickSums' => function( $query ) {
                                            $query->where( 'user_id', '=', 2 );
                                        },
                                        'facebookSums' => function( $query ) {
                                            $query->where( 'user_id', '=', 2 );
                                        },
                                        'googleSums' => function( $query ) {
                                            $query->where( 'user_id', '=', 2 );
                                        },
                                        'yahooSums' => function( $query ) {
                                            $query->where( 'user_id', '=', 2 );
                                        },
                                        'keywords' => function( $query ) {
                                            $query->with(
                                                array(
                                                    'validClickSums' => function( $query ) {
                                                        $query->where( 'user_id', '=', 2 );
                                                    },
                                                    'sourceSums' => function( $query ) {
                                                        $query->where( 'user_id', '=', 2 );
                                                    }
                                                )
                                            );
                                        }
                                    )
                                );
                            }
                        )
                    );
                }
            )
        )->get();