Thx, marnen (again)
getting a MySQL error near 'p.count(*) as posting_count '... something
it doesnt like there
I'll try to figure that out, but if you see it, please let me know
I'm also wondering if I could do a query for all the postings in the
last 24 hrs and somehow sum them by their stream_id's since each
posting can only be part of 1 stream
On Oct 30, 1:53 pm, Marnen Laibow-Koser <rails-mailing-l...@andreas-
s.net> wrote:
> 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-Koserhttp://www.marnen.org
> [email protected]
> --
> Posted viahttp://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
-~----------~----~----~----~------~----~------~--~---
