How to find only the users who have posted at least one comment

Question

I am using Rails 2.3.5 .

This is a standard case. Tables are: users, comments, user_comments . I need to find all the users who have status 'active' and have posted at least one comment.

I know comments table can have foreign key but this is a contrived example. There are two users in the table. There are two comments. Both the comments are posted by the first user.

named_scope :with_atleast_one_comment, lambda { {
      :joins => 'inner join user_comments on users.id = user_comments.user_id ' } }


 named_scope :with_active_status, {:conditions => {:status => 'active'} }    

When I execute

 User.with_atleast_one_comment.with_active_status 

I get two records. Since both the comments are posted by one user I want only one user.

What's the fix?

Answer 1

Specify the :uniq => true option to remove duplicates from the collection. This is most useful in conjunction with the :through option.

Answer 2

The with_at_least_one_comment scope isn't behaving as you expect it to. As it appears in the question, it will select a user for each entry in user_comments. Which results in the duplicate results you're seeing. When compounded with active_users, you will remove any records returned by with_at_least_one_comment that don't have active status.

Let's start by simplifying the problematic named scope first. You don't need the lambda because there are no arguments to take, and the join can be outsourced to Active Record, which performs an inner join if given an association.

In short, this named scope will do exactly what you want.

named_scope :with_at_least_one_comment, :joins => :user_comments, 
  :group => 'users.id'

Answer 3

if i'm is not wrong, there is few way to achieve this...
unless User.comments?
or another way is also specify a new method in your controller and lastly...
the info from Emfi should work have a try for it~

Answer 4

I've written a post about this type of query recently.

http://iamwil.posterous.com/find-all-posts-with-at-least-2-comments