AR - find all records not already associated

Rails
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 B. 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,
–Â
Marnen Laibow-Koser
http://www.marnen.org
[email protected]

4

On Jan 18, 1:42Â pm, Marnen Laibow-Koser [email protected] wrote:

Here’s sample SQL – you can work out the find!

Thanks. I will have a go at it.

5

On Jan 18, 6:42Â pm, Marnen Laibow-Koser [email protected] wrote:

C belongs_to :B

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