# "Subselect" type functionality in ActiveRecord

**URL:** <https://www.ruby-forum.com/t/subselect-type-functionality-in-activerecord/109178>\
**Category:** Rails\
**Created:** [July 12, 2007, 6:32am UTC](https://www.ruby-forum.com/t/subselect-type-functionality-in-activerecord/109178 "2007-07-12T06:32:18Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![jeannibee](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/85e7bf/32.png) [@jeannibee](https://www.ruby-forum.com/u/jeannibee)\
**Post date:** [July 12, 2007, 6:32am UTC](https://www.ruby-forum.com/t/subselect-type-functionality-in-activerecord/109178/1 "2007-07-12T06:32:18Z")

</div>

Hi

Is there any way (Using my Model clases) to be able to do a sort of  
subselect.

Ex: select a, b, c from table\_a where a not in (SELECT aa from table\_b  
);

I really hope I don’t have to select all the models from each table and  
us ‘each’ on ModelA list and ‘find’ on the ModelB list.

I"m still learning, but as I use this I find more and more… CRUD =  
ActiveRecord, anything else hit the DB directly.

---

<div class="post-metadata">

**Author:** ![jeannibee](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/85e7bf/32.png) [@jeannibee](https://www.ruby-forum.com/u/jeannibee)\
**Post date:** [July 12, 2007, 12:05pm UTC](https://www.ruby-forum.com/t/subselect-type-functionality-in-activerecord/109178/2 "2007-07-12T12:05:51Z")

</div>

Jean N. wrote:

> ActiveRecord, anything else hit the DB directly.  
> Yes, just have :conditions =\> ‘a not in (SELECT aa from table\_b)’

–  
We develop, watch us RoR, in numbers too big to ignore.

---

<div class="post-metadata">

**Author:** ![jeannibee](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/85e7bf/32.png) [@jeannibee](https://www.ruby-forum.com/u/jeannibee)\
**Post date:** [July 18, 2007, 10:41am UTC](https://www.ruby-forum.com/t/subselect-type-functionality-in-activerecord/109178/3 "2007-07-18T10:41:30Z")

</div>

Mark Reginald J. wrote:

> Jean N. wrote:
> 
> > ActiveRecord, anything else hit the DB directly.  
> > Yes, just have :conditions =\> ‘a not in (SELECT aa from table\_b)’
> 
> –  
> We develop, watch us RoR, in numbers too big to ignore.

But is there a way to do this without writing SQL? e.g.

:conditions =\> [‘a not in ?’, Aaaa.find(…).the\_sql]

or some other even cleaner way?

---

<div class="post-metadata">

**Author:** ![jeannibee](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/85e7bf/32.png) [@jeannibee](https://www.ruby-forum.com/u/jeannibee)\
**Post date:** [July 12, 2007, 5:25pm UTC](https://www.ruby-forum.com/t/subselect-type-functionality-in-activerecord/109178/4 "2007-07-12T17:25:44Z")

</div>

Mark Reginald J. wrote:

> Jean N. wrote:
> 
> > ActiveRecord, anything else hit the DB directly.  
> > Yes, just have :conditions =\> ‘a not in (SELECT aa from table\_b)’
> 
> –  
> We develop, watch us RoR, in numbers too big to ignore.

Well that seems easy enough. 🙂

Thank you…

I also discovered find\_by\_sql() last night too and that can also do the  
job, just with less ‘Rails’ and more ‘Sql dependant’ code.

---

<div class="post-metadata">

**Author:** ![jeannibee](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/85e7bf/32.png) [@jeannibee](https://www.ruby-forum.com/u/jeannibee)\
**Post date:** [July 18, 2007, 4:59pm UTC](https://www.ruby-forum.com/t/subselect-type-functionality-in-activerecord/109178/5 "2007-07-18T16:59:16Z")

</div>

A more “rails” olution might be to do something like this:

array\_of\_ids\_of\_users\_to\_skip =  
User.find\_all\_by\_status(“disabled”).collect{|u|  
u.id}  
@profiles = Profile.find :all, :include=\>[:users],  
:conditions=\>[“users.idnot in(?)”, array\_of\_ids\_of\_users\_to\_skip]

This would result in two database hits, but it may perform better.

I would caution that subselects in the WHERE clause can be bad unless  
your  
database is smart enough to handle them. Some databases would execute  
that  
subselect once per every row in the table. (Where clauses are usually  
run  
for every row)

Again, this all depends on your database. You have a development.log  
file  
that shows you all the sql statements that the app runs. Take those and  
run  
them through your database’s query analyzer / explain plan.

This code is typed on the fly, not tested. You should be able to grasp  
the  
basic idea though. Let me know how it all goes.