Ruby on Rails的average方法导致n+1个查询

kgsdhlau  于 2023-04-20  发布在  Ruby
关注(0)|答案(2)|浏览(96)

我有以下脚本:

User.includes(:owned_ratings).map{|x| x.owned_ratings.average(:score)}

调用x.owned_ratings.average(:score)会导致n+1个查询:

(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]]

为什么includes不能使用聚合方法?有什么方法可以解决这个问题吗?我知道我可以自己实现average方法并忽略这个问题,但我想确定没有更好的解决方案。

9rnv2umw

9rnv2umw1#

为什么includes不能使用聚合方法?
因为在ruby中重新实现聚合方法是没有意义的,因为数据库服务器可以更快地完成工作。
事实上,如果这是您需要做的,那么最好准备并执行一个原始SQL查询,以便在数据库中完成整个迭代,从而避免往返和N+1(更不用说加载所有内容)。

xkftehaa

xkftehaa2#

因为average实际上需要sql查询?请尝试以下操作:

User.includes(:owned_ratings).map{ |x| x.owned_ratings.map(&:score).instance_eval { reduce(:+) / size.to_f } }

方法来计算从SO answer得到的数组中的平均值。效率取决于有多少owned_ratings记录。
哦,塞尔吉奥·图伦采夫是对的,最好在数据库方面做

相关问题