Help with this (somewhat simple) finder query

rubyonrails-talk
1

I have this section in my site that I want to build, "Hot announcements" i.e. the latest 4 announcements that have lots of comments, ordered in a descending fashion.

What I want to do is only get the top 4 from the database, and in the necessary order. Normally, I would've just done it in ruby since its easier, but I decided to try and do it properly this time :slight_smile:

Any help would be greatly appreciated :smiley:

2

Answering my own question:     @hot_announcements = Announcement.find(:all,       :select => 'announcements.*, COUNT(comments.id) comment_count',       :joins => 'LEFT OUTER JOIN comments ON announcements.id = comments.announcement_id',       :conditions => 'announcements.created_at > (NOW() - INTERVAL 14 DAY)',       :group => 'announcements.id',       :order => 'comment_count DESC',       :limit => 4     )

Luke Grimstrup wrote:

3

It would be better to make that a named_scope:

named_scope :hot,   :select => 'accouncements.*, COUNT(comments.id) comment_count',   :conditions => lambda { "announcements.created_at > #{2.weeks.ago}",   :joins => :comments,   :order => 'comment_count DESC',   :limit => 4

then in your controller:

@hot_announcements = Announcement.hot

It's much cleaner in the controller, easier to test and more flexible. You could later, for instance, get a specific user's hot announcements with:

@user.announcements.hot

4

Fantastic! That's would indeed be ideal!

Thanks for the heads up!

Rein Henrichs wrote: