How do I use ActiveRecord to find unrelated records?

Question

I have a many-to-many relationship set up through a join model. Essentially, I allow people to express interests in activities.

class Activity < ActiveRecord::Base
  has_many :personal_interests
  has_many :people, :through => :personal_interests
end

class Person < ActiveRecord::Base
  has_many :personal_interests
  has_many :activities, :through => :personal_interests
end

class PersonalInterest < ActiveRecord::Base
  belongs_to :person
  belongs_to :activity
end

I now want to find out: in which activities has a particular user not expressed interest? This must include activities that have other people interested as well as activities with exactly zero people interested.

A successful (but inefficent) method were two separate queries:

(Activity.all - this_person.interests).first

How can I neatly express this query in ActiveRecord? Is there a (reliable, well-kept) plugin that abstracts the queries?

Answer 1

I think the easiest way will be to just use an SQL where clause fragment via the :conditions parameter.

For example:

Activity.all(:conditions => ['not exists (select 1 from personal_interests where person_id = ? and activity_id = activities.id)', this_person.id])

Totally untested, and probably doesn't work exactly right, but you get the idea.