lunaclaire wrote:
> here are a few models:
>
> Stream
> has_many :postings
> belongs_to :user
>
> Posting
> belongs_to :stream
> belongs_to :user
>
> User
> has_many :streams
> has_many :postings
>
>
> Each Posting has a 'created_at' attribute.
>
> I need to find 'popular' streams based on those that have the most,
> recent postings (within the last 24 hrs).
>
So the SQL would be something like this (untested):
SELECT s.*, p.count(*) as posting_count
FROM postings p LEFT JOIN streams s ON (p.stream_id = s.id)
WHERE -- some appropriate condition for p.created_at
GROUP BY p.stream_id
ORDER BY posting_count DESC
[...]
> Can someone help me with how I'd construct the find() for this?
The SQL above should help, but it doesn't look like AR will do much for
aggregate functions on an associated table. If you want to do this in
one query with AR, perhaps something like the following would work:
Stream.find(:all, :joins => :postings, :select => "streams.*,
postings.count(*) as posting_count", :conditions =>
{'postings.created_at > ?', 1.day.ago}, :group => 'postings.stream_id',
:order => 'posting_count desc')
Best,
--
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
--
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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
