Specifying a foreign key in has_many: through relationships

Question

I have three models: User, Project and Assignment.

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

Often user_id and completer_id will be the same (if the user to whom the project was assigned completes it). However, if another user completes it, user_id and completeter_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 create another association called :completed_projects , which uses completer_id as the foreign key for the user in the :through model, and not :user_id . Can this be done?

And, aside, I know the option :foreign_key . However, this parameter is ignored when using :through , so I would like to know if there is a way to do this without it.

Finally, I must mention that I am open to other projects if it is not possible to do this in such a way, and someone might think of a better way.

Answer 1

You can always use SQL to choose if ActiveRecord cannot easily express relationships 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}' 

Answer 2

Do you have other metadata in the assignment table?

If I would not use habtm and instead add the component_id table to the project table, it makes sense to live there.

If you need / need to use has_many: you can look at using named_scopes (allow the Rails version) so you can tell user.projects.completed and user.projects.incomplete