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.
on 2007-04-01 07:44
on 2007-04-01 08:24
Hi, you might be able to use the following plugin: http://crookedhideout.com/blog_entry/show/8 Good luck, -Conrad
on 2007-04-01 09:42
the only problem is, I don't see why you need a seperate Hits entity. On 4/1/07, Conrad T. <firstname.lastname@example.org> wrote: > > > > > > want them weighted by the number of hits in that amount of time. > -- Best Regards XRuby http://xruby.com femto http://hi.baidu.com/femto
on 2007-04-01 15:48
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.
on 2007-04-01 18:12
Hi! You migh want to use aggregate function say count. Regards, Swanand
on 2007-04-01 18:36
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