Laravel – Fastest way to get sum of one column from many results?

  laravel, sql

I’m building a web app, and on this web app the user page displays the user’s comment karma (works exactly like reddit, it’s basically the sum of the total score of all their comments).

So in my user model I have a function to calculate this:

    public function comments()
    {
      return $this->hasMany(Comment::class, 'created_by', 'id');
    }
    public function commentKarma()
    {
      return $this->comments->where('anonymous', 0)->sum('total_score');
    }   

However, I’m noticing that in my dev environment (I’m using laravel homestead for reference), this query goes very slow.

So for example, for this user that only has 174 comments, the query is taking up almost 5 seconds.

select * from comments where comments.created_by = 2 and comments.created_by is not null
4.62s

I don’t want to imagine how long this query would take for a user that would have thousands of comments. What would be the best way to speed this up?

I was thinking of maybe setting up a task schedule that would automatically calculate all the users karma every hour or something, but that doesn’t seem very optimal either. Thoughts?

Source: Laravel

Leave a Reply