thr3ads.net - Rails - Finding and Ordering by associations [Apr 2007]

If this information is useful, please help other people find it:
Share via:

El Gato

2007-Apr-01 03:44 UTC

Finding and Ordering by associations

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.

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Conrad Taylor

2007-Apr-01 04:24 UTC

head link

Re: Finding and Ordering by associations

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

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

Good luck,

-Conrad

On 3/31/07, El Gato
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:>
> 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.
>
> --
> Posted via http://www.ruby-forum.com/.
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

femto gary

2007-Apr-01 05:41 UTC

head link

Re: Finding and Ordering by associations

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

On 4/1/07, Conrad Taylor
<conradwt-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:>
> Hi, you might be able to use the following plugin:
>
> http://crookedhideout.com/blog_entry/show/8
>
> Good luck,
>
> -Conrad
>
> On 3/31/07, El Gato
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:
> >
> > 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.
> >
> > --
> > Posted via http://www.ruby-forum.com/.
> >
> > >
> >
>
> >
>

-- 
Best Regards
XRuby http://xruby.com
femto http://hi.baidu.com/femto

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

El Gato

2007-Apr-01 11:48 UTC

head link

Re: Finding and Ordering by associations

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.

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

swanand deodhar

2007-Apr-01 14:11 UTC

head link

Re: Finding and Ordering by associations

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

On 4/1/07, El Gato
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:>
>
> 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.
>
> --
> Posted via http://www.ruby-forum.com/.
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

El Gato

2007-Apr-01 14:36 UTC

head link

Re: Finding and Ordering by associations

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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Rails - Apr 2007 - Finding and Ordering by associations

Finding and Ordering by associations

Re: Finding and Ordering by associations

Re: Finding and Ordering by associations

Re: Finding and Ordering by associations

Re: Finding and Ordering by associations

Re: Finding and Ordering by associations