Ruby-on-rails Rails 与多个外键的关联

Rails association with multiple foreign keys

提问by JonathanSimmons

I want to be able to use two columns on one table to define a relationship. So using a task app as an example.

我希望能够在一个表上使用两列来定义关系。因此，以任务应用程序为例。

Attempt 1:

尝试 1：

class User < ActiveRecord::Base
  has_many :tasks
end

class Task < ActiveRecord::Base
  belongs_to :owner, class_name: "User", foreign_key: "owner_id"
  belongs_to :assignee, class_name: "User", foreign_key: "assignee_id"
end

So then Task.create(owner_id:1, assignee_id: 2)

那么 Task.create(owner_id:1, assignee_id: 2)

This allows me to perform Task.first.ownerwhich returns user oneand Task.first.assigneewhich returns user twobut User.first.taskreturns nothing. Which is because task doesn't belong to a user, they belong to ownerand assignee. So,

这允许我执行Task.first.owner哪个返回用户一，Task.first.assignee哪个返回用户二但不User.first.task返回任何内容。这是因为任务不属于用户，它们属于owner和assignee。所以，

Attempt 2:

尝试 2：

class User < ActiveRecord::Base
  has_many :tasks, foreign_key: [:owner_id, :assignee_id]
end

class Task < ActiveRecord::Base
  belongs_to :user
end

That just fails altogether as two foreign keys don't seem to be supported.

这完全失败了，因为似乎不支持两个外键。

So what I want is to be able to say User.tasksand get both the users owned and assigned tasks.

所以我想要的是能够说User.tasks并获得用户拥有和分配的任务。

Basically somehow build a relationship that would equal a query of Task.where(owner_id || assignee_id == 1)

基本上以某种方式建立一个关系，这将等于一个查询 Task.where(owner_id || assignee_id == 1)

Is that possible?

那可能吗？

Update

更新

I'm not looking to use finder_sql, but this issue's unaccepted answer looks to be close to what I want: Rails - Multiple Index Key Association

我不打算使用finder_sql，但这个问题的未接受答案看起来接近我想要的：Rails - Multiple Index Key Association

So this method would look like this,

所以这个方法看起来像这样，

Attempt 3:

尝试 3：

class Task < ActiveRecord::Base
  def self.by_person(person)
    where("assignee_id => :person_id OR owner_id => :person_id", :person_id => person.id
  end 
end

class Person < ActiveRecord::Base

  def tasks
    Task.by_person(self)
  end 
end

Though I can get it to work in Rails 4, I keep getting the following error:

虽然我可以让它在 中工作Rails 4，但我不断收到以下错误：

ActiveRecord::PreparedStatementInvalid: missing value for :owner_id in :donor_id => :person_id OR assignee_id => :person_id

采纳答案by Arslan Ali

TL;DR

TL; 博士

class User < ActiveRecord::Base
  def tasks
    Task.where("owner_id = ? OR assigneed_id = ?", self.id, self.id)
  end
end

Remove has_many :tasksin Userclass.

has_many :tasks在User课堂上删除。

---

---

Using has_many :tasksdoesn't make sense at all as we do not have any column named user_idin table tasks.

使用has_many :tasks完全没有意义，因为我们没有user_id在 table 中命名的任何列tasks。

What I did to solve the issue in my case is:

在我的情况下，我为解决问题所做的工作是：

class User < ActiveRecord::Base
  has_many :owned_tasks,    class_name: "Task", foreign_key: "owner_id"
  has_many :assigned_tasks, class_name: "Task", foreign_key: "assignee_id"
end

class Task < ActiveRecord::Base
  belongs_to :owner,    class_name: "User", foreign_key: "owner_id"
  belongs_to :assignee, class_name: "User", foreign_key: "assignee_id"
  # Mentioning `foreign_keys` is not necessary in this class, since
  # we've already mentioned `belongs_to :owner`, and Rails will anticipate
  # foreign_keys automatically. Thanks to @jeffdill2 for mentioning this thing 
  # in the comment.
end

This way, you can call User.first.assigned_tasksas well as User.first.owned_tasks.

通过这种方式，你可以调用User.first.assigned_tasks以及User.first.owned_tasks。

Now, you can define a method called tasksthat returns the combination of assigned_tasksand owned_tasks.

现在，你可以定义一个名为方法tasks是回报的组合assigned_tasks和owned_tasks。

That could be a good solution as far the readability goes, but from performance point of view, it wouldn't be that much good as now, in order to get the tasks, two queries will be issued instead of once, and then, the result of those two queries need to be joined as well.

就可读性而言，这可能是一个很好的解决方案，但从性能的角度来看，它不会像现在那么好，为了获得tasks，将发出两个查询而不是一次，然后，结果这两个查询中的一个也需要连接。

So in order to get the tasks that belong to a user, we would define a custom tasksmethod in Userclass in the following way:

因此，为了获取属于用户的任务，我们将通过以下方式tasks在User类中定义一个自定义方法：

def tasks
  Task.where("owner_id = ? OR assigneed_id = ?", self.id, self.id)
end

This way, it will fetch all the results in one single query, and we wouldn't have to merge or combine any results.

这样，它将在一个查询中获取所有结果，而我们不必合并或组合任何结果。

回答by Dwight

Extending upon @dre-hh's answer above, which I found no longer works as expected in Rails 5. It appears Rails 5 now includes a default where clause to the effect of WHERE tasks.user_id = ?, which fails as there is no user_idcolumn in this scenario.

扩展@dre-hh 上面的答案，我发现它在 Rails 5 中不再按预期工作。看来 Rails 5 现在包含一个默认的 where 子句来影响WHERE tasks.user_id = ?，由于user_id在这种情况下没有列而失败。

I've found it is still possible to get it working with a has_manyassociation, you just need to unscope this additional where clause added by Rails.

我发现它仍然可以与has_many关联一起工作，您只需要取消 Rails 添加的这个附加 where 子句的范围。

class User < ApplicationRecord
  has_many :tasks, ->(user) {
    unscope(:where).where(owner: user).or(where(assignee: user)
  }
end

回答by dre-hh

Rails 5:

导轨 5：

you need to unscope the default where clause see @Dwight answer if you still want a has_many associaiton.

如果您仍然想要 has_many 关联，则需要取消默认 where 子句的范围，请参阅 @Dwight 答案。

Though User.joins(:tasks)gives me

虽然User.joins(:tasks)给了我

ArgumentError: The association scope 'tasks' is instance dependent (the scope block takes an argument). Preloading instance dependent scopes is not supported.

As it is no longer possible you can use @Arslan Ali solution as well.

由于不再可能，您也可以使用@Arslan Ali 解决方案。

Rails 4:

导轨 4：

class User < ActiveRecord::Base
  has_many :tasks, ->(user){ where("tasks.owner_id = :user_id OR tasks.assignee_id = :user_id", user_id: user.id) }
end

Update1: Regarding @JonathanSimmons comment

更新1：关于@JonathanSimmons 评论

Having to pass the user object into the scope on the User model seems like a backwards approach

必须将用户对象传递到 User 模型的作用域中似乎是一种倒退的方法

You don't have to pass the user model to this scope. The current user instance is passed automatically to this lambda. Call it like this:

您不必将用户模型传递到此范围。当前用户实例会自动传递给此 lambda。像这样调用它：

user = User.find(9001)
user.tasks

Update2:

更新2：

if possible could you expand this answer to explain what's happening? I'd like to understand it better so I can implement something similar. thanks

如果可能的话，你能扩展这个答案来解释发生了什么吗？我想更好地理解它，以便我可以实现类似的东西。谢谢

Calling has_many :taskson ActiveRecord class will store a lambda function in some class variable and is just a fancy way to generate a tasksmethod on its object, which will call this lambda. The generated method would look similar to following pseudocode:

调用has_many :tasksActiveRecord 类会将 lambda 函数存储在某个类变量中，这只是tasks在其对象上生成方法的一种奇特方式，该方法将调用此 lambda。生成的方法类似于以下伪代码：

class User

  def tasks
   #define join query
   query = self.class.joins('tasks ON ...')
   #execute tasks_lambda on the query instance and pass self to the lambda
   query.instance_exec(self, self.class.tasks_lambda)
  end

end

回答by JonathanSimmons

I worked out a solution for this. I'm open to any pointers on how I can make this better.

我为此制定了一个解决方案。我愿意接受任何关于如何使这更好的指示。

class User < ActiveRecord::Base

  def tasks
    Task.by_person(self.id)
  end 
end

class Task < ActiveRecord::Base

  scope :completed, -> { where(completed: true) }   

  belongs_to :owner, class_name: "User", foreign_key: "owner_id"
  belongs_to :assignee, class_name: "User", foreign_key: "assignee_id"

  def self.by_person(user_id)
    where("owner_id = :person_id OR assignee_id = :person_id", person_id: user_id)
  end 
end

This basically overrides the has_many association but still returns the ActiveRecord::Relationobject I was looking for.

这基本上覆盖了 has_many 关联，但仍然返回ActiveRecord::Relation我正在寻找的对象。

So now I can do something like this:

所以现在我可以做这样的事情：

User.first.tasks.completedand the result is all completed task owned or assigned to the first user.

User.first.tasks.completed结果是所有已完成的任务拥有或分配给第一个用户。

回答by user2309631

Since Rails 5 you can also do that which is the ActiveRecord safer way:

从 Rails 5 开始，你也可以这样做，这是 ActiveRecord 更安全的方式：

def tasks
  Task.where(owner: self).or(Task.where(assignee: self))
end

回答by sunsoft

My answer to Associations and (multiple) foreign keys in rails (3.2) : how to describe them in the model, and write up migrationsis just for you!

我对Rails (3.2) 中的关联和（多个）外键的回答：如何在模型中描述它们，并编写迁移只适合您！

As for your code,here are my modifications

至于你的代码，这是我的修改

class User < ActiveRecord::Base
  has_many :tasks, ->(user) { unscope(where: :user_id).where("owner_id = ? OR assignee_id = ?", user.id, user.id) }, class_name: 'Task'
end

class Task < ActiveRecord::Base
  belongs_to :owner, class_name: "User", foreign_key: "owner_id"
  belongs_to :assignee, class_name: "User", foreign_key: "assignee_id"
end

Warning:If you are using RailsAdmin and need to create new record or edit existing record,please don't do what I've suggested.Because this hack will cause problem when you do something like this:

警告：如果您正在使用 RailsAdmin 并且需要创建新记录或编辑现有记录，请不要执行我的建议。因为当您执行以下操作时，此 hack 会导致问题：

current_user.tasks.build(params)

The reason is that rails will try to use current_user.id to fill task.user_id,only to find that there is nothing like user_id.

原因是rails会尝试用current_user.id来填充task.user_id，结果发现没有us​​er_id之类的东西。

So,consider my hack method as an way outside the box,but don't do that.

所以，把我的 hack 方法看作是一种开箱即用的方法，但不要那样做。