HABTM Relationship and Named Scopes

Question

I've been working on this for a while now and I cant seem to find where there error.

I have a User Model(:name, :password, :email), and Event model(:name, :etc) and Interest model (:name) [>all singular<]

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. [>plural<]

My Models contain the HABTM Relationships as follows

user.rb => has_and_belongs_to_many :interests
event.rb => has_and_belongs_to_many :interests
interest.rb => has_and_belongs_to_many :users
               has_and_belongs_to_many :events
events_interests.rb => has_and_belongs_to_many :interests
                       has_and_belongs_to_many :events
users_interests.rb => has_and_belongs_to_many :users
                      has_and_belongs_to_many :interests

Whew..ok So I wanted to created a named_scope of that find all the events that share interest with a particular user. Here is some code someone helped me with.

named_scope :shares_interest_with_user, lambda {|user| { :joins => "LEFT JOIN
              events_interests ei ON ei.event_id = Event.id " +
             "LEFT JOIN users_interests ui ON ui.interest_id = ei.interest_id",
           :conditions => ["ui.user_id = ?", user]  }}

When i run from the controller =>

  @user = User.find(1) 
  @events = Event.shares_interest_with_user(@user)

I get the SQL error : "no such column: Event.id: SELECT "events".* FROM "events" LEFT JOIN events_interests ei ON ei.event_id =..........."

Now I'm sure the event model has an id and I think I have all the column names right..can anyone see what is missing?

Answer 1

I suggest moving away from HABTM and instead using has_many :through.

Here's a great blog post explaining the difference written by SO user Jaime Bellmyer.