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

Colonial

Is this gem what you're looking for? github.com/composite-primary-keys/composite_primary_keys – Corrasion

Thanks for the info mus but this is not what I'm looking for. I want a query for either or column being a given value. Not a composite primary key. – Colonial

yeah, the update makes it clear. Forget about the gem. We both thought you just want to use a composed primary key. This should be possible at least by defining a custom scope a scoped relationship. Interesting scenario. I'll have a look a it later – Lyso

FWIW my goal here is to get a given users task and retain the ActiveRecord::Relation format so I can continue to use task scopes on the result for search/filtering. – Colonial

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

Danelaw

This solution is great! It does imply a requirement for separate access to assign, owned and all tasks respectively. Something that on a large project would be great forethoguht. That however was not a requirement in my case. As for has_many "not making sense" If you read the accepted answer you'd see we did not end up using a has_many declaration at all. Assuming your requirements match the needs of every other visitor is unproductive, this answer could have been a less judgmental. – Colonial

That being said I do believe this is the better long term setup we should be advocating to people with this problem. If you would revise your answer to include a basic example of the other models and the user method to retrieve the combined set of tasks I'll revise it as the selected answer. Thanks! – Colonial

Yes, I agree with you. Using owned_tasks and assigned_tasks to get all the tasks will have a performance hint. Anyway, I've updated my answer, and have included a method in User class to get all the associated tasks, it will fetch the results in one query, and no merging/combining needs to be done. – Danelaw

This is not an ideal solution, because you are loosing the user scope and can't combine the scopes anymore like this User.join(:tasks).order_by('tasks.name') – Lyso

@Lyso Of course, this isn't an ideal solution. This is the solution according to the needs of the OP, but there is the thing that you are asking. First, it is User.joins, not User.join. Second, you can get the results equivalent of User.joins(:tasks) by doing User.joins(:assigned_tasks).joins(:owned_tasks), but using two joins will give you a lot of similar results, so you can call uniq to find out the unique results. – Danelaw

@Lyso You can also do User.joins(:assigned_tasks, :owned_tasks). There are other work arounds as well like having an association of has_many :tasks, and then saving this association in the database whenever an onwed_task or assigned_task gets saved in the database, but that depends upon different situations. – Danelaw

@zx1986 If the answer helped you, you can upvote it. – Danelaw

this solution does not retain a true "association" – Gnarly

How could it when it doesn't have the foreign key? – Danelaw

Useful answer! This article explains it more for Rails 6: dev.to/luchiago/… – Praemunire

Answer 2

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.

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.

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

Etz

Thanks @Dwight, I never would have thought of this! – Boogie

Can't get this to work for a belongs_to (where the parent has no id, so it has to be based on the multi-column PK). It just says that the relation is nil (and looking at the console, I can't see the lambda query ever being executed). – Doctrine

@Doctrine belongs_to has an option called :primary_key, that Specify the method that returns the primary key of associated object used for the association. By default this is id. I think this could help you. – Merl

@AhmedKamal I don't see how that is useful at all? – Doctrine

@dwight have you ever tested this solution? I mean, everything works like a charm, however after running expect(described_class.new).to(have_many(:tasks)) I get Task does not have a user_id foreign key error – Cradling

@Cradling I haven't tested it using shoulda, rather actual DB tests making sure I get the expected result. – Etz

That code seems to be missing a ')' – Phonation

Answer 3

Rails 5:

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

Though 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 it is no longer possible you can use @Arslan Ali solution as well.

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

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:

user = User.find(9001)
user.tasks

Update2:

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:

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

Lyso

so awesome, everywhere else i looked people kept trying to suggest using this outdated gem for composite keys – Galbanum

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 – Sadick

note: the lambda works when immediately following the has_many declaration, but not when following other parameters like :inverse_of, :primary_key, :foreign_key, etc. – Gnarly

although this retains the association, it causes other problems. from the docs: Note: Joining, eager loading and preloading of these associations is not fully possible. These operations happen before instance creation and the scope will be called with a nil argument. This can lead to unexpected behavior and is deprecated. api.rubyonrails.org/classes/ActiveRecord/Associations/… – Gnarly

Just to clarify, the lambda association is not deprecated, joining, eager loading and preloading is. One can allways keep 2 associations and rename this one to owned_tasks or something – Lyso

This looks promising but with Rails 5 ends up still using the foreign_key with a where query instead of just using the lambda above – Mumble

Yeah, it appears as though this no longer works in Rails 5. – Etz

Answer 4

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.

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.

Colonial

are you still using this method to solve your question? I am in the same boat and am wondering if you have learnt a new way or if this is still the best option. – Chloropicrin

Still the best option I've found. – Colonial

That is likely a remnant of old attempts. Edited the answer to remove it. – Colonial

Would this and dre-hh's answer below accomplish the same thing? – Darendaresay

OddityOverseer essential the answer is yes. Though I felt his answer was semantically wrong. Having to pass the user object into the scope on the User model seems like a backwards approach. So my version allows for User.find(1).tasks and dre-hh's answer requires something like User.find(1).tasks(User.find(1)). – Colonial

Agreed. I can't find a good way to do this through the association directly, so I think your method works the best for this. – Darendaresay

> Having to pass the user object into the scope on the User model seems like a backwards approach. You don't have to pass anything, this is a lambda and the current user instance is passed automatically to it – Lyso

@Lyso I didn't know it would just dynamically set a lambda like that. Cool! At this point all three of these answer offer a different approach to the same thing. I personally tend to use the scope via method definition vs the rails scope shorthand when a variable of any kind is required. That is preference though. – Colonial

Answer 5

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

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

Ratiocinate

Answer 6

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!

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:

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.

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

Cannell

Not sure this answer provides anything the approved answer didn't already. Moreover, it feels like an advertisement for another question. – Colonial

@Colonial Thank you. I will remove this answer if its bad to act like an ads. – Cannell

Answer 7

Better way is using polymorphic association:

task.rb

class Task < ActiveRecord::Base
  belongs_to :taskable, polymorphic: true
end

assigned_task.rb

class AssignedTask < Task
end

owned_task.rb

class OwnedTask < Task
end

user.rb

class User < ActiveRecord::Base
  has_many :assigned_tasks, as: :taskable, dependent: :destroy
  has_many :owned_tasks,    as: :taskable, dependent: :destroy
end

In result, we can use it so:

new_user = User.create(...)
AssignedTask.create(taskable: new_user, ...)
OwnedTask.create(taskable: new_user, ...)

pp user.assigned_tasks
pp user.owned_tasks

Subeditor

Answer 8

We came across a similar case where we need to have 2 foreign keys for belongs_to, and we fixed it as follows:

class User < ActiveRecord::Base
  has_many :tasks
end

class Task < ActiveRecord::Base
  belongs_to :user, ->(task) { where(assignee_id: task.assignee_id) }, foreign_key: :owner_id
end

Montford