Recently I was working on a project where it looked something like this: One model (movie) had a one-to-many relation with another model (user_rating), but I was only interested about the average of the ratings of the users.
My initial approach was to do something like:
# frozen_string_literal: true class Movie < Sequel::Model include ActiveModel::Serialization one_to_many :movie_ratings plugin :association_dependencies, movie_ratings: :destroy nested_attributes :movie_ratings def avg_rating return nil if movie_ratings.empty? sum_of_ratings = movie_ratings.sum(:rating) sum_of_ratings.to_f / movie_ratings.length end end
Getting the average of the ratings by loading the records and doing the math was behaving fine for a single Movie but doing the same on a collection was super super slow even with eager loading. I had more tha 3.000.000 records for user_ratings.
One idea was to use pagination, and query only the Movies that are display in the current page of the list I had in the UI. That would offload the problem of the slow query to the user, every time the user would be filtering/re-sorting the movies the application would have to send a new request to the server, fetch the records from the DB and display them to the user. This would have a noticeable delay for the user, so it was not ideal.
My second thought was to use a Materialied View. Materialized View is a pre-computed query that is stored for later use with the goal to not re-peat the query again, the drawback of-course is that the data that you are getting may not be fresh enough, but in my case, the exact user-rating was not so critical and didn’t had to be 100% accurate. Ofcourse you probably do not want to do the same when you show to the user something that has to be 100% accurate e.g. the balance in his bank account.
An example of the query can be found here
So first I created a migration to create the materialized view. It was looking something like this:
# frozen_string_literal: true Sequel.migration do up do execute <<-SQL CREATE MATERIALIZED VIEW movie_ratings_aggregations AS SELECT movies.id as movie_id, avg(user_ratings.rating)::float as avg_rating, count(user_ratings.rating) as ratings_count, now() AS last_refresh FROM user_ratings JOIN movies ON user_ratings.movie_id = movies.id GROUP BY movies.id ORDER BY movies.id; SQL end down do execute <<-SQL DROP MATERIALIZED VIEW movie_ratings_aggregations; SQL end end
Next thing was to create a model to make the interaction with the view easier.
# frozen_string_literal: true class MovieRatingsAggregation < Sequel::Model(:movie_ratings_aggregations) def readonly? true end def self.refresh_view Sequel::Model.db.refresh_view(:movie_ratings_aggregations) end end
Important is to create a
refresh method so we have the ability to refresh the view
whenever we want, either by a cronjob or with callbacks after something changes in
our main table.
Then we can create a one-to-one relationship with our main model
# frozen_string_literal: true class Movie < Sequel::Model include ActiveModel::Serialization one_to_one :movie_ratings_aggregation, key: :movie_id ... ...
We can refresh the view when new ratings are assigned, although in that case we have to be careful to not over-do it.
# frozen_string_literal: true class MovieRating < Sequel::Model many_to_one :movies def after_save MovieRatingsAggregation.refresh_view end def after_destroy MovieRatingsAggregation.refresh_view end ... ... end
That’s it, combined with eager loading as well can boost significanlty the performance of the application.