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
URIs, Addressability, and the use of HTTP GET and POST)
 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:in execute’
/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:in select_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:in find_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:in find’
app/controllers/airport_controller.rb:19:in list' app/controllers/airport_controller.rb:10:in index’
=============== END OF CUT ================================