Something wrong in has_many :through

Rails
1

I have a ‘classic’ situation for using has_many :through, but falling
into a problems with it…

I have 3 tables: job_history, job_history_industry and industry; the
associations between them are as following:

class JobHistory < ActiveRecord::Base
set_table_name “job_history”
set_primary_key “_key”

has_many :job_history_industry,
:class_name => “JobHistoryIndustry”,
:foreign_key => “job_history_key”

has_many :industry, :through => :job_history_industry,
end

class JobHistoryIndustry < ActiveRecord::Base
set_table_name “job_history_industry”
set_primary_key “job_history_key, industry_key”

belongs_to :job_history,
:class_name => “JobHistory”,
:foreign_key => “_key”

belongs_to :industry,
:class_name => “Industry”,
:foreign_key => “_key”
end

class Industry < ActiveRecord::Base
set_table_name “industry”
set_primary_key “_key”

has_many :job_history_industry,
:class_name => “JobHistoryIndustry”,
:foreign_key => “industry_key”
end

So I need to get industry for job_history through job_history_industry.
I create the correct job_history object j_hist;
but when I try to write test = j_hist.industry it gives me the following
error:

ActiveRecord::StatementInvalid in View personController#index

Mysql::Error: #42S22Unknown column ‘job_history_industry._key’ in ‘on
clause’: SELECT industry.* FROM industry INNER JOIN
job_history_industry ON industry._key = job_history_industry._key WHERE
(job_history_industry.job_history_key = 228)

Of course, it’s incorrect mysql; there is no such column
‘job_history_industry._key’; instead of ‘job_history_industry._key’
there should be ‘job_history_industry.industry_key’

So what I’m doing wrong and how to correct this?

2

Ok, I found the problem. The answer is that foreign key should be into
the join table model, not in the associated table model; so correct
models for my situation are:
class JobHistory < ActiveRecord::Base
set_table_name “job_history”
set_primary_key “_key”

has_many :job_history_industry,
:class_name => “JobHistoryIndustry”,
:foreign_key => “job_history_key”

has_many :industry, :through => :job_history_industry,
end

class JobHistoryIndustry < ActiveRecord::Base
set_table_name “job_history_industry”

set_primary_key “job_history_key, industry_key”

belongs_to :job_history,
:class_name => “JobHistory”,

belongs_to :industry,
:class_name => “Industry”,
:foreign_key => “industry_key”
end

class Industry < ActiveRecord::Base
set_table_name “industry”
set_primary_key “_key”

has_many :job_history_industry,
:class_name => “JobHistoryIndustry”,
:foreign_key => “industry_key”
end

Dmitry H. wrote:

I have a ‘classic’ situation for using has_many :through, but falling
into a problems with it…

I have 3 tables: job_history, job_history_industry and industry; the
associations between them are as following:

class JobHistory < ActiveRecord::Base
set_table_name “job_history”
set_primary_key “_key”

has_many :job_history_industry,
:class_name => “JobHistoryIndustry”,
:foreign_key => “job_history_key”

has_many :industry, :through => :job_history_industry,
end

class JobHistoryIndustry < ActiveRecord::Base
set_table_name “job_history_industry”
set_primary_key “job_history_key, industry_key”

belongs_to :job_history,
:class_name => “JobHistory”,
:foreign_key => “_key”

belongs_to :industry,
:class_name => “Industry”,
:foreign_key => “_key”
end

class Industry < ActiveRecord::Base
set_table_name “industry”
set_primary_key “_key”

has_many :job_history_industry,
:class_name => “JobHistoryIndustry”,
:foreign_key => “industry_key”
end

So I need to get industry for job_history through job_history_industry.
I create the correct job_history object j_hist;
but when I try to write test = j_hist.industry it gives me the following
error:

ActiveRecord::StatementInvalid in View personController#index

Mysql::Error: #42S22Unknown column ‘job_history_industry._key’ in ‘on
clause’: SELECT industry.* FROM industry INNER JOIN
job_history_industry ON industry._key = job_history_industry._key WHERE
(job_history_industry.job_history_key = 228)

Of course, it’s incorrect mysql; there is no such column
‘job_history_industry._key’; instead of ‘job_history_industry._key’
there should be ‘job_history_industry.industry_key’

So what I’m doing wrong and how to correct this?