# Finding and Ordering by associations

**URL:** <https://www.ruby-forum.com/t/finding-and-ordering-by-associations/90771>\
**Category:** Rails\
**Created:** [April 1, 2007, 5:44am UTC](https://www.ruby-forum.com/t/finding-and-ordering-by-associations/90771 "2007-04-01T05:44:37Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![el-gato](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/e0b2c6/32.png) [@el-gato](https://www.ruby-forum.com/u/el-gato)\
**Post date:** [April 1, 2007, 5:44am UTC](https://www.ruby-forum.com/t/finding-and-ordering-by-associations/90771/1 "2007-04-01T05:44:37Z")

</div>

I’m stumped on this one and haven’t found anything to help me yet. Any  
input is welcomed. I’m sure this isn’t too hard for a Rails/SQL wizard.  
Basically, I want to order a listing based on the number of hits in the  
last x amount of time.

I have a model with a has\_many association.

class Item \< ActiveRecord::Base  
has\_many :hits  
end

class Hit \< ActiveRecord::Base  
belongs\_to :item  
end

and I want to do a find that essentially goes like this:

Item.find :all, :order =\> “…the number of hits found for the item over  
the last x amount of time”

I don’t really want a where clause, because I want all items… I just  
want them weighted by the number of hits in that amount of time.

Thanks in advance.

---

<div class="post-metadata">

**Author:** ![el-gato](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/e0b2c6/32.png) [@el-gato](https://www.ruby-forum.com/u/el-gato)\
**Post date:** [April 1, 2007, 6:24am UTC](https://www.ruby-forum.com/t/finding-and-ordering-by-associations/90771/2 "2007-04-01T06:24:37Z")

</div>

Hi, you might be able to use the following plugin:

[http://crookedhideout.com/blog\_entry/show/8](http://crookedhideout.com/blog_entry/show/8)

Good luck,

-Conrad

---

<div class="post-metadata">

**Author:** ![el-gato](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/e0b2c6/32.png) [@el-gato](https://www.ruby-forum.com/u/el-gato)\
**Post date:** [April 1, 2007, 7:42am UTC](https://www.ruby-forum.com/t/finding-and-ordering-by-associations/90771/3 "2007-04-01T07:42:41Z")

</div>

the only problem is, I don’t see why  
you need a seperate Hits entity.

On 4/1/07, Conrad T. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> > want them weighted by the number of hits in that amount of time.

–  
Best Regards  
XRuby [http://xruby.com](http://xruby.com)  
femto [如流，新一代智能工作平台](http://hi.baidu.com/femto)

---

<div class="post-metadata">

**Author:** ![el-gato](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/e0b2c6/32.png) [@el-gato](https://www.ruby-forum.com/u/el-gato)\
**Post date:** [April 1, 2007, 1:48pm UTC](https://www.ruby-forum.com/t/finding-and-ordering-by-associations/90771/4 "2007-04-01T13:48:45Z")

</div>

femto gary wrote:

> the only problem is, I don’t see why  
> you need a seperate Hits entity.

I’m not really sure I can imagine another way. Each item has an amount  
of information and a link. I’d like to track unique hits on that link.  
However, I don’t think that Conrad’s clicktrack will quite work for me  
(as you pointed out, it seems to be more of a where clause than an order  
clause). And to be quite honest, this is one of those problems that’s  
just plain bugging me now and I’d love to know what a good solution is.

---

<div class="post-metadata">

**Author:** ![el-gato](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/e0b2c6/32.png) [@el-gato](https://www.ruby-forum.com/u/el-gato)\
**Post date:** [April 1, 2007, 4:36pm UTC](https://www.ruby-forum.com/t/finding-and-ordering-by-associations/90771/5 "2007-04-01T16:36:19Z")

</div>

I managed to get some working SQL (with help). Of course, now I’d love  
it if I didn’t have to use find\_by\_sql, but again… it’s a little more  
complicated than my skill level. Is it possible to use the standard  
find() options with this SQL or would I just be best off leaving this  
alone?

def self.find\_by\_activity(time = 24.hours)  
find\_by\_sql([“select a.\* from items a left join (  
select distinct (t1.item\_id), count(t1.item\_id)  
at\_intervals, unix\_timestamp(t1.created\_at) from `hits`  
t1  
where unix\_timestamp(now()) -  
unix\_timestamp(t1.created\_at) \< ?  
group by t1.item\_id) b on a.id = b.item\_id  
order by at\_intervals desc, a.id;”, time])  
end

---

<div class="post-metadata">

**Author:** ![el-gato](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/e0b2c6/32.png) [@el-gato](https://www.ruby-forum.com/u/el-gato)\
**Post date:** [April 1, 2007, 4:12pm UTC](https://www.ruby-forum.com/t/finding-and-ordering-by-associations/90771/6 "2007-04-01T16:12:24Z")

</div>

Hi!  
You migh want to use aggregate function say count.  
Regards,  
Swanand