Help with this (somewhat simple) finder query

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:

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:

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

Fantastic! That's would indeed be ideal!

Thanks for the heads up!

Rein Henrichs wrote: