有多个外键的Rails关联。

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)

所以任务。创建(owner_id:1、assignee_id:2)

This allows me to perform Task.first.owner which returns user one and Task.first.assignee which returns user two but User.first.task returns nothing. Which is because task doesn't belong to a user, they belong to owner and assignee. So,

这让我可以执行任务。返回用户1和任务的所有者。转让人，返回用户2但返回用户1。任务返回什么。这是因为任务不属于用户，它们属于所有者和受让人。所以,

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.tasks and get both the users owned and assigned tasks.

我想说的是用户。任务，并获得用户拥有和分配的任务。

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

基本上，以某种方式构建一个与任务查询相等的关系。其中(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 -多个索引键关联。

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

5 个解决方案

#1

TL;DR

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

Remove has_many :tasks in User class.

删除用户类中的has_many:任务。

---

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

使用has_many:tasks根本没有意义，因为我们在表任务中没有任何名为user_id的列。

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_tasks as well as User.first.owned_tasks.

这样，您可以先调用User.first。assigned_tasks以及User.first.owned_tasks。

Now, you can define a method called tasks that returns the combination of assigned_tasks and 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.

这可能是一个好的解决方案的可读性,但从性能的角度来看,它不会像现在那么多好,为了任务,两个查询将发布,而不是一次,然后,这两个查询的结果需要加入。

So in order to get the tasks that belong to a user, we would define a custom tasks method in User class in the following way:

因此，为了得到属于用户的任务，我们将在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.

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

#2

Rails 5:

Rails 5:

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

如果您仍然想要一个has_many associaiton，那么需要取消默认where子句的作用域，请参见@Dwight answer。

Though User.joins(:tasks) gives me

尽管User.joins(任务)给我

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:

Rails 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

Update1:关于@JonathanSimmons发表评论

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

必须将用户对象传递到用户模型上的范围似乎是一种反向方法

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 :tasks on ActiveRecord class will store a lambda function in some class variable and is just a fancy way to generate a tasks method on its object, which will call this lambda. The generated method would look similar to following pseudocode:

调用has_many: ActiveRecord类上的tasks将在某个类变量中存储lambda函数，这是在其对象上生成task方法的一种奇特方式，该方法将调用这个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

#3

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::Relation object I was looking for.

这基本上覆盖了has_many关联，但仍然返回我要查找的ActiveRecord::关系对象。

So now I can do something like this:

现在我可以这样做:

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

完成的任务和结果是所有已完成的任务或分配给第一个用户。

#4

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_id column in this scenario.

扩展到上面的@dre-hh的答案，我发现它在Rails 5中不再像预期的那样工作了。看起来Rails 5现在包含了where子句的默认值。user_id = ?，失败，因为在此场景中没有user_id列。

I've found it is still possible to get it working with a has_many association, 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_id = :id OR assignee_id = :id", id: user.id) }
end

#5

My answer to Associations and (multiple) foreign keys in rails (3.2) : how to describe them in the model, and write up migrations is 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，并且需要创建新记录或编辑现有记录，请不要按照我的建议做。因为当你做这样的事情时，这种方法会带来问题:

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来填补的任务。user_id，结果发现没有user_id。

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

所以，把我的hack方法当作跳出框框的一种方式，但是不要这么做。