# Help with this (somewhat simple) finder query

**URL:** <https://www.ruby-forum.com/t/help-with-this-somewhat-simple-finder-query/146072>\
**Category:** Rails\
**Created:** [August 17, 2008, 12:39pm UTC](https://www.ruby-forum.com/t/help-with-this-somewhat-simple-finder-query/146072 "2008-08-17T12:39:13Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![galeal](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/8c91f0/32.png) [@galeal](https://www.ruby-forum.com/u/galeal)\
**Post date:** [August 17, 2008, 12:39pm UTC](https://www.ruby-forum.com/t/help-with-this-somewhat-simple-finder-query/146072/1 "2008-08-17T12:39:13Z")

</div>

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 😃

---

<div class="post-metadata">

**Author:** ![galeal](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/8c91f0/32.png) [@galeal](https://www.ruby-forum.com/u/galeal)\
**Post date:** [August 17, 2008, 12:58pm UTC](https://www.ruby-forum.com/t/help-with-this-somewhat-simple-finder-query/146072/2 "2008-08-17T12:58:08Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![galeal](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/8c91f0/32.png) [@galeal](https://www.ruby-forum.com/u/galeal)\
**Post date:** [August 17, 2008, 6:50pm UTC](https://www.ruby-forum.com/t/help-with-this-somewhat-simple-finder-query/146072/3 "2008-08-17T18:50:16Z")

</div>

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. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![galeal](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/8c91f0/32.png) [@galeal](https://www.ruby-forum.com/u/galeal)\
**Post date:** [August 17, 2008, 11:21pm UTC](https://www.ruby-forum.com/t/help-with-this-somewhat-simple-finder-query/146072/4 "2008-08-17T23:21:49Z")

</div>

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