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

**URL:** <https://www.ruby-forum.com/t/i-know-this-is-a-join-but-not-sure-how-to-do-it/178211>\
**Category:** Rails\
**Created:** [October 30, 2009, 9:24pm UTC](https://www.ruby-forum.com/t/i-know-this-is-a-join-but-not-sure-how-to-do-it/178211 "2009-10-30T21:24:28Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![lunaclaire](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/f4b2a3/32.png) [@lunaclaire](https://www.ruby-forum.com/u/lunaclaire)\
**Post date:** [October 30, 2009, 9:24pm UTC](https://www.ruby-forum.com/t/i-know-this-is-a-join-but-not-sure-how-to-do-it/178211/1 "2009-10-30T21:24:28Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![lunaclaire](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/f4b2a3/32.png) [@lunaclaire](https://www.ruby-forum.com/u/lunaclaire)\
**Post date:** [October 30, 2009, 9:53pm UTC](https://www.ruby-forum.com/t/i-know-this-is-a-join-but-not-sure-how-to-do-it/178211/2 "2009-10-30T21:53:41Z")

</div>

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](http://www.marnen.org)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![lunaclaire](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/f4b2a3/32.png) [@lunaclaire](https://www.ruby-forum.com/u/lunaclaire)\
**Post date:** [October 30, 2009, 11:20pm UTC](https://www.ruby-forum.com/t/i-know-this-is-a-join-but-not-sure-how-to-do-it/178211/3 "2009-10-30T23:20:25Z")

</div>

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-

---

<div class="post-metadata">

**Author:** ![lunaclaire](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/f4b2a3/32.png) [@lunaclaire](https://www.ruby-forum.com/u/lunaclaire)\
**Post date:** [October 31, 2009, 12:00am UTC](https://www.ruby-forum.com/t/i-know-this-is-a-join-but-not-sure-how-to-do-it/178211/4 "2009-10-31T00:00:49Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![lunaclaire](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/f4b2a3/32.png) [@lunaclaire](https://www.ruby-forum.com/u/lunaclaire)\
**Post date:** [November 1, 2009, 9:11pm UTC](https://www.ruby-forum.com/t/i-know-this-is-a-join-but-not-sure-how-to-do-it/178211/5 "2009-11-01T21:11:13Z")

</div>

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.