Specifying the foreign key in a has_many :through relationship

Question

I have the following three models: User, Project, and Assignment.

A User has_many Projects through an assignment. However, Assignment actually has two foreign keys that relate to a User: user_id (representing the user who was assigned the project) and completer_id (representing the user who completed the project).

Often, user_id and completer_id will be the same (if the user who was assigned the project completes it). However, if another user completes it, user_id and completer_id will be different.

In my User model, I have the following:

class User < ActiveRecord::Base
  has_many   :assignments
  has_many   :incomplete_assignments, :class_name => 'Assignment',
    :conditions  => 'completer_id IS NULL'
  has_many   :completed_assignments, :class_name => 'Assignment',
    :foreign_key => 'completer_id'

  # this is the important one
  has_many   :incomplete_projects,
    :through     => :assignments,
    :source      => :project,
    :conditions  => 'completer_id IS NULL'
end

I would like to make another association, called :completed_projects, which uses completer_id as the foreign key for the User in the :through model, rather than :user_id. Is it possible to do this?

And, as an aside, I am aware of the :foreign_key option. However, this option is ignored when using :through, so I'd like to know if there's a way to do this without it.

Finally, I should mention that I'm open to other designs, if it can't be done this way and someone can think of a better way.

Answer 1

You can always use SQL for the selection if ActiveRecord can't easily express the relationship out of the box:

has_many :completed_projects,
:class_name => "Project",
:finder_sql => 'SELECT p.* FROM projects p ' +
  'INNER JOIN assignments a ON p.id=a.project_id ' +
  'INNER JOIN users u on u.id=a.completer_id ' +
  'WHERE u.id=#{id}'