ActiveRecord 中使用 habtm 进行双重连接

发布于 2024-07-29 19:22:46 字数 932 浏览 6 评论 0原文

我有一个奇怪的情况，涉及需要双重内部联接。我已经尝试过我需要的查询，我只是不知道如何让 Rails 做到这一点。

数据

帐户（has_many：sites）
站点（habtm：users，belongs_to：account）
用户（habtm：sites）

忽略它们是habtm或其他什么，我可以使它们成为habtm或has_many：through。

我希望能够执行

@user.accounts

或

@account.users

然后我当然应该能够执行

@user.accounts < @some_other_account

然后让 @user.sites 包含来自 @some_other_account 的所有网站。

我摆弄了 habtm 和 has_many :through 但无法让它做我想做的事。

基本上我需要以这样的查询结束（从 phpmyadmin 复制。经过测试并有效）：

SELECT accounts.* 
FROM accounts
INNER JOIN sites ON sites.account_id = accounts.id
INNER JOIN user_sites ON sites.id = user_sites.site_id
WHERE user_sites.user_id = 2

我可以这样做吗？这种双重连接是个好主意吗？我假设如果用户一开始就与帐户关联，然后担心获得@user.sites，那么它会工作得更好，但如果保持原样，它对于许多其他事情会更好（用户 <- > 站点）。

原文

I have a weird situation involving the need of a double inner join. I have tried the query I need, I just don't know how to make rails do it.

The Data

Account (has_many :sites)
Site (habtm :users, belongs_to :account)
User (habtm :sites)

Ignore that they are habtm or whatever, I can make them habtm or has_many :through.

I want to be able to do

@user.accounts

@account.users

Then of course I should be able to do

@user.accounts < @some_other_account

And then have @user.sites include all the sites from @some_other_account.

I've fiddled with habtm and has_many :through but can't get it to do what I want.

Basically I need to end up with a query like this (copied from phpmyadmin. Tested and works):

SELECT accounts.* 
FROM accounts
INNER JOIN sites ON sites.account_id = accounts.id
INNER JOIN user_sites ON sites.id = user_sites.site_id
WHERE user_sites.user_id = 2

Can I do this? Is it even a good idea to have this double join? I am assuming it would work better if users had the association with accounts to begin with, and then worry about getting @user.sites instead, but it works better for many other things if it is kept the way it is (users <-> sites).

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

战皆罪 2024-08-05 19:22:46

我认为最好为此创建自定义方法，而不是试图将其硬塞到关联中。例如。

# in user.rb
def accounts
  Account.all(:include => {:sites => :users}, :conditions => ["users.id=?", self])
end

def add_account(other_account)
  other_account.sites.each do |site|
    self.sites << site
  end
end

# in account.rb
def users
  User.all(:include => {:sites => :account}, :conditions => ["accounts.id=?", self])
end

未经测试，但这应该适用于 HABTM 或 has_many :through 关联。您可以对查询进行一些优化，具体取决于您采用的方法。

有一天，我们可能会获得对深度嵌套 has_many :through 的支持，它可以处理其中的一些问题。

I think it's best to create custom methods for this rather than trying to shoe-horn it into an association. For example.

# in user.rb
def accounts
  Account.all(:include => {:sites => :users}, :conditions => ["users.id=?", self])
end

def add_account(other_account)
  other_account.sites.each do |site|
    self.sites << site
  end
end

# in account.rb
def users
  User.all(:include => {:sites => :account}, :conditions => ["accounts.id=?", self])
end

Untested, but that should work for either a HABTM or has_many :through association. There are some optimizations you can make with the query depending on which approach you go with.

Someday we might get support for deeply nested has_many :through which would handle some of this.

回复收藏 0 原文