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 build a relationship that would equal a query of 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

So this method would look like this,

Attempt 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:

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

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

---

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", 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.

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.