# AR - find all records not already associated

**URL:** <https://discuss.rubyonrails.org/t/ar-find-all-records-not-already-associated/48663>\
**Category:** rubyonrails-talk\
**Created:** [January 18, 2010, 6:24pm UTC](https://discuss.rubyonrails.org/t/ar-find-all-records-not-already-associated/48663 "2010-01-18T18:24:04Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![James\_B\_Byrne](https://avatars.discourse-cdn.com/v4/letter/j/5e9695/32.png) [@James\_B\_Byrne](https://discuss.rubyonrails.org/u/James_B_Byrne)\
**Post date:** [January 18, 2010, 6:24pm UTC](https://discuss.rubyonrails.org/t/ar-find-all-records-not-already-associated/48663/1 "2010-01-18T18:24:04Z")

</div>

Given

A has\_many :B :through =\> :C A has\_many :C

B has\_many :A :through =\> :C B has\_many :C

C belongs\_to :A C belongs\_to :B

What is the idiomatic way to find all B that are not associated with a given value for A? This will work but is there a simpler way?

this\_a = A.find\_by\_attribute('value') all\_b = B.find(:all) assigned = this\_a.bs unassigned = all\_b - assigned

---

<div class="post-metadata">

**Author:** ![James\_B\_Byrne](https://avatars.discourse-cdn.com/v4/letter/j/5e9695/32.png) [@James\_B\_Byrne](https://discuss.rubyonrails.org/u/James_B_Byrne)\
**Post date:** [January 18, 2010, 6:26pm UTC](https://discuss.rubyonrails.org/t/ar-find-all-records-not-already-associated/48663/2 "2010-01-18T18:26:59Z")

</div>

Let me rephrase the question.

How would one construct an AR find so as to return the desired subset of B records in a single SQL query?

---

<div class="post-metadata">

**Author:** ![11155](https://avatars.discourse-cdn.com/v4/letter/1/d26b3c/32.png) [@11155](https://discuss.rubyonrails.org/u/11155)\
**Post date:** [January 18, 2010, 6:42pm UTC](https://discuss.rubyonrails.org/t/ar-find-all-records-not-already-associated/48663/3 "2010-01-18T18:42:33Z")

</div>

James Byrne wrote:

> Given
> 
> A has\_many :B :through =\> :C A has\_many :C
> 
> B has\_many :A :through =\> :C B has\_many :C
> 
> C belongs\_to :A C belongs\_to :B
> 
> What is the idiomatic way to find all B that are not associated with a given value for A? This will work but is there a simpler way?
> 
> this\_a = A.find\_by\_attribute('value') all\_b = B.find(:all) assigned = this\_a.bs unassigned = all\_b - assigned

Here's sample SQL -- you can work out the find!

SELECT \* from b LEFT JOIN c on (c.b\_id = b.id) LEFT JOIN a on (c.a\_id = a.id) WHERE a.id != 17 # or whatever value

Best,

---

<div class="post-metadata">

**Author:** ![James\_B\_Byrne](https://avatars.discourse-cdn.com/v4/letter/j/5e9695/32.png) [@James\_B\_Byrne](https://discuss.rubyonrails.org/u/James_B_Byrne)\
**Post date:** [January 18, 2010, 7:00pm UTC](https://discuss.rubyonrails.org/t/ar-find-all-records-not-already-associated/48663/4 "2010-01-18T19:00:53Z")

</div>

Thanks. I will have a go at it.

---

<div class="post-metadata">

**Author:** ![Frederick\_Cheung](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/frederick_cheung/32/3901_2.png) [@Frederick\_Cheung](https://discuss.rubyonrails.org/u/Frederick_Cheung)\
**Post date:** [January 18, 2010, 10:20pm UTC](https://discuss.rubyonrails.org/t/ar-find-all-records-not-already-associated/48663/5 "2010-01-18T22:20:38Z")

</div>

> James Byrne wrote: \> Given
> 
> \> A has\_many :B :through =\> :C \> A has\_many :C
> 
> \> B has\_many :A :through =\> :C \> B has\_many :C
> 
> \> C belongs\_to :A \> C belongs\_to :B
> 
> \> What is the idiomatic way to find all B that are not associated with a \> given value for A? This will work but is there a simpler way?
> 
> \> this\_a = A.find\_by\_attribute('value') \> all\_b = B.find(:all) \> assigned = this\_a.bs \> unassigned = all\_b - assigned
> 
> Here's sample SQL -- you can work out the find!
> 
> SELECT \* from b LEFT JOIN c on (c.b\_id = b.id) LEFT JOIN a on (c.a\_id = a.id) WHERE a.id != 17 # or whatever value

I don't think that's quite right -

if the c table contains

a\_id b\_id 1 2 17 2 3 2

Then clearly the b with id 2 is associated with the a with id 17, but I believe the above query would return the b with id 2 (twice)

something like

select \* from b left join c on c.b\_id = b.id and c.a\_id = 17 left join a on c.a\_id = a.id where a.id is null

should do the trick (and if you have foreign key constraints then you don't ever need to join the a table

Fred