Finding and Ordering by associations

Rails
1

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.

2

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

http://crookedhideout.com/blog_entry/show/8

Good luck,

-Conrad

3

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

On 4/1/07, Conrad T. [email protected] wrote:

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

–
Best Regards
XRuby http://xruby.com
femto 如流，新一代智能工作平台

4

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.

5

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

6

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