AR - find all records not already associated

rubyonrails-talk
1

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

2

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?

3

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,

4

Thanks. I will have a go at it.

5

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