HABTM 关系和连接表

Question

我正在尝试连接我拥有的两个连接表的值并根据条件关系显示结果...并且我遇到了一些问题

我有一个用户模型（：名称，：密码，：电子邮件），和事件模型（：名称，：等）和兴趣模型（：名称）

我在每个模型中创建了大约 5 条记录。

然后我创建了两个连接表 ->用户兴趣和事件兴趣；每个不包含主键，仅分别由 user_id/interest_id 和 event_id/interest_id 组成。

然后我将 HABTM 关系添加到模型文件中

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

现在我想创建一个控制器，它只查找用户兴趣与事件兴趣相对应的事件

经过一段时间的研究，我发现我需要以下领域的东西

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

或类似的东西...我有点迷失了..你如何陈述查找条件？...我知道如何在sql中进行内部连接，但我正在寻找优雅的Rails方法来做到这一点。 ..有什么建议吗？

Answer 1

优雅的 ruby​​ 方法是使用命名范围。但是，由于您决定使用 has_and_belongs_to_many 关系而不是 has_many :through 关系，因此您将需要使用原始 SQL 定义联接，这不是很优雅。由于 Rails 处理 SQL 生成的方式，您必须创建一个供单个用户使用的范围，以及一个供多个用户使用的第二个命名范围。

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

现在，您可以这样做来获取用户可能感兴趣的所有事件：

@events = Event.shares_interest_with_user(@user)

或者这样做来获取用户列表可能感兴趣的所有事件：

@events = Event.shares_interest_with_users(@users)

但正如我警告的那样，这并不是真正的优雅。

如果您通过使用正确的连接模型而不是 HABTM 关系将关系重新定义为 has_many，则可以大大简化连接。您的情况需要嵌套有很多直通插件才能正常工作。注意：您必须在所有其他模型中添加相应的 has_many/belongs_to 语句。甚至是连接模型。

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

现在，以下内容将起作用。

@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)

您甚至可以定义一个命名范围来选择尚未发生的事件并将两者链接起来：

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)