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 / googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe / googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---