I know this is a join, but not sure how to do it

Rails
1

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).

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?

2

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]

3

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-

4

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

5

If you can use find_by_sql try this:

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.