how to get sum of deep hasmany relation.

how can I get sum of deep hasmany relationship ?

I have 3 models ( Client / Banner / BannerLog )

Each relationship is like this :

Client -> Banner : hasMany 

Banner -> BannerLog : hasMany

BannerLog has view_count, click_count. that would increase every time user access or click.

I can get all clients with their banners and banner logs like this :

$clients = Client::query()
        ->with('banners')
        ->with(['banners.banner_logs' => function ($q) {
            $q->whereBetween('reg_date', [$this->start, $this->end]);
        }])
        ->get();

But I would like to get sum of view_count, click_count in banner_logs table.

How can I do this ?

binggle

3 years ago

Best Answer

Level 3

I solved problem.

        $clients = Client::query()
                ->with(['banners'=> function( $q ) use ($start, $end)  {
                    $q->withSum (['banner_logs as view_count_sum' => fn ($query) => $query->whereBetween('reg_date', [$start, $end])], 'view_count')
                        ->withSum (['banner_logs as click_count_sum' => fn ($query) => $query->whereBetween('reg_date', [$start, $end])], 'click_count');
                }])
                ->orderBy('id', 'desc')
                ->paginate();

For someone who looks for same situation.

Please or to participate in this conversation.