yea! the following worked when I switched the logic to look for
postings first... might not be the most efficient as marnen's
suggestion, but for now I think it's good enough
class Stream< ActiveRecord::Base
def self.popular
grouped_postings = Posting.find_by_sql(["select stream_id from
postings where created_at > ? group by stream_id order by count
(stream_id) desc", 1.day.ago])
grouped_postings .collect { |p| Stream.find_by_id(p.stream_id) }
end
end
actually, of anybody *can* suggest how to avoid the collect and get it
all into the find_by_sql in a way that works for mysql, that'd be
great
On Oct 30, 3:19 pm, lunaclaire <[email protected]> wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
