From: Philip Nelson <teamdba@...>
Date: 2006-09-25T21:40:18+09:00
Subject: RoR : Linking tables without ID column

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
-~----------~----~----~----~------~----~------~--~---