Discussion on: Rails N + 2 queries

View post

Replies for: sometimes it is better to have a small performance penalty rather than a model full of associations I think you can also do something like this (...

Hey, thanks for commenting! The #includes method does not accept filtering AFAIK, the where clause would be applied to the query to fetch the posts but not to the loaded comments. So when I paste that query on the console I get the following:

irb> Post.includes(:comments).where(comments: Comment.where(censored: false))
  Post Load (2.1ms)  SELECT  "posts".* FROM "posts" WHERE "posts"."id" IN (SELECT "comments"."post_id" FROM "comments" WHERE "comments"."censored" = $1) LIMIT $2  [["censored", false], ["LIMIT", 11]]
  Comment Load (0.4ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN ($1, $2, $3, $4)  [["post_id", 2], ["post_id", 3], ["post_id", 4], ["post_id", 5]]

Then if I add a method on the Post model to get uncensored comments:

  def uncensored_comments
    comments.where(censored: false)
  end

And run the same example I did in the post:

irb> posts = Post.all
irb> all_comments = posts.map { |p| p.uncensored_comments }

We go back the N + 2 query problem

  Post Load (2.3ms)  SELECT "posts".* FROM "posts" WHERE "posts"."id" IN (SELECT "comments"."post_id" FROM "comments" WHERE "comments"."censored" = $1)  [["censored", false]]
  Comment Load (0.6ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN ($1, $2, $3, $4)  [["post_id", 2], ["post_id", 3], ["post_id", 4], ["post_id", 5]]
  Comment Load (0.3ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = $1 AND "comments"."censored" = $2  [["post_id", 2], ["censored", false]]
  Comment Load (0.2ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = $1 AND "comments"."censored" = $2  [["post_id", 3], ["censored", false]]
  Comment Load (0.8ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = $1 AND "comments"."censored" = $2  [["post_id", 4], ["censored", false]]
  Comment Load (1.2ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = $1 AND "comments"."censored" = $2  [["post_id", 5], ["censored", false]]

I think the code you suggested would be a nice way of getting posts with comments, but it does not help preload custom queries.

Tomek Urban • Jun 10 '20

Thanks, I didn't think this through. Or at all, with this weird #includes ;)
But still, you can do it without association in the model.

Comment.where(post: Post.all, censored: false)

Bruno Vezoli • Jun 10 '20

Yeah, you can always do the queries straight somewhere else, but that wouldn't help you preload the records. The example was very simple I hope it doesn't miss the point, which is preloading and not doing extra queries and not just being able to get the data. Maybe something like this paints a clearer picture:

posts = Post.all.includes(:comments)
posts.map do |post|
  create_thumbnail(post, post.uncensored_comments)
end

This creates the same N + 2 queries as before since the .includes(:comments) it's actually useless here. Sure you can always find a way around not having associations in the model, one of the ways to do so and that I didn't mention in the post is to do the following:

posts = Post.all
uncensored_comments = Comment.where(post: posts, uncensored: false).group_by(&:post_id)
posts.map do |post|
  create_thumbnail(post, uncensored_comments[post.id])
end

But that, IMO, it's very ugly and does not follow OOP at all.