Menu
I have the following script:
User.includes(:owned_ratings).map{|x| x.owned_ratings.average(:score)}
calling x.owned_ratings.average(:score) causes n+1 queries:
(0.2ms) SELECT AVG("ratings"."score") FROM "ratings" INNER JOIN "video_chats" ON "ratings"."video_chat_id" = "video_chats"."id" WHERE "video_chats"."user_id" = $1 [["user_id", 4]]
(0.1ms) SELECT AVG("ratings"."score") FROM "ratings" INNER JOIN "video_chats" ON "ratings"."video_chat_id" = "video_chats"."id" WHERE "video_chats"."user_id" = $1 [["user_id", 1]]
(0.1ms) SELECT AVG("ratings"."score") FROM "ratings" INNER JOIN "video_chats" ON "ratings"."video_chat_id" = "video_chats"."id" WHERE "video_chats"."user_id" = $1 [["user_id", 5]]
(0.1ms) SELECT AVG("ratings"."score") FROM "ratings" INNER JOIN "video_chats" ON "ratings"."video_chat_id" = "video_chats"."id" WHERE "video_chats"."user_id" = $1 [["user_id", 7]]
(0.1ms) SELECT AVG("ratings"."score") FROM "ratings" INNER JOIN "video_chats" ON "ratings"."video_chat_id" = "video_chats"."id" WHERE "video_chats"."user_id" = $1 [["user_id", 3]]
Why includes is not working with aggregate methods? Is there any way to fix that? I know that I can implement average method on my own and omit the problem but I want to be sure that there is not better solution for that.
688
You can avoid most n+1 queries in rails by simply eager loading associations. Eager loading allows you to load all of your associations (parent and children) once instead of n+1 times (which often happens with lazy loading, rails' default). As seen above, . includes allows nested association eager loading!
The n+1 query problem is one of the most common scalability bottlenecks. It involves fetching a list of resources from a database that includes other associated resources within them. This means that we might have to query for the associated resources separately.
What is the N+1 query problem? The N+1 query problem is one of the common performance antipatterns in ORMs. It happens when a query is executed on each result of the previous query, in other words, when an application gets data from the database and then loop through the result of that data.
How lazy loading works: Whenever you try to get some data from database, For example, users is the database table that you have. And you are querying database to get users having age less than 20.
Why
includesis not working with aggregate methods?
Because it would not make sense to reimplement aggregate methods in ruby, when the database server can do the work so much faster.
In fact, if this is what you need to do, it would probably be better to prepare and execute a raw SQL query, so that entire iteration is done in the database, therefore avoiding roundtrips and N+1 (not to mention loading everything).
168
This is pretty trivial to accomplish with SQL:
@users = User.select(
'users.*, AVG(ratings.score) AS users.average_score'
).group_by('ratings.user_id').joins(:ratings)
@users.map(&:average_score)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
