Rails ActiveRecord has more attributes than occurrences

Question

I have an author_comments table with an author_name field, a comment, and a brand identifier.

I would like to get the number (number) of entries where the author has more than N (2) entries for this brand.

For instance,

author_comments

author_name comment brand joel "loves donuts" 1 joel "loves cookies" 1 joel "loves oranges" 1 fred "likes bananas" 2 fred "likes tacos" 2 fred "likes chips" 2 joe "thinks this is cool" 1 sally "goes to school" 1 sally "is smart" 1 sally "plays soccer" 1 

In this case, my request should return 2 for brands 1 and 1 for brand 2.

I'm interested in the best option here, without getting all the entries from db and sorting them into ruby, I can do this. I am looking for a better way using active record constructors or sql.

Update: Here is the SQL:

 SELECT author_name, COUNT(*) AS author_comments FROM fan_comments WHERE brand_id =269998788 GROUP BY author_name HAVING author_comments > 2; 

Should I just find find_by_sql?

Answer 1

You can define the same query using active record constructs:

 FanComments.all( :select => 'author_name, count(*) as author_comments', :group => 'author_name', :having => 'author_comments > 2') # in rails 2 

or

 FanComments. select('author_name, count(*) as author_comments'). group('author_name'). having('author_comments > 2') # in rails 3 

Answer 2

 FanComment.group(:author_name).count