27

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.

Garfield
  • 1,247
  • 4
  • 15
  • 33
  • 1
    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. – Jed Schneider Jul 23 '10 at 11:35
  • Thanks Jed. But I do not want coach_id there for some reason. – Garfield Jul 23 '10 at 11:40

3 Answers3

55

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

John Topley
  • 113,588
  • 46
  • 195
  • 237
10

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.

Marcel Jackwerth
  • 53,948
  • 9
  • 74
  • 88
-6

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
alex.zherdev
  • 23,914
  • 8
  • 62
  • 56