DEV Community

loading...

Discussion on: Rails N + 2 queries

Collapse
tomurb profile image
Tomek Urban

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 (unless I have missed the point):

Pots.includes(:comments).where(comments: Comment.where(censored: false))
Collapse
brunvez profile image
Bruno Vezoli Author

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.

Collapse
tomurb profile image
Tomek Urban

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)
Thread Thread
brunvez profile image
Bruno Vezoli Author

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.