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:
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:
Next thing was to create a model to make the interaction with the view easier.
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
We can refresh the view when new ratings are assigned, although in that case we have to be careful to not over-do it.
That's it, combined with eager loading as well can boost significanlty the performance of the application.