Is there a right approach to calculate the average of a rating system?

I'm curious what your thoughts are about this.

Let's say I have an offering table (for some items) with a rating system from 1-5. How would you implement it when you would like to display the average rating of an offer? I see two possible ways:

I could add those two columns in the offer table:

rate_count : 0,
rate_average: 0

And for each new review in the rating table, I would recalculate the rate_count and rate_average.

The second possibility is that I'm calculating the average on the fly with the aggregate methods avg() and count().

Personally, I feel like it's not a good idea to calculate these numbers in every query. However, in one episode Jeff is explaining how to implement a "favorite/liking" system. In there he counts the sum of likes with

 return $this->favorites->count();

How would you handle this?

Cronix

7 years ago

Level 67

I generally use separate tables for the ratings (or same table) and just update that. It's a lot faster and more efficient to grab a single specific row with the rating than doing aggregate functions on the fly on a db with millions of rows. I guess it really depends on the app and how much data you're talking about, or are anticipating.

1 like

Tray2

7 years ago

Level 73

I've done this in one of my apps

public function score()
    {
        return $this->hasMany(Score::class);
    }


    public function getScoreAttribute()
    {
        return $this->score()->average('score');
    }

I have a scores table and a pivot table item_scores where each score is connected to an item_id and a score

It works pretty nicely.

1 like

shez1983

7 years ago

Best Answer

Level 23

the idea is that things you can calculate should not be stored in the DB ideally. if you have the right indexes the query can take < second. If you store calculated data then thats an extra headache for you to make sure it is correct all the time.

Now depending on the data/tables you could:

update these columns rate_count, rate_average every 5/10 minutes (using a cron job/schedule command)
or cache them via REDIS (so with this you are not creating a column to store avgs) again using a schedule command or on the fly and invalidate the cache when someone adds/update

1 like

shadrix

7 years ago

Level 12

Guys it's super hard for me to mark the right answer. Every response was helpful. But I feel bad if I don't mark anything. I choose the one was the lowest XP and because I really like the approach with redis.

Thank you for the help!

1 like

Please or to participate in this conversation.