Nested ActiveRecord relationships in Rails

Question

I have a number of models

class User < ActiveRecord::Base
  belongs_to :group
end

class Group < ActiveRecord::Base
  has_many :users
  has_many :posts
end

class Post < ActiveRecord::Base
  belongs_to :group
  has_many :comments
end

class Comment < ActiveRecord::Base
  belongs_to :post
end

To get a user's post I can do User.find(1).group.posts.all, but if I want all comments for a user's posts I can't do User.find(1).group.posts.all.comments.all.

Is there an easy solution to get all comments for a user's posts other than looping through all the posts?

Based on a comment by Sævar on my answer below I think it would be a good idea to point out that User.find(1).group.posts retrieves all posts in the user's group, not just ones made by that user. Is that what you want? — Coenwulf, Feb 26 '14 at 22:41

score 1 · Answer 1 · answered Feb 26 '14 at 22:25

1

What you want is the has_many through relationship: http://guides.rubyonrails.org/association_basics.html#the-has-many-through-association

Add the following to your Group class

has_many :comments, through: :posts

Then you can get the comments with:

User.find(1).group.comments

answered Feb 26 '14 at 22:25

Coenwulf

1,937
2
15
23

This gives you all comments for the user's group, not the comments for the posts belonging to the specified user. – Sævar Feb 26 '14 at 22:34
Good point, but based on the way the OP got posts (User.find(1).group.posts) I assume this is the desired behavior. If not the database structure needs to be changed. – Coenwulf Feb 26 '14 at 22:38
Well, the OP says: "get all comments for a user's posts", so that's what I assumed. The database structure is fine, the trick is to come up with an efficient query. – Sævar Feb 26 '14 at 22:42
The database structure is fine as long as the desired relationship is between Post and Group not Post and User. If you need to know what User the Post (and/or its comments) belong to then the database will need user_id field in the posts table (for posts) or if only comments need are needed then in comments table. That would both change the DB structure and how you would get the info. – Coenwulf Feb 26 '14 at 23:01

score 0 · Accepted Answer · answered Feb 26 '14 at 23:03

Single query solution:

Comment.joins(post: {group: :users}).where(users: {id: 1})

Resulting query:

SELECT "comments".* FROM "comments"
  INNER JOIN "posts" ON "posts"."id" = "comments"."post_id"
  INNER JOIN "groups" ON "groups"."id" = "posts"."group_id"
  INNER JOIN "users" ON "users"."group_id" = "groups"."id"
  WHERE "users"."id" = 1

score -1 · Answer 3 · answered Feb 26 '14 at 22:27

-1

Simply adding a has_many :comments, through: :posts is not enough. Then, a query like User.find(1).group.comments would give you all comments for the user's group, not the specified user.

Another way is to look up all post ID's for a given user and use that to find comments for that user's posts:

post_ids = User.find(1).group.posts.map(&:id).uniq
user_comments = Comment.where(:post => post_ids)

answered Feb 26 '14 at 22:27

Sævar

1,602
11
18

That will fire a query for every post, which is very inefficient. It's called a N+1 query, see http://stackoverflow.com/questions/97197/what-is-the-n1-selects-issue – Patrick Oscity Feb 26 '14 at 22:28
1

No, that actually results in 3 sql queries. One to select the user. Then on like this to select the posts based on the user group: select posts.* from posts where posts.group_id = 1. The third one selects the comments using a query like: SELECT comments.* from comments where comments.post_id IN [1,2,3,4,5] – Sævar Feb 26 '14 at 22:40
You are right, sorry. However, this will still perform badly because you have to read the values from the database to Ruby, just to put them back in again for selecting the comments instead of doing a JOIN. – Patrick Oscity Feb 26 '14 at 22:42
Since there is no direct relationship between a user and a comment the comments of the user's group are semantically equivalent to the user's comments. – Patrick Oscity Feb 26 '14 at 23:11
Also there are 4 not 3 queries (one for every model), and I think it should be `where(:post_id => post_ids)` instead of `where(:post => post_ids)` – Patrick Oscity Feb 26 '14 at 23:26

Nested ActiveRecord relationships in Rails

3 Answers3