# Has\_many :foreign\_key association fails

**URL:** <https://www.ruby-forum.com/t/has-many-foreign-key-association-fails/117041>\
**Category:** Rails\
**Created:** [October 12, 2007, 11:53am UTC](https://www.ruby-forum.com/t/has-many-foreign-key-association-fails/117041 "2007-10-12T11:53:46Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![inriz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/50afbb/32.png) [@inriz](https://www.ruby-forum.com/u/inriz)\
**Post date:** [October 12, 2007, 11:53am UTC](https://www.ruby-forum.com/t/has-many-foreign-key-association-fails/117041/1 "2007-10-12T11:53:46Z")

</div>

hello list,

class Advertiser \< ActiveRecord::Base  
set\_table\_name “INSERENT”  
set\_primary\_key “INSNR”

has\_many :priv\_properties,  
:class\_name =\> “PrivProperty”,  
:foreign\_key =\> “MAKNR”  
end

produces wrong sql selelect.  
so the searched value is NULL

ins = Advertiser.find(100050)

select \* from objpriv where objpriv.maknr = NULL;  
it should be  
select \* from objpriv where objpriv.maknr = 100050;

so  
ins.quoted\_id gets NULL

does activerecord handle the id wrong?

should i add ticket on [dev.rubyonrails.org](http://dev.rubyonrails.org)?

best regards

---

<div class="post-metadata">

**Author:** ![inriz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/50afbb/32.png) [@inriz](https://www.ruby-forum.com/u/inriz)\
**Post date:** [October 12, 2007, 12:01pm UTC](https://www.ruby-forum.com/t/has-many-foreign-key-association-fails/117041/2 "2007-10-12T12:01:56Z")

</div>

module ActiveRecord  
module ConnectionAdapters # :nodoc:  
module Quoting  
# Quotes the column value to help prevent  
# {SQL injection attacks}[[Wikipedia, the free encyclopedia](http://en.wikipedia.org/wiki/)  
SQL\_injection].  
def quote(value, column = nil)  
# records are quoted as their primary key  
return value.quoted\_id if value.respond\_to?(:quoted\_id)

```
    case value
      when String, ActiveSupport::Multibyte::Chars
        value = value.to_s
        if column && column.type == :binary &&

```

column.class.respond\_to?(:string\_to\_binary)

“#{quoted\_string\_prefix}‘#{quote\_string(column.class.string\_to\_binary(value))}’”

# ’ (for ruby-mode)

```
        elsif column && [:integer, :float].include?(column.type)
          value = column.type == :integer ? value.to_i :

```

value.to\_f  
value.to\_s  
else  
“#{quoted\_string\_prefix}‘#{quote\_string(value)}’” #  
’ (for ruby-mode)  
end  
when NilClass then “NULL”  
when TrueClass then (column && column.type  
== :integer ? ‘1’ : quoted\_true)  
when FalseClass then (column && column.type  
== :integer ? ‘0’ : quoted\_false)  
when Float, Fixnum, Bignum then value.to\_s  
# BigDecimals need to be output in a non-normalized form and  
quoted.  
when BigDecimal then value.to\_s(‘F’)  
else  
if value.acts\_like?(:date) || value.acts\_like?(:time)  
“‘#{quoted\_date(value)}’”  
else

“#{quoted\_string\_prefix}‘#{quote\_string(value.to\_yaml)}’”  
end  
end  
end  
…

so i saw the method quote and the case select which returns “NULL” if  
value is a NilClass  
but why its NilClass?

hmm

---

<div class="post-metadata">

**Author:** ![inriz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/50afbb/32.png) [@inriz](https://www.ruby-forum.com/u/inriz)\
**Post date:** [October 12, 2007, 12:22pm UTC](https://www.ruby-forum.com/t/has-many-foreign-key-association-fails/117041/3 "2007-10-12T12:22:25Z")

</div>

On 12 Oct 2007, at 10:53, inriz wrote:

> end
> 
> produces wrong sql selelect.  
> so the searched value is NULL
> 
> ins = Advertiser.find(100050)
> 
> select \* from objpriv where objpriv.maknr = NULL;  
> it should be  
> select \* from objpriv where objpriv.maknr = 100050;

Given what you’ve told AR, I would expect the sql generated to be  
select \* from INSERENT where INSNR = 100050; since you’ve asked AR to  
find you the instance of Advertiser with primary key value 100050 and  
you’ve told it that the table name is INSERENT and the primary key INSNR

Are you sure you’ve pasted the right bit of sql?

Fred

---

<div class="post-metadata">

**Author:** ![inriz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/50afbb/32.png) [@inriz](https://www.ruby-forum.com/u/inriz)\
**Post date:** [October 12, 2007, 1:10pm UTC](https://www.ruby-forum.com/t/has-many-foreign-key-association-fails/117041/4 "2007-10-12T13:10:58Z")

</div>

On 12 Oct 2007, at 11:20, inriz wrote:

> ```
> :class_name => "PrivProperty",
> :foreign_key => "MAKNR"
> 
> ```
> 
> end
> 
> so set\_table\_name and set\_primary\_key must have a :hash? in my book,  
> agile webdevelopment from dhh, he wrote a normal string?  
> Strings should work absolutely fine. Not sure why you’re talking  
> about hashes.

> i wonder, why the its a floating point number.  
> basicly the behavior is rigth 😉 but this, floating point number seam  
> to be scary?

I can’t guess without seeing the code calling this.

Fred

---

<div class="post-metadata">

**Author:** ![inriz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/50afbb/32.png) [@inriz](https://www.ruby-forum.com/u/inriz)\
**Post date:** [October 12, 2007, 12:31pm UTC](https://www.ruby-forum.com/t/has-many-foreign-key-association-fails/117041/5 "2007-10-12T12:31:12Z")

</div>

dear frederick,

im a step forward. so i changed the my model see below:

class Advertiser \< ActiveRecord::Base  
set\_table\_name :inserent  
set\_primary\_key :insnr

has\_many :priv\_properties,  
:class\_name =\> “PrivProperty”,  
:foreign\_key =\> “MAKNR”  
end

so set\_table\_name and set\_primary\_key must have a :hash? in my book,  
agile webdevelopment from dhh, he wrote a normal string?  
but it works if i call ins.id  
there is a none nil result 😉  
so columns\_hash expected a string an the code converts a hash? to\_s ?

def column\_for\_attribute(name)  
self.class.columns\_hash[name.to\_s]  
end

now im quite happy but the association always doesnt work.

so AR creates the following sql

SELECT \* FROM OBJPRIV WHERE (OBJPRIV.MAKNR = 100050.0)

i wonder, why the its a floating point number.  
basicly the behavior is rigth 😉 but this, floating point number seam  
to be scary?

many thanks

---

<div class="post-metadata">

**Author:** ![inriz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/50afbb/32.png) [@inriz](https://www.ruby-forum.com/u/inriz)\
**Post date:** [October 12, 2007, 3:17pm UTC](https://www.ruby-forum.com/t/has-many-foreign-key-association-fails/117041/6 "2007-10-12T15:17:29Z")

</div>

On 12 Oct 2007, at 13:50, inriz wrote:

> so ins.id returns the bigdecimal and the sql statement will be filled  
> with the right value in the condition area  
> the only thing i changed, was the options under set\_primary\_key from  
> string “INSNR” to a hash :insnr  
> (is it the right spelling?)

You mean symbol, not hash. I’ve no idea why that would make a difference

> between 100050 100050.0  
> for an integer column its no difference but it isnt proper for my  
> eyes 😉

> so for the current integration of the legacy tables, the bigdecimal  
> column should be a clear integer …  
> ins.id.to\_i returns the right value for the condition
> 
> additionally, if i scaffold the INSERENT table the ids also are  
> floating point numbers  
> why?

What are the types of the database columns. If you’ve actually  
declared them as big decimals what rails is doing seems reasonable,  
if not what you want.

Fred

---

<div class="post-metadata">

**Author:** ![inriz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/50afbb/32.png) [@inriz](https://www.ruby-forum.com/u/inriz)\
**Post date:** [October 12, 2007, 2:54pm UTC](https://www.ruby-forum.com/t/has-many-foreign-key-association-fails/117041/7 "2007-10-12T14:54:35Z")

</div>

ok lets speak some code snippet

##################

# version 1 start

class Advertiser \< ActiveRecord::Base  
set\_table\_name “INSERENT”  
set\_primary\_key “INSNR”

has\_many :priv\_properties,  
:class\_name =\> “PrivProperty”,  
:foreign\_key =\> “MAKNR”  
end

ruby script/console:

> > ins = Advertiser.find(100050)  
> > … display resultset
> 
> > ins.id  
> > =\> nil
> 
> > ins.priv\_properties  
> > =\> []

ins.priv\_properties produces the follow sql statement  
SELECT \* FROM OBJPRIV WHERE (OBJPRIV.MAKNR = NULL)

so ins.id is a NilClass and the quote method returns NULL

# ende

##################

##################

# version 2 start

class Advertiser \< ActiveRecord::Base  
set\_table\_name “INSERENT”  
set\_primary\_key :insnr

has\_many :priv\_properties,  
:class\_name =\> “PrivProperty”,  
:foreign\_key =\> “MAKNR”  
end

ruby script/console:

> > ins = Advertiser.find(100050)  
> > … display resultset
> 
> > ins.id  
> > =\> #BigDecimal:3fd2c8c,‘0.10005E6’,8(12)
> 
> > ins.priv\_properties  
> > =\> []

ins.priv\_properties produces the follow sql statement  
SELECT \* FROM OBJPRIV WHERE (OBJPRIV.MAKNR = 100050.0)

so ins.id returns the bigdecimal and the sql statement will be filled  
with the right value in the condition area  
the only thing i changed, was the options under set\_primary\_key from  
string “INSNR” to a hash :insnr  
(is it the right spelling?)

# ende

##################

the next problem is the bigdecimal typed id  
standard rails created database schematas will return only the value  
of id without any kind of  
type information and so on

in this case, the id is mapped to “INSNR” of my legacy table and that  
produces =\> #BigDecimal:3fd2c8c,‘0.10005E6’,8(12)  
its okay, but the foreign\_key is a char(10) so its a difference  
between 100050 100050.0  
for an integer column its no difference but it isnt proper for my  
eyes 😉

so for the current integration of the legacy tables, the bigdecimal  
column should be a clear integer …  
ins.id.to\_i returns the right value for the condition

additionally, if i scaffold the INSERENT table the ids also are  
floating point numbers  
why?

many thanks to you

best regards

---

<div class="post-metadata">

**Author:** ![inriz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/50afbb/32.png) [@inriz](https://www.ruby-forum.com/u/inriz)\
**Post date:** [October 12, 2007, 3:28pm UTC](https://www.ruby-forum.com/t/has-many-foreign-key-association-fails/117041/8 "2007-10-12T15:28:38Z")

</div>

On 12 Okt., 15:14, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> > its okay, but the foreign\_key is a char(10) so its a difference  
> > why?
> 
> What are the types of the database columns. If you’ve actually  
> declared them as big decimals what rails is doing seems reasonable,  
> if not what you want.
> 
> Fred

yes, symbol was the right name 😉  
the column maknr is a char(10) in the table objpriv  
fortunately, our databases, firebird, support sql statements  
like select \* from objpriv where maknr = 100050 without apostrophe  
of a char(10)  
select \* from objpriv where maknr = 100050.0

on an integer column 100050 and 100050.0 are same  
but on a char column its not the same  
so thats my problem

---

<div class="post-metadata">

**Author:** ![inriz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/50afbb/32.png) [@inriz](https://www.ruby-forum.com/u/inriz)\
**Post date:** [October 12, 2007, 5:11pm UTC](https://www.ruby-forum.com/t/has-many-foreign-key-association-fails/117041/9 "2007-10-12T17:11:54Z")

</div>

so another breakpoint

the database, were using firebird, has to types  
integer and bigint

the ruby firebird adapter uses bigint and bigint is mapped  
via bigdecimal

so the behavior, using bigdecimal is defined by the adapter  
and the floating point number is right

there are many things to get involved  
the fireruby adapter is old and have many bugs with firebird 2.0