How to find all items not related to another model - Rails 3

Question

I have a fairly complicated lookup i'm trying to do in Rails and I'm not entirely sure how hoping someone can help.

I have two models, User and Place.

A user is related to Place twice. Once for visited_places and once for planned_places. Its a many to many relationship but using has_many :through. Here's the relationship from User.

has_many :visited_places
has_many :visited, :class_name=>"Place", :through=>:visited_places, :source=>:place

has_many :planned_places
has_many :planned, :class_name=>"Place", :through=>:planned_places, :source=>:place

In place the relationship is also defined. Here's the definition there

has_many :visited_users, :class_name=>"User", :through=>:visited_places  
has_many :planned_users, :class_name=>"User", :through=>:planned_places

I'm trying to write a find on Place that returns all places in the database that aren't related to a User through either visited or planned. Right now I'm accomplishing this by simply querying all Places and then subtracting visited and planned from the results but I want to add in pagination and I'm worried this could complicate that. Here's my current code.

all_places = Place.find(:all)        
all_places = all_places - user.visited - user.planned

Anyone know how i can accomplish this in just a call to Place.find. Also this is a Rails 3 app so if any of the active record improvements make this easier they are an option.

Answer 1

How about something like:

unvisited_places = Place.find(:all, :conditions => "id NOT IN(#{visited_places.map(&:place_id)})")

That's the general idea -- it can be made more efficient and convenient depending on your final needs.

Answer 2

You don't show it but if I am right in assuming that the VisitedPlace and PlannedPlace models have a belongs_to :user relationships then those tables have a user_id secondary key, right?

So in that case I would think it would be most efficient to do this in the database in which case you are looking for a select across a table join of places, visited_places and planned_places where users.id is not in either of visited_places or planned_places

Answer 3

in sql:

select * from places where id not in 
  (
    (select place_id from visited_places where user_id = ?) 
  union 
    (select place_id from planned_places where user_id=?)
  )

If that query works, you can use as follows:

Places.find_by_sql(...the complete sql query ...)

I would not know how to write such a query, with an exclusion, in Rails 3 otherwise.