Rails - Find results from two join tables

Question

I have have 3 Tables of data and 2 Join Tables connecting everything. I'm trying to figure out a way to query the results based on the condition that the join table data is the same.

To explain, I have User, Interest, and Event Tables. These tables are linked through an HABTM relationship (which is fine for my needs since I dont need to store any other fields) and joined through two join tables. So i also have a UsersInterests table with (user_id, interest_id) and a EventsInterests table with (event_id, interest_id).

The problem comes when trying to query all the Events where the users interests match the events interests.

I thought it would look something like this...

 @events= Event.find(:all, :conditions => [@user.interests = @event.interests])

but I get the error "undefined method `interests' for nil:NilClass", Is there something wrong with my syntax or my logic?

Answer 1

You're problem is that either @user or @event is undefined. Even if you define them, before executing this statement, the conditions option supplied is invalid, [@user.interests = @event.interests].

This named scope on events should do the trick

class Event < ActiveRecord::Base
  ...
  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"
    }
end

@events = Event.shares_interest_with_user(@user)

Answer 2

Given Event <-> Interest <-> User query all the Events where the users interests match the events interests (so the following will find all such Events that this event's interest are also interests of at least one user).

First try, the simplest thing that could work:

@events = []
Interest.all.each do |i|
  i.events.each do |e|
    @events << e if i.users.any?
  end
end
@events.uniq!

Highly inefficient, very resource hungry and cpu intensive. Generates lots of sql queries. But gets the job done.

Second try should incorporate some complicated join, but the more I think about it the more I see how vague your problem is. Be more precise.

Answer 3

Not sure I completely follow what you are trying to do. If you have one user and you want all events that that user also has interest in then something like:

Event.find(:all, :include => [:events_interests], :conditions => ['events_interests.interest_id in (?)', @user.interests.collect(&:id)])

should probably work.