Help with this (somewhat simple) finder query

Rails
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 G. wrote:

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:

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

On Aug 17, 6:58 am, Luke G. [email protected]

4

Fantastic! That’s would indeed be ideal!

Thanks for the heads up!

Rein H. wrote:

It would be better to make that a named_scope