# Sql "in" query using active record

**URL:** <https://www.ruby-forum.com/t/sql-in-query-using-active-record/175268>\
**Category:** Rails\
**Created:** [September 10, 2009, 12:08pm UTC](https://www.ruby-forum.com/t/sql-in-query-using-active-record/175268 "2009-09-10T12:08:58Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![AppleII717](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/59ef9b/32.png) [@AppleII717](https://www.ruby-forum.com/u/AppleII717)\
**Post date:** [September 10, 2009, 12:08pm UTC](https://www.ruby-forum.com/t/sql-in-query-using-active-record/175268/1 "2009-09-10T12:08:58Z")

</div>

I’m trying to convert a non-sql application (4D or 4th Dimension) to  
rails. Got all the CRUD stuff down, but I’m running into problem in  
generating reports. 4D makes heavy use of Sets and Selections, which  
are really just an array of record numbers that can be used to quickly  
restore a query. Those arrays make aggregate reporting fairly easy. I  
can get there using an sql query. I’m just looking for the best  
approach using rails.

Lets say I have a simple has\_many, belong\_to relation:

Project:ARB (project fields :id, :name, "status, …)  
has\_many :tasks  
Task:ARB (task fields :id, :project\_id, :name, :date\_due, …)  
belong\_to :project

I need to go both ways  
give me a list of tasks where the project status is active, or:

```
select * from tasks where project_id in (select id from projects

```

where status = ‘Active’)

give me a list of projects where the task date\_due is \< some\_date

```
select * from projects where id in (select project_id from tasks

```

where date\_due \< ‘2009-09-09’

How do I do that in an ActiveRecord find?

Steve

---

<div class="post-metadata">

**Author:** ![AppleII717](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/59ef9b/32.png) [@AppleII717](https://www.ruby-forum.com/u/AppleII717)\
**Post date:** [September 10, 2009, 1:33pm UTC](https://www.ruby-forum.com/t/sql-in-query-using-active-record/175268/2 "2009-09-10T13:33:16Z")

</div>

On Sep 10, 11:05 am, AppleII717 [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Project:ARB (project fields :id, :name, "status, …)  
> give me a list of projects where the task date\_due is \< some\_date
> 
> ```
> select * from projects where id in (select project_id from tasks
> 
> ```
> 
> where date\_due \< ‘2009-09-09’
> 
> How do I do that in an ActiveRecord find?

At a very basic level, the conditions option is just a blob of sql -  
you can stick anything you want in there.

I personally would do the above as Project.find :all, :joins  
=\> :task, :conditions =\> [“due\_date \< ?”,…]

which isn’t the same sql but has (I believe) the same result

Fred

---

<div class="post-metadata">

**Author:** ![AppleII717](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/59ef9b/32.png) [@AppleII717](https://www.ruby-forum.com/u/AppleII717)\
**Post date:** [September 10, 2009, 2:55pm UTC](https://www.ruby-forum.com/t/sql-in-query-using-active-record/175268/3 "2009-09-10T14:55:48Z")

</div>

Thanks, you pointed my in the right direction. Not many examples  
using :joins. I ended up with

Stage.find(:all, :joins =\> :job, :conditions =\> [“jobs.status = ? and  
stages.name = ?”,“Active”,“Application”])

Which is my real life models where Jobs have many Stages.

Steve

On Sep 10, 6:31 am, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![AppleII717](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/59ef9b/32.png) [@AppleII717](https://www.ruby-forum.com/u/AppleII717)\
**Post date:** [September 10, 2009, 6:55pm UTC](https://www.ruby-forum.com/t/sql-in-query-using-active-record/175268/4 "2009-09-10T18:55:44Z")

</div>

AppleII717 wrote:

> Thanks, you pointed my in the right direction. Not many examples  
> using :joins. I ended up with
> 
> Stage.find(:all, :joins =\> :job, :conditions =\> [“jobs.status = ? and  
> stages.name = ?”,“Active”,“Application”])

You may be able to rewrite this using the hash syntax for :conditions.  
You might also want to look at named\_scope and possibly  
acts\_as\_state\_machine.

> Which is my real life models where Jobs have many Stages.
> 
> Steve
> 
> On Sep 10, 6:31�am, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

## Best,

Marnen Laibow-Koser  
[http://www.marnen.org](http://www.marnen.org)  
removed\_email\_address@domain.invalid