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?
Marnen Laibow-Koser
2009-Oct-30 20:53 UTC
Re: I know this is a join, but not sure how to do it...
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 marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/.
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 > mar...-sbuyVjPbboAdnm+yROfE0A@public.gmane.org > -- > Posted viahttp://www.ruby-forum.com/.
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
<szager...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
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
> > mar...-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
> > --
> > Posted viahttp://www.ruby-forum.com/.
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.