适用范围适用于自加盟HABTM协会 [英] Scope for Self-joining HABTM Association

问题描述

使用的Rails 3.1.3 在红宝石1.9.2 ，我有：

class User < ActiveRecord::Base
  has_and_belongs_to_many :states
end

class State < ActiveRecord::Base
  has_and_belongs_to_many :users
end

A 用户与许多国家 s之关联，以及国家与许多用户秒。给定一个用户，我想找到的所有其他用户取值是谁在她的任何状态。

A User is associated with many States, and a State with many Users. Given a User, I want to find all other Users who are in any of her states.

我试过：

class User < ActiveRecord::Base
  scope :in_state, lambda { |states| joins(:states).where(:states => states) }
end

User.in_state(current_user.states).all

通过这个错误（格式化以提高可读性）：

With this error (formatted for readability):

Mysql2::Error: Not unique table/alias: 'states_users': 
SELECT COUNT(*) 
FROM `users` 
  INNER JOIN `states_users` ON `states_users`.`user_id` = `users`.`id` 
  INNER JOIN `states` ON `states`.`id` = `states_users`.`state_id` 
  LEFT OUTER JOIN states_users on users.id = states_users.user_id AND states_users.state_id IS NULL 
WHERE `states`.`id` IN (8)

我意识到我需要一些方法来别名 states_users 引用之一，但我在搞清楚如何使用的 范围

I realize I need some way to alias one of the states_users references, but I'm at a loss for figuring out how to do this with scope.

下面是我会写的SQL：

Here's the SQL I would write:

SELECT u2.*
FROM users u
  INNER JOIN states_users su ON su.user_id = u.id
  INNER JOIN states s ON s.id = su.state_id
  INNER JOIN states_users su2 ON s.id = su2.state_id
  INNER JOIN users u2 ON u2.id = su2.user_id
WHERE u.id = 771

思考？

感谢。

更新12/14/2011 @ 10:48：

下面是什么工作：

scope :in_states_of, lambda { |user|
  joins('INNER JOIN states_users su ON su.user_id = users.id ' +
        'INNER JOIN states s ON s.id = su.state_id '           +
        'INNER JOIN states_users su2 ON s.id = su2.state_id '  +
        'INNER JOIN users u ON u.id = su2.user_id').
  where("u.id = ?", user.id) }

User.in_states_of(current_user)

这不是特别优雅，但它的工作。你有别名用户为已进入用户参考，所以你可以链接此范围内与其他人，如

It's not particularly elegant, but it's working. You have to alias the users reference for the "incoming" User so you can chain this scope with others, such as:

scope :active, where(active => true)

User.in_states_of(current_user).active

改进的任何建议将是最欢迎的。

Any suggestions for improvement would be most welcome.

推荐答案

下面是什么工作：

scope :in_states_of, lambda { |user|
  joins('INNER JOIN states_users su ON su.user_id = users.id ' +
        'INNER JOIN states s ON s.id = su.state_id '           +
        'INNER JOIN states_users su2 ON s.id = su2.state_id '  +
        'INNER JOIN users u ON u.id = su2.user_id').
  where("u.id = ?", user.id) }

User.in_states_of(current_user)

这不是特别优雅，但它的工作。你有别名用户为已进入用户参考，所以你可以链接此范围内与其他人，如

It's not particularly elegant, but it's working. You have to alias the users reference for the "incoming" User so you can chain this scope with others, such as:

scope :active, where(active => true)

User.in_states_of(current_user).active

改进的任何建议将是最欢迎的。

Any suggestions for improvement would be most welcome.

这篇关于适用范围适用于自加盟HABTM协会的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！