CODEWITHSUNDEEP
ruby-on-railsactiverecord
Nick Vanderbilt
Nick Vanderbilt

Reputation: 38420

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

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?

Upvotes: 4

Views: 591

Answers (3)

1myb
1myb

Reputation: 3596

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~

Upvotes: 0

EmFi
EmFi

Reputation: 23450

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'

Upvotes: 5

Tony
Tony

Reputation: 19151

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

Upvotes: 0

Related Questions