# Inner SELECT-s in Rails

**URL:** <https://www.ruby-forum.com/t/inner-select-s-in-rails/98723>\
**Category:** Rails\
**Created:** [July 17, 2006, 7:55pm UTC](https://www.ruby-forum.com/t/inner-select-s-in-rails/98723 "2006-07-17T19:55:02Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![roland](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/3ab097/32.png) [@roland](https://www.ruby-forum.com/u/roland)\
**Post date:** [July 17, 2006, 7:55pm UTC](https://www.ruby-forum.com/t/inner-select-s-in-rails/98723/1 "2006-07-17T19:55:02Z")

</div>

Is there a proper way of doing inner selects in rails.

Basically, I run in issues where I need the count of a query and end up  
using find\_by\_sql. I was wondering if there was any elegant way of doing  
such queries.

Roland

---

<div class="post-metadata">

**Author:** ![roland](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/3ab097/32.png) [@roland](https://www.ruby-forum.com/u/roland)\
**Post date:** [July 17, 2006, 8:36pm UTC](https://www.ruby-forum.com/t/inner-select-s-in-rails/98723/2 "2006-07-17T20:36:34Z")

</div>

On Monday, July 17, 2006, at 7:55 PM, Roland M. wrote:

> * * *
> 
> Rails mailing list  
> removed\_email\_address@domain.invalid  
> [http://lists.rubyonrails.org/mailman/listinfo/rails](http://lists.rubyonrails.org/mailman/listinfo/rails)

Can you post an example of the kind of query you want to do?

\_Kevin  
[www.sciwerks.com](http://www.sciwerks.com)

---

<div class="post-metadata">

**Author:** ![roland](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/3ab097/32.png) [@roland](https://www.ruby-forum.com/u/roland)\
**Post date:** [July 17, 2006, 9:13pm UTC](https://www.ruby-forum.com/t/inner-select-s-in-rails/98723/3 "2006-07-17T21:13:25Z")

</div>

here you go:

select count(\*)  
from (select distinct field\_id from my\_table  
left join my\_other\_table on my\_other\_table.forein\_key\_id =  
my\_table.id  
where field\_id \< 5) as a  
left join some\_other\_table on a.other\_id = some\_other\_table.id  
where some\_other\_table.created\_at \< getdate()

---

<div class="post-metadata">

**Author:** ![roland](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/3ab097/32.png) [@roland](https://www.ruby-forum.com/u/roland)\
**Post date:** [June 3, 2009, 9:38pm UTC](https://www.ruby-forum.com/t/inner-select-s-in-rails/98723/4 "2009-06-03T21:38:01Z")

</div>

Roland M. wrote:

> here you go:
> 
> select count(\*)  
> from (select distinct field\_id from my\_table  
> left join my\_other\_table on my\_other\_table.forein\_key\_id =  
> my\_table.id  
> where field\_id \< 5) as a  
> left join some\_other\_table on a.other\_id = some\_other\_table.id  
> where some\_other\_table.created\_at \< getdate()

Hi, yahoo search brought me to this old topic, but I am running into the  
same problem. So is find\_by\_sql the only way to perform joins on  
inner-selects?

---

<div class="post-metadata">

**Author:** ![roland](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/3ab097/32.png) [@roland](https://www.ruby-forum.com/u/roland)\
**Post date:** [June 5, 2009, 11:28am UTC](https://www.ruby-forum.com/t/inner-select-s-in-rails/98723/5 "2009-06-05T11:28:48Z")

</div>

Depends on how you layout your models. Things like “left join  
my\_other\_table on my\_other\_table.forein\_key\_id = my\_table.id” are  
taken care of by AR if you set up the associations properly. You  
could then do a Model.count(:joins=\>…, :conditions=\>…)

On Jun 4, 12:38 am, Fernando P. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)