HABTM Relationships and the Join Table

Question

I'm trying to connect the values of two join tables that I have and show the results based on a conditional relationship...and i'm having some problems

I have a Users Model(:name, :password, :email), and Events model(:name, :etc) and Interests model (:name)

I created about 5 records in each model.

Then I created two join tables -> UsersInterests and EventsInterests; each not containing a primary key and only comprised of the user_id/interest_id and event_id/interest_id respectively.

Then I added to the model files the HABTM Relationship

users => has_and_belongs_to_many :interests
events => has_and_belongs_to_many :interests
interests => has_and_belongs_to_many :users
         has_and_belongs_to_many :events

Now I wanted to create a controller that finds only the events where the users interests correspond with the events interests

From working on this for a while I've figured that I need something in the area of

@Events = Event.User.find([condition])
[condition] = where users.interest == event.interest

or something like that... I'm kind of lost..How do you state the find condition?...I know how to do the inner join in sql but I'm looking for the elegant Rails way to do this... any tips guys?

Answer 1

The elegant ruby way to do this is with named scopes. However because you've decided to use has_and_belongs_to_many relationships instead of has_many :through relationships, you're going to need to define the join with raw SQL, which isn't very elegant. And because of the way Rails handles SQL generation, you will have to make a scope for use with a single user, and a second named scope for use with many users.

Class Event < ActiveRecord::Base
  ...

  #find events that share an interest with a single user
  named_scope :shares_interest_with_user, lambda {|user|
    { :joins => "LEFT JOIN events_interests ei ON ei.event_id = events.id " +
         "LEFT JOIN users_intersets ui ON ui.interest_id = ei.interest_id",
      :conditions => ["ui.user_id = ?", user], :group_by => "events.id"
    }

  #find events that share an interest with a list of users
  named_scope :shares_interest_with_users, lambda {|users|
    { :joins => "LEFT JOIN events_interests ei ON ei.event_id = events.id " +
         "LEFT JOIN users_intersets ui ON ui.interest_id = ei.interest_id",
      :conditions => ["ui.user_id IN ?", users], :group_by => "events.id"
    }    
  }

  #find events that share an interest with any user
  named_scope :shares_interest_with_any_user, lambda {
    { :joins => "LEFT JOIN events_interests ei ON ei.event_id = events.id " +
         "JOIN users_intersets ui ON ui.interest_id = ei.interest_id",
      :conditions => "ui.user_id IS NOT NULL", :group_by => "events.id"
    }    
  }

end

Now you can do this to get all the events a user might be interested in:

@events = Event.shares_interest_with_user(@user)

Or this to get all the events a list of users might be interested in:

@events = Event.shares_interest_with_users(@users)

But as I warned, that's not really elegant.

You can greatly simplify the joins if you redefine your relationships to be has_many through relationships with proper join models instead of HABTM relationships. Your case would require the nested has many through plugin for this to work. N.B. You'll have to add corresponding has_many/belongs_to statements in all of the other models. Even the join models.

Class Event < ActiveRecord::Base
  has_many :event_interests
  has_many :interests, :through => :event_interests
  has_many :user_interests, :through => :interests
  has_many :users, :through => :user_interests
  ...   

  #find events that share an interest with a list of users
  named_scope :shares_interest_with_users, lambda {|user|
    { :joins => :user_interests, :group_by => "events.id",
      :conditions => {:user_interests => {:user_id => user}}
    }    
  }

  #find events that share an interest with any user
  named_scope :shares_interest_with_any_user, lambda {
    { :joins => :user_interests, :group_by => "events.id",
      :conditions => "user_interests.user_id IS NOT NULL"
    }

end

Now, the following will work.

@user = User.first; @users = User.find(1,2,3)

# @events = all events a single user would be interested in
@events = Event.shares_interest_with_users(@user)

# @events = all events any of the listed users would be interested in.
@events = Event.shares_interest_with_users(@user)

You could even define a named scope to select events that haven't happened yet and chain the two:

named_scope :future_events, lambda {
   { :conditions => ["start_time > ?", Time.now]}
}

Events.future_events #=> Events that haven't started yet.

# Events that a user would be interested in but only choose those 
# that haven't started yet.
Events.future_events.shares_interest_with_user(@user)