# Habtm help with oracle?

**URL:** <https://www.ruby-forum.com/t/habtm-help-with-oracle/117559>\
**Category:** Rails\
**Created:** [October 18, 2007, 2:40am UTC](https://www.ruby-forum.com/t/habtm-help-with-oracle/117559 "2007-10-18T02:40:10Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![John\_Adams](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e47c2d/32.png) [@John\_Adams](https://www.ruby-forum.com/u/John_Adams)\
**Post date:** [October 18, 2007, 2:40am UTC](https://www.ruby-forum.com/t/habtm-help-with-oracle/117559/1 "2007-10-18T02:40:10Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![John\_Adams](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e47c2d/32.png) [@John\_Adams](https://www.ruby-forum.com/u/John_Adams)\
**Post date:** [October 18, 2007, 3:00am UTC](https://www.ruby-forum.com/t/habtm-help-with-oracle/117559/2 "2007-10-18T03:00:36Z")

</div>

Disregard. I’ve fixed this issue.

The finder method needs:

> > p = Product.find\_by\_product\_id(“291211”,:include=\>[:medias])

Otherwise it won’t load the underlying data.

Is this normal behavior? I thought that child records were auto-loaded.

-john