I need to find 'popular' streams based on those that have the most,
recent postings (within the last 24 hrs).
The user parts of the above are not really relevant, 'cause I need to
find the streams across the entire DB, not just for a user (an earlier
req, that may come back).
Can someone help me with how I'd construct the find() for this?
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
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
Stream.find_by_sql(
["select streams.* from streams join
(select stream_id, count(*) from postings
where created_at > :one_day_ago
group by stream_id
order by count(*) desc) subq
on streams.id = subq.stream_id ",
{:one_day_ago=>1.day.ago}]
)
Bad thing is you can't programmatically do much with it in terms of
adding limit statements, etc, but if it solves the problem, use it.