Forum: Ruby on Rails Finding and Ordering by associations

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
El G. (Guest)
on 2007-04-01 07:44
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.
Conrad T. (Guest)
on 2007-04-01 08:24
(Received via mailing list)
Hi, you might be able to use the following plugin:

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

Good luck,

-Conrad
femto gary (Guest)
on 2007-04-01 09:42
(Received via mailing list)
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> 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
El G. (Guest)
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.
Swanand D. (Guest)
on 2007-04-01 18:12
(Received via mailing list)
Hi!
  You migh want to use aggregate function say count.
Regards,
Swanand
El G. (Guest)
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
This topic is locked and can not be replied to.