Rails association with multiple foreign keys

Question

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: ``` 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)` 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, Attempt 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 b…

Accepted Answer

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.

Using `has_many :tasks` doesn't make sense at all as we do not have any column named `user_id` in 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"
  belongs_to :assignee, class_name: "User"
  # 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`.

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

```
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.

Rails association with multiple foreign keys

Solution

Problem

Related problems