ruby-on-rails - Ruby on Rails 平均方法导致 n+1 次查询

Question

我有以下脚本:

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 不能使用聚合方法？有什么办法可以解决这个问题吗？我知道我可以自己实现平均方法并忽略问题，但我想确保没有更好的解决方案。

Answer 1

Why includes is not working with aggregate methods?

因为当数据库服务器可以更快地完成工作时，在 ruby​​ 中重新实现聚合方法没有意义。

事实上，如果这是您需要做的，准备和执行原始 SQL 查询可能会更好，这样整个迭代都在数据库中完成，从而避免往返和 N+1(更不用说加载一切)。