How to improve performance of multiple count queries in a laravel view

Question

I'm working on a marketing application that allows users to message their contacts. When a message is sent, a new "processed_message" database entry is created. There is a list view that displays all campaigns and the number of messages sent, blocked and failed for each campaign. My problem is that this list view takes way too long to load after there are > 50 campaigns with lots of messages.

Currently each campaign has 3 computed attributes (messages_sent, messages_failed and messages_blocked) that are all in the Campaign model's "appends" array. Each attribute queries the count of processed_messages of the given type for the given campaign.

namespace App;

class Campaign
{
    protected $appends = [
        'messages_sent',
        'messages_blocked',
        'messages_failed'
    ];

    /**
     * @relationship
     */
    public function processed_messages()
    {
        return $this->hasMany(ProcessedMessage::class);
    }

    public function getMessagesSentAttribute()
    {
        return $this->processed_messages()->where('status', 'sent')->count();
    }

    public function getMessagesFailedAttribute()
    {
        return $this->processed_messages()->where('status', 'failed')->count();
    }

    public function getMessagesBlockedAttribute()
    {
        return $this->processed_messages()->where('status', 'blocked')->count();
    }
}

I also tried to query all of the messages at once or in chunks to reduce the number of queries but getting all of the processed_messages for a campaing at once will overflow memory and the chunking method is way too slow since it has to use offset. I considered using eager loading the campaigns with processed_messages but that would obviously use way too much memory as well.

namespace App\Http\Controllers;

class CampaignController extends Controller
{
    public function index()
    {
        $start = now();
        $campaigns = Campaign::where('user_id', Auth::user()->id)->orderBy('updated_at', 'desc')->get();
        $ids = $campaigns->map(function($camp) {
            return $camp->id;
        });

        $statistics = ProcessedMessage::whereIn('campaign_id', $ids)->select(['campaign_id', 'status'])->get();
        foreach($statistics->groupBy('campaign_id') as $group) {
            foreach($group->groupBy('status') as $messages) {
                $status = $messages->first()->status;
                $attr = "messages_$status";
                $campaign = $campaigns->firstWhere('id', $messages->first()->campaign_id);
                $campaign->getStatistics()->$attr = $status;
            }
        }

        return view('campaign.index', [
            'campaigns' => $campaigns
        ]);
    }
}

My main goal is to reduce the current page load time considerably (which can take anywhere from 30 seconds to 5 minutes when there are a bunch of campaigns).

Answer 1

You could use the withCount method to count all the objects without loading the relation.

Reference:

If you want to count the number of results from a relationship without actually loading them you may use the withCount method, which will place a {relation}_count column on your resulting models.

In your controller you could do this:

$count = Campaign::withCount(['processed_messages' => function ($query) {
    $query->where('content', 'sent');
}])->get();

You could do multiple counts in the same relationship too:

$campaigns = Campaign::withCount([
'processed_messages',
'processed_messages as sent_message_count' => function ($query) {
    $query->where('content', 'sent');
}],
'processed_messages as failed_message_count' => function ($query) {
    $query->where('status', 'failed');
}],
'processed_messages as blocked_message_count' => function ($query) {
    $query->where('status', 'blocked');
}])->get();

You can access the count with this:

echo $campaigns[0]->sent_message_count

Docs