In Rails how does one find disassociated records in a HABTM relationship?

Question

I have a HABTM relationship between Videos and Campaigns in Rails which means the association is stored in a join table. I want to find all the Videos that do NOT have an associated campaign. What would be the most efficient way of doing this?

Thank you for looking =)

Answer 1

Video.all(:include => :campaigns, :conditions => ["campaigns.id IS ?", nil])

the :include will do a left join to the associated table so anything without a campaign should have NULL values for the campaign field values.

Answer 2

The SQL way:

Videos.select_by_sql("SELECT * FROM videos WHERE id NOT IN (SELECT video_id FROM campaign_videos)")

Answer 3

The Ruby way:

Video.all.select {|v| v.campaigns.empty?}

I think this is more elegant if you use it standalone in a method. However, I would recommend to write a named scope for that. Then again, Geoffs version is the right one:

named_scope :campaign_less, :include => :campaigns, :conditions => ["campaigns.id IS ?", nil]

Besides, Geoffs solution is probably more efficient as it is more lowlevel.