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 
Any help would be greatly appreciated 
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: