Combining Rails with Multiple Foreign Keys

Question

I want to be able to use two columns in one table to define relationships. Thus, using the task application 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 execute Task.first.owner , which returns user one and Task.first.assignee , which returns user two, but User.first.task does not return anything. This is due to the fact that the task does not belong to the user, they belong to the owner and assignee. In this way,

Attempt 2:

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

It just crashes because two foreign keys are not supported.

Therefore, I want to say User.tasks and get both user-owned and assigned tasks.

In principle, somehow build a relationship that will be equal to the query Task.where(owner_id || assignee_id == 1)

Is it possible?

Update

I don't want to use finder_sql , but this unconfirmed answer seems close to what I want: Rails - Association with multiple indexes

Thus, this method will 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 

Although I can get it working 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 the User class.

---

Using has_many :tasks does not make sense at all, since we do not have a column named user_id in the tasks table.

What I did to solve the problem in my case:

 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 

So you can call User.first.assigned_tasks as well as User.first.owned_tasks .

Now you can define a method called tasks that returns a combination of assigned_tasks and owned_tasks .

This may be a good solution, as far as readability, but from a performance point of view, it would not be as good as now to get tasks , two queries will be issued twice, and then the result of these two queries should also be combined.

So, to get the tasks belonging to the user, we define our own tasks method in the User class as follows:

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

Thus, he will receive all the results in one query, and we would not need to combine or combine any results.

Answer 2

Rails 5:

you need to cancel the default offer where see @Dwight answer if you still want to have has_many associaiton.

Although User.joins(:tasks) gives me

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

As already impossible, you can use the @Arslan Ali solution.

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 the comment by @JonathanSimmons

Passing a user object to the scope of the user model looks like the reverse approach

You do not need to pass the user model to this area. The current user instance is automatically passed to this lambda. Call it that:

 user = User.find(9001) user.tasks 

Update 2:

if possible, can you expand this answer to explain what is happening? I would like to understand this better so that I can implement something like this. thank

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

 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 

Answer 3

I developed a solution for this. I am open to any pointers on how I can do 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 returns the ActiveRecord::Relation object I was looking for.

So now I can do something like this:

User.first.tasks.completed , and the result will be an all completed task belonging to or assigned to the first user.

Answer 4

The extension of @ dre-hh's answer above, which, as I found, no longer works as expected in Rails 5. Appears Rails 5 now includes a default sentence in which WHERE tasks.user_id = ? acts WHERE tasks.user_id = ? that does not work as there is no user_id in this scenario.

I found that it is still possible to make it work with the has_many association, you just need to undo that extra where clause added by Rails.

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

Answer 5

My answer to Associations and (several) foreign keys in rails (3.2): how to describe them in a model and record migrations - this is just for you!

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 

Note: If you are using RailsAdmin and you need to create a new entry or modify an existing entry, please do not do what I suggested. Since this hack will cause a problem when you do something like this:

 current_user.tasks.build(params) 

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

So, consider my hacking method as an off-field method, but don't do this.