Rails - Sep 2006 - 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: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 ===============================

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---