Linking tables with non-ID primary key / foreign key pair

Folks,

I need to link two tables together which don’t conform to the standard
Rails
conventions (it doesn’t have an ID column as the primary key / foreign
key
column). I’ve got everything working for the COUNTRIES table (primary
key
COUNTRY_CODE), but can’t get the AIRPORTS table LIST function working
(see
error at bottom).

Help please.

Phil

Here are the DB definitions (for migration) -

============== START OF DB DEFNS ===============================
class CreateCountries < ActiveRecord::Migration
def self.up
create_table(:countries, :id => false, :options => ’ IN SFPS001’) do
|t|
# t.column :name, :string
t.column :country_code, :string, :null => false, :limit => 2
t.column :country_name, :string, :null => false, :limit => 50
end
end

def self.down
drop_table :countries
end
end

class CreateAirports < ActiveRecord::Migration
def self.up
create_table(:airports, :options => ‘IN SFPS002’) do |t|
# t.column :name, :string
t.column :icao_code , :string, :null => false, :limit => 4
t.column :iata_code , :string, :null => false, :limit => 3
t.column :town , :string, :null => false, :limit => 100
t.column :state , :string, :limit => 100
t.column :airport_name, :string, :limit => 100
t.column :country_code, :string, :limit => 2
t.column :latitude , :float
t.column :longitude , :float
end

execute "alter table dbfps001.airports
         add constraint r002001a foreign key
         (country_code)
         references dbfps001.countries
         (country_code)
         on delete restrict"

end

def self.down
drop_table :airports
end
end
==================== END OF DB DEFNS

Here are the models I’ve defined -

==================== START OF MODELS

class Country < ActiveRecord::Base

primary_key :country_code
has_many :trip_leg_passengers, :as => :passport_country
has_many :airports, :as => :country_code

validates_presence_of :country_code, :country_name
validates_uniqueness_of :country_code
validates_uniqueness_of :country_name
end

class Airport < ActiveRecord::Base

has_many :trip_legs, :as => :start_airport
has_many :trip_legs, :as => :end_airport
belongs_to :country, :foreign_key => :country_code

validates_presence_of :icao_code, :iata_code, :town
validates_uniqueness_of :icao_code
validates_uniqueness_of :iata_code
end
=================== END OF MODELS ================================

And here is the controller for Airports -

================== START OF CONTROLLERS ==========================
class AirportController < ApplicationController

Enforce login

before_filter :authorize, :except => :login

layout “admin”

def index
list
render :action => ‘list’
end

GETs should be safe (see

http://www.w3.org/2001/tag/doc/whenToUseGet.html)
verify :method => :post, :only => [ :destroy, :create, :update ],
:redirect_to => { :action => :list }

def list
@airports = Airport.find(:all, :include => :country)

end

def show
@airport = Airport.find(params[:id])
end

def new
@airport = Airport.new
end

def create
@airport = Airport.new(params[:airport])
if @airport.save
flash[:notice] = ‘Airport was successfully created.’
redirect_to :action => ‘list’
else
render :action => ‘new’
end
end

def edit
@airport = Airport.find(params[:id], :include => :country)
end

def update
@airport = Airport.find(params[:id], :include => :country)
if @airport.update_attributes(params[:airport])
flash[:notice] = ‘Airport was successfully updated.’
redirect_to :action => ‘show’, :id => @airport
else
render :action => ‘edit’
end
end

def destroy
Airport.find(params[:id]).destroy
redirect_to :action => ‘list’
end
end

================== END OF CONTROLLERS ============================

When I try to list the airport records I get -

============== START OF CUT =============================
ActiveRecord::StatementInvalid: [IBM][CLI Driver][DB2/LINUX] SQL0206N
“COUNTRIES.ID” is not valid in the context where it is used.
SQLSTATE=42703
SQLCODE=-206: SELECT airports.id AS t0_r0, airports.icao_code AS t0_r1,
airports.iata_code AS t0_r2, airports.town AS t0_r3, airports.state AS
t0_r4,
airports.airport_name AS t0_r5, airports.country_code AS t0_r6,
airports.latitude AS t0_r7, airports.longitude AS t0_r8, countries.id AS
t1_r0, countries.country_code AS t1_r1, countries.country_name AS t1_r2
FROM
airports LEFT OUTER JOIN countries ON countries.id =
airports.country_code
=============== END OF CUT ==============================

In other words it is trying to join looking for an ID column in
COUNTRIES.

Here’s the trace -

=============== START OF CUT ============================
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/connection_adapters/abstract_adapter.rb:120:in
log' /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/connection_adapters/ibm_db2_adapter.rb:517:inexecute’
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/connection_adapters/ibm_db2_adapter.rb:466:in
select_all' /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/associations.rb:1110:inselect_all_rows’
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/associations.rb:975:in
find_with_associations' /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/associations.rb:973:infind_with_associations’
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:923:in
find_every' /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:381:infind’
app/controllers/airport_controller.rb:19:in list' app/controllers/airport_controller.rb:10:inindex’
=============== END OF CUT ================================

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs