8

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.

Mateusz Urbański
  • 7,352
  • 15
  • 68
  • 133
  • `average` is a query method (well a `CalculationMethod` to be exact). This requires a database call to use SQL average function. To avoid this you could do `map {|x| x.owned_ratings.inject(0) { |sum, n| sum + n.score } / x.owned_ratings.size }` – engineersmnky Mar 20 '18 at 17:49
  • @engineersmnky: ah, but why isn't it smart enough to use already available data? This expectation is not entirely unreasonable. – Sergio Tulentsev Mar 20 '18 at 17:50
  • @SergioTulentsev I don't understand the question? The "already available data" is not a thing. rails won't even execute the query until you act on `owned_ratings` and the action being taken is the `average` method which results in a query. Essentially the query to act on the list would have to be `SELECT user_id, AVG(ratings.score) as alias FROM ratings where ratings.user_id IN (list)` and then this would have to some how be re associated back into the `OwnedRating` object. – engineersmnky Mar 20 '18 at 17:56
  • @engineersmnky: I'm just rephrasing what I think it is OP means :) – Sergio Tulentsev Mar 20 '18 at 17:59
  • I guess you could do `User.joins(:owned_ratings).select(*User.column_names).select("AVG(ratings.score) as average_rating_score").group(*User.column_names)` which theoretically could work and will add a virtual attribute of `average_rating_score` to the `User` objects – engineersmnky Mar 20 '18 at 18:00

2 Answers2

5

Why includes is 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).

Sergio Tulentsev
  • 226,338
  • 43
  • 373
  • 367
  • 1
    Another key point is that the result of performing an aggregate method in Ruby might actually give different results if there is a lot of write activity in the DB since the fetched data may be stale. – max Mar 20 '18 at 18:06
2

Because average actually requires sql query? Try something like:

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

Method to calc average in array got from this SO answer. Efficiency depends on how much owned_ratings records there are.

Oh Sergio Tulentsev is right and its better to do on db side

Martin
  • 4,042
  • 2
  • 19
  • 29