MailerController.php

protected function loadCounters(Collection $mails)
    {
        $mails->load([
            'eTrackings' => function ($query) {
                return $query->select(['mailer_id'])
                    ->selectRaw('SUM(`clicks`) AS `clicks`')
                    ->groupBy('mailer_id');
            },
            'ePixels' => function ($query) {
                return $query->select(['mailer_id'])
                    ->selectRaw('COUNT(`views`) AS `views`')
                    ->selectRaw('SUM(`views`) AS `sum_views`')
                    ->groupBy('mailer_id');
            },
            'eUnsubscribers' => function ($query) {
                return $query->select(['mail_id'])
                    ->selectRaw('COUNT(*) AS `count`')
                    ->groupBy('mail_id');
            }
        ]);

        $mails->transform(function (MailerMessageEntity $mail) {
            $mail->counters = collect([
                'clicks' => $mail->eTrackings->sum('clicks'),
                'views' => $mail->ePixels->sum('views'),
                'sum_views' => $mail->ePixels->sum('sum_views'),
                'unsubscribers' => $mail->eUnsubscribers->sum('count')
            ]);

            return $mail;
        });
    }