I have two tables that I am trying to join with a habtm model through
a join table, and I’m seeing invalid SQL being generated (at least,
that’s what I see in the developer log!) Any help you can offer me
would be appreciated. Please help! 
This is on OCI8, Ruby 1.8.6 and rails 1.2 against Oracle.
I know my database is working, because this works:
Media.find_by_media_id(“m588318”)
=> #<Media:0xb76192b8 @attributes={“media_id”=>“m588318”,
“url”=>“291211.jpg”}>
Product.find_by_product_id(“291211”)
=> #<Product:0xb7606988 @attributes={“end_date”=>nil,
“start_date”=>#<DateTime: 2454350,0,2299161>, “product_type”=>7,
“creation_date”=>#<DateTime: 21205586171/8640,0,2299161>,
“product_id”=>“291211”,
(blah blah blah, more info about product here…)
Why does this fail, then? There’s a record in the DCS_PRD_MEDIA table
that maps product ID m588318 to product id 29211, but it doesn’t map
right…
Product.find_by_product_id(“291211”).medias
=> []
Developer.log (note the where large_image_id = null! that shouldn’t
happen!)
Media Load (0.001385) SELECT * FROM DCS_MEDIA_EXT INNER JOIN
DCS_PRD_MEDIA ON DCS_MEDIA_EXT.MEDIA_ID =
DCS_PRD_MEDIA.LARGE_IMAGE_ID WHERE (DCS_PRD_MEDIA.LARGE_IMAGE_ID =
NULL )
Model Files:
class Product < ActiveRecord::Base
set_table_name “DCS_PRODUCT”
set_primary_key “PRODUCT_ID”
# assoc forigen key is in join table to find other object!
has_and_belongs_to_many :medias, :join_table=>“DCS_PRD_MEDIA”, :foreign_
key => “LARGE_IMAGE_ID”, :association_foreign_key => “LARGE_IMAGE_ID”
end
class Media < ActiveRecord::Base
set_table_name “DCS_MEDIA_EXT”
set_primary_key “MEDIA_ID”
has_and_belongs_to_many :products, :join_table=>“DCS_PRD_MEDIA”, :foreig
n_key => “LARGE_IMAGE_ID”, :association_foreign_key => “PRODUCT_ID”
end
Table Schema from (Oracle SQLPlus):
SQL> desc dcs_media;
Name Null? Type
MEDIA_ID NOT NULL VARCHAR2(40)
VERSION NOT NULL NUMBER(38)
CREATION_DATE DATE
START_DATE DATE
END_DATE DATE
DESCRIPTION VARCHAR2(254)
NAME NOT NULL VARCHAR2(254)
PATH NOT NULL VARCHAR2(254)
PARENT_FOLDER_ID NOT NULL VARCHAR2(40)
MEDIA_TYPE NUMBER(38)
SQL> desc dcs_prd_media;
Name Null? Type
PRODUCT_ID NOT NULL VARCHAR2(40)
TEMPLATE_ID VARCHAR2(40)
THUMBNAIL_IMAGE_ID VARCHAR2(40)
SMALL_IMAGE_ID VARCHAR2(40)
LARGE_IMAGE_ID VARCHAR2(40)
SQL> desc dcs_product
Name Null? Type
PRODUCT_ID NOT NULL VARCHAR2(40)
VERSION NOT NULL NUMBER(38)
CREATION_DATE DATE
START_DATE DATE
END_DATE DATE
DISPLAY_NAME VARCHAR2(254)
DESCRIPTION VARCHAR2(254)
LONG_DESCRIPTION CLOB
PARENT_CAT_ID VARCHAR2(40)
PRODUCT_TYPE NUMBER(38)
ADMIN_DISPLAY VARCHAR2(254)