Retrieve from multiple relations, ruby on rails

Question

Hello,

user (has_many :user, has_many :comments) post (belongs_to :user, has_many :comments) comment (belongs_to :user, belongs_to :post)

Now I am trying to retrieve all posts (having atleast one comment of the user) updated with comments from last comment of user in that post (i will be using user's updated_at attribute for this)

Example : Post A

User X,Y

X posts comment in A

then Y posts comment in A

Now I want to retrieve the post A since there has been a new comment posted in A after User X's last comment.

Similarly I want to retrieve all posts which have an comment posted after user's comment. (I have updated updated_at value of user everytime he posts a comment in any post) (I also have updated updated_at value of post, everytime some1 posts a comment in that post)

I have racked my brain on how this can be achieved in RoR, but have`nt got a clue, so finally I turn to help here in StackOverflow :)

Thanks in Advance guys

Answer 1

SQL magic:

class User
  def posts_with_comments_since_my_last_comment_in_that_post
     Post.find_by_sql([%Q| SELECT DISTINCT posts.* from posts INNER JOIN users on 
       (posts.user_id = users.id) INNER JOIN comments on 
       (comments.user_id = users.id AND comments.post_id = posts.id) 
       WHERE (posts.updated_at > comments.updated_at) AND (users.id = ?)|, id])
  end

  def posts_with_comments_since_my_last_comment
    Post.find_by_sql([%Q| SELECT DISTINCT posts.* from posts INNER JOIN users on 
         (posts.user_id = users.id) INNER JOIN comments on 
         (comments.user_id = users.id AND comments.post_id = posts.id) 
         WHERE (posts.updated_at > user.updated_at) AND (users.id = ?)|, id])
  end
end

You can possibly avoid that via creative use of caching, but I wouldn't suggest it. Make sure your tables are indexed properly. This has the potential to go very, very wrong if you run a site with lots of comments.

Answer 2

Patrick has some good advice. But I think he misunderstood your request. As his queries join posts to the user who posts them, not a user who commented on them. He's on the right track, and he's definitely correct about indexing.

The following doesn't work because you're trying to fetch Posts on Comment objects.

@post_ids = Comment.all(:select => "DISTINCT post_id",
  :conditions => ["user_id = ? AND updated_at > ?", 
  @userinfo.id, @userinfo.updated_at.to_date.to_time]) 

@posts = Post.find(@post_ids)

You could map all the comments to post ids. ie: Post.find(@post_ids.map{&:post_id}) But that's inefficient requiring two database trips and instantiating a comment for hit.

Instead you should be using a named scope or something to select Posts based on your criteria.

class Post < ActiveRecord::Base
  ...
  #this is what you asked for.
  named_scope :with_unseen_comments_for_user, lamda do |user|
    {
     :select => "DISTINCT posts.*", :joins => "INNER JOIN comments, 
        comments others_comments, users ON (comments.user_id = users.id AND 
        others_comments.post_id = post.id AND comments.post_id = posts.id", 
      :conditions => ["users.id = ? AND 
        comments_posts.updated_at > users.updated_at", user]
    }
  end

end

#usage:
Post.updated_after_users_last_comment_in_post(@user)

The complex joins statement is needed to create an efficient query because you want the join to work list this:

Other's Comments <--- post_id - id ---> Posts <--- id - post_id ---> User's Comments <--- user_id - id ---> User

Assuming my SQL is what I remember it being, this should retrieve all posts where the given user has commented and his last post was made before Other's comments on the same post.

Regardless, you're going to run into problems when a user is posting in multiple threads. So you may want to rethink your relationships and update methods.

For instance your user will not be notified of new comments on Post A if the following happens:

User X comments on Post A

User Y comments on Post A

User X comments on Post B before noticing that User Y commented on Post A.

You can get around this by adding a last_seen_at field to the comments and update it whenever the owner views the comments. That's fairly trivial with an :after_filter. Infact it makes the named scope much simpler. Because we don't need to drag the user record into the query to compare dates.

  named_scope :unseen_comments, lamda do |user|
    {
     :select => "DISTINCT posts.*", :joins => [:comments, :comments], 
      :conditions => ["comments.user_id = ? AND 
        others_comments.updated_at > comments.last_seen_at", user]
    }
  end

The :joins => [:comments, :comments] is a little odd but it will join comments to posts in the query twice, aliasing the second time as comments_posts. It's not specifically documented in the find or named_scope, but it uses the same syntax as the association join arguments.