Rails: Non id foreign key lookup ActiveRecord

Question

I want ActiveRecord to lookup by a non-id column from a table. Hope this is clear when I give you my code sample.

class CoachClass < ActiveRecord::Base
  belongs_to :coach
end

class Coach < ActiveRecord::Base
    has_many :coach_classes, :foreign_key => 'user_name'
end

When I do a coach_obj.coach_classes, this rightly triggers

SELECT * FROM `coach_classes` WHERE (`coach_classes`.user_name = 2)

(2 being the that coach's id here which is my problem.)

I want it to trigger

SELECT * FROM `coach_classes` WHERE (`coach_classes`.user_name = 'David')

('David' being the that coach's user_name)

user_name is unique and present in both tables.

I do not want to have a coach_id in my coach_classes table for some reason.

Fisken

unsolicited advice: you may want to consider adding a coach_id to the table, it will make it easier if this relationship becomes nested and more conventional. – Mesothorium

Thanks Jed. But I do not want coach_id there for some reason. – Fisken

Answer 1

I think you need to specify the primary key options on the associations as well:

class CoachClass < ActiveRecord::Base 
  belongs_to :coach, :foreign_key => 'user_name', :primary_key => 'user_name'
end

class Coach < ActiveRecord::Base
  has_many :coach_classes, :foreign_key => 'user_name', :primary_key => 'user_name'
end 

This specifies the method that returns the primary key of the associated object (defaulting to id).

Ingather

John, are you sure the other side of the association has an effect here? One could simply have has_many, without a belongs_to on the other side. – Unwarranted

I have scoured the internet for this answer. Thank you! – Insightful

Answer 2

There is a option called primary_key which is per default set to :id. You want to use:

has_many :coach_classes, :foreign_key => :user_name, :primary_key => :user_name

Also use these options on the belongs_to association.

Read more in the documentation.

Acea

Answer 3

You need to use finder_sql:

class Coach < ActiveRecord::Base
    has_many :coach_classes, :finder_sql => 'SELECT * FROM `coach_classes` WHERE (`coach_classes`.user_name = "#{user_name}")'
end

Unwarranted

Note - they took this away in later versions. Really a lifesaver when their 'magic' doesn't have the functionality you need. – Dustcloth

Definitely do not do this... ever. – Castrato