Taisuke Yamada
2005-Apr-15 12:25 UTC
Re: ActiveRecord bug with has_and_belongs_to_many, using association table with primary key "id"
Oh, and yes, I do have a one-line fix of this issue (attached), but not so sure if this is a correct way to fix it. I still haven''t grapsed how AR works internally...> I''m having wierd problem with AR, and not sure if it''s a feature (limitation) > or a bug (or me doing something wrong). However, I can reproduce it with > attached script, so someone with more insight might help me... > > Here''s a problem. I''m using association table to represent directed > graph to manage dependency tree. With this data structure, I have > two objects: node and link. And it will result to a tree something > like below: > > node0 ---+---[link0]---> node1 > +---[link1]---> node2 > +---[link2]---> node3 ---[link3]---> node4 > > Now, with AR, this can be expressed as > > - "node" having "has_many" relation with "link" > - "link" having "belongs_to" relation with "node" > - "node" having "has_and_belongs_to_many" relation with other "node" > > However, when I use "id" as a primary key of "link" table, AR fails to > track "has_and_belongs_to_many" relation. I do get "node" objects > through accessing this relation, but returned nodes'' "node.id" attribute > is messed up. AR seems to have filled it up with "id" value of connecting > "link" object. > > When I change primary key of "link" table from "id" to anything else, > this problem goes away. > > Is this a bug? Or is it me doing something wrong? > > I have attached script to reproduce this issue. > I''ve verified this with latest activerecord-1.9.1, installed by rubygems. > > Thanks in advance. > -- > Taisuke Yamada > > > > ------------------------------------------------------------------------ > > #!/bin/sh > > rm -f test.db > > ###################################################################### > sqlite3 test.db <<EOF > --- digraph nodes > CREATE TABLE nodes( > id INTEGER PRIMARY KEY NOT NULL, > name TEXT > ); > INSERT INTO nodes VALUES (1, ''foo''); > INSERT INTO nodes VALUES (2, ''bar''); > > --- digraph > CREATE TABLE links( > id INTEGER PRIMARY KEY NOT NULL, > source INT, > target INT > ); > INSERT INTO links VALUES (1, 1, 2); > EOF > > ###################################################################### > ruby <<EOF > require ''test/unit'' > require ''rubygems'' > require_gem ''activerecord'' > > class Link < ActiveRecord::Base > belongs_to :target, :class_name => "Node", :foreign_key => "target" > end > > class Node < ActiveRecord::Base > has_many :target_links, :class_name => "Link", :foreign_key => "source" > > has_and_belongs_to_many :target_nodes, > :class_name => "Node", :foreign_key => "source", > :join_table => "links", :association_foreign_key => "target" > end > > class TC_TEST < Test::Unit::TestCase > def test_main > ActiveRecord::Base.establish_connection(:adapter => ''sqlite3'', > :dbfile => ''test.db'') > src = Node.find(1) > dst = Node.find(2) > > ## get linked node by tracking each link > tmp = src.target_links[0].target > > ## will pass > assert_equal(dst.id, tmp.id, > "Target node should be at the other end of the link") > > ## get linked node directly using association table > tmp = src.target_nodes[0] > > ## will pass - so seems OK, but... > assert_equal(dst.name, tmp.name, > "Target node should be at the other end of the link") > > ## will NOT pass - id is messed up > assert_equal(dst.id, tmp.id, > "Target node should be at the other end of the link") > end > end > EOF > > exit 0_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Chris McGrath
2005-Apr-15 13:50 UTC
Re: Re: ActiveRecord bug with has_and_belongs_to_many, using association table with primary key "id"
You shouldn''t use id as the primary key field for a habtm association. This is mentioned in the ActiveRecord docs [1]. If you''ve got tree-like data, have a look at acts_as_tree [2] HTH, Chris [1] http://ar.rubyonrails.com/classes/ActiveRecord/Associations/ClassMethods.html#M000012 [2] http://ar.rubyonrails.org/classes/ActiveRecord/Acts/Tree/ClassMethods.html On 4/15/05, Taisuke Yamada <tyamadajp-PVDt0BYFe5m597m9RUd4JNi2O/JbrIOy@public.gmane.org> wrote:> Oh, and yes, I do have a one-line fix of this issue (attached), > but not so sure if this is a correct way to fix it. I still > haven''t grapsed how AR works internally... > > > I''m having wierd problem with AR, and not sure if it''s a feature (limitation) > > or a bug (or me doing something wrong). However, I can reproduce it with > > attached script, so someone with more insight might help me... > > > > Here''s a problem. I''m using association table to represent directed > > graph to manage dependency tree. With this data structure, I have > > two objects: node and link. And it will result to a tree something > > like below: > > > > node0 ---+---[link0]---> node1 > > +---[link1]---> node2 > > +---[link2]---> node3 ---[link3]---> node4 > > > > Now, with AR, this can be expressed as > > > > - "node" having "has_many" relation with "link" > > - "link" having "belongs_to" relation with "node" > > - "node" having "has_and_belongs_to_many" relation with other "node" > > > > However, when I use "id" as a primary key of "link" table, AR fails to > > track "has_and_belongs_to_many" relation. I do get "node" objects > > through accessing this relation, but returned nodes'' "node.id" attribute > > is messed up. AR seems to have filled it up with "id" value of connecting > > "link" object. > > > > When I change primary key of "link" table from "id" to anything else, > > this problem goes away. > > > > Is this a bug? Or is it me doing something wrong? > > > > I have attached script to reproduce this issue. > > I''ve verified this with latest activerecord-1.9.1, installed by rubygems. > > > > Thanks in advance. > > -- > > Taisuke Yamada > > > > > > > > ------------------------------------------------------------------------ > > > > #!/bin/sh > > > > rm -f test.db > > > > ###################################################################### > > sqlite3 test.db <<EOF > > --- digraph nodes > > CREATE TABLE nodes( > > id INTEGER PRIMARY KEY NOT NULL, > > name TEXT > > ); > > INSERT INTO nodes VALUES (1, ''foo''); > > INSERT INTO nodes VALUES (2, ''bar''); > > > > --- digraph > > CREATE TABLE links( > > id INTEGER PRIMARY KEY NOT NULL, > > source INT, > > target INT > > ); > > INSERT INTO links VALUES (1, 1, 2); > > EOF > > > > ###################################################################### > > ruby <<EOF > > require ''test/unit'' > > require ''rubygems'' > > require_gem ''activerecord'' > > > > class Link < ActiveRecord::Base > > belongs_to :target, :class_name => "Node", :foreign_key => "target" > > end > > > > class Node < ActiveRecord::Base > > has_many :target_links, :class_name => "Link", :foreign_key => "source" > > > > has_and_belongs_to_many :target_nodes, > > :class_name => "Node", :foreign_key => "source", > > :join_table => "links", :association_foreign_key => "target" > > end > > > > class TC_TEST < Test::Unit::TestCase > > def test_main > > ActiveRecord::Base.establish_connection(:adapter => ''sqlite3'', > > :dbfile => ''test.db'') > > src = Node.find(1) > > dst = Node.find(2) > > > > ## get linked node by tracking each link > > tmp = src.target_links[0].target > > > > ## will pass > > assert_equal(dst.id, tmp.id, > > "Target node should be at the other end of the link") > > > > ## get linked node directly using association table > > tmp = src.target_nodes[0] > > > > ## will pass - so seems OK, but... > > assert_equal(dst.name, tmp.name, > > "Target node should be at the other end of the link") > > > > ## will NOT pass - id is messed up > > assert_equal(dst.id, tmp.id, > > "Target node should be at the other end of the link") > > end > > end > > EOF > > > > exit 0 > > > --- has_and_belongs_to_many_association.rb.orig 2005-04-15 21:18:06.000000000 +0900 > +++ has_and_belongs_to_many_association.rb 2005-04-15 21:18:42.000000000 +0900 > @@ -147,7 +147,7 @@ > def construct_sql > interpolate_sql_options!(@options, :finder_sql, :delete_sql) > @finder_sql = @options[:finder_sql] || > - "SELECT t.*, j.* FROM #{@join_table} j, #{@association_table_name} t " + > + "SELECT j.*, t.* FROM #{@join_table} j, #{@association_table_name} t " + > "WHERE t.#{@association_class.primary_key} = j.#{@association_foreign_key} AND " + > "j.#{@association_class_primary_key_name} = #{@owner.quoted_id} " + > (@options[:conditions] ? " AND " + interpolate_sql(@options[:conditions]) : "") + " " + > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > >
Taisuke Yamada
2005-Apr-16 01:23 UTC
limitation of act_as_tree (was Re: ActiveRecord bug with has_and_belongs_to_many, using association table with primary key "id")
Thanks for a reply.
It was written so clearly, there was no way I could have dropped it (shame,
shame).
But it seems acts_as_tree is too limited to handle tree-like data.
=== Limitations of "acts_as_tree" ==1. Both node and link must be on
the same table
a. This means link itself is cannot be first-class data
2. There can be only one referring parent node
a. You cannot define N-to-N relation (many nodes pointing to one node,
and one node point to multiple nodes)
Am I correct with this understanding?
Since I need to manage multiple digraph relation (= need to manage graph
relation in separate tables), with relation itself holding different
attributes (= link must be an object itself), I guess I need to do some
coding myself...
> You shouldn''t use id as the primary key field for a habtm
association.
> This is mentioned in the ActiveRecord docs [1].
>
> If you''ve got tree-like data, have a look at acts_as_tree [2]
>
> HTH,
>
> Chris
>
> [1]
http://ar.rubyonrails.com/classes/ActiveRecord/Associations/ClassMethods.html#M000012
> [2]
http://ar.rubyonrails.org/classes/ActiveRecord/Acts/Tree/ClassMethods.html
>
>
>
>
>
> On 4/15/05, Taisuke Yamada
<tyamadajp-PVDt0BYFe5m597m9RUd4JNi2O/JbrIOy@public.gmane.org> wrote:
>
>>Oh, and yes, I do have a one-line fix of this issue (attached),
>>but not so sure if this is a correct way to fix it. I still
>>haven''t grapsed how AR works internally...
>>
>>
>>>I''m having wierd problem with AR, and not sure if
it''s a feature (limitation)
>>>or a bug (or me doing something wrong). However, I can reproduce it
with
>>>attached script, so someone with more insight might help me...
>>>
>>>Here''s a problem. I''m using association table to
represent directed
>>>graph to manage dependency tree. With this data structure, I have
>>>two objects: node and link. And it will result to a tree something
>>>like below:
>>>
>>> node0 ---+---[link0]---> node1
>>> +---[link1]---> node2
>>> +---[link2]---> node3 ---[link3]---> node4
>>>
>>>Now, with AR, this can be expressed as
>>>
>>> - "node" having "has_many" relation with
"link"
>>> - "link" having "belongs_to" relation with
"node"
>>> - "node" having "has_and_belongs_to_many"
relation with other "node"
>>>
>>>However, when I use "id" as a primary key of
"link" table, AR fails to
>>>track "has_and_belongs_to_many" relation. I do get
"node" objects
>>>through accessing this relation, but returned nodes''
"node.id" attribute
>>>is messed up. AR seems to have filled it up with "id"
value of connecting
>>>"link" object.
>>>
>>>When I change primary key of "link" table from
"id" to anything else,
>>>this problem goes away.
>>>
>>>Is this a bug? Or is it me doing something wrong?
>>>
>>>I have attached script to reproduce this issue.
>>>I''ve verified this with latest activerecord-1.9.1,
installed by rubygems.
>>>
>>>Thanks in advance.
>>>--
>>>Taisuke Yamada
>>>
>>>
>>>
>>>------------------------------------------------------------------------
>>>
>>>#!/bin/sh
>>>
>>>rm -f test.db
>>>
>>>######################################################################
>>>sqlite3 test.db <<EOF
>>>--- digraph nodes
>>>CREATE TABLE nodes(
>>>id INTEGER PRIMARY KEY NOT NULL,
>>>name TEXT
>>>);
>>>INSERT INTO nodes VALUES (1, ''foo'');
>>>INSERT INTO nodes VALUES (2, ''bar'');
>>>
>>>--- digraph
>>>CREATE TABLE links(
>>>id INTEGER PRIMARY KEY NOT NULL,
>>>source INT,
>>>target INT
>>>);
>>>INSERT INTO links VALUES (1, 1, 2);
>>>EOF
>>>
>>>######################################################################
>>>ruby <<EOF
>>>require ''test/unit''
>>>require ''rubygems''
>>>require_gem ''activerecord''
>>>
>>>class Link < ActiveRecord::Base
>>> belongs_to :target, :class_name => "Node",
:foreign_key => "target"
>>>end
>>>
>>>class Node < ActiveRecord::Base
>>> has_many :target_links, :class_name => "Link",
:foreign_key => "source"
>>>
>>> has_and_belongs_to_many :target_nodes,
>>> :class_name => "Node", :foreign_key =>
"source",
>>> :join_table => "links", :association_foreign_key
=> "target"
>>>end
>>>
>>>class TC_TEST < Test::Unit::TestCase
>>> def test_main
>>> ActiveRecord::Base.establish_connection(:adapter =>
''sqlite3'',
>>> :dbfile =>
''test.db'')
>>> src = Node.find(1)
>>> dst = Node.find(2)
>>>
>>> ## get linked node by tracking each link
>>> tmp = src.target_links[0].target
>>>
>>> ## will pass
>>> assert_equal(dst.id, tmp.id,
>>> "Target node should be at the other end of the
link")
>>>
>>> ## get linked node directly using association table
>>> tmp = src.target_nodes[0]
>>>
>>> ## will pass - so seems OK, but...
>>> assert_equal(dst.name, tmp.name,
>>> "Target node should be at the other end of the
link")
>>>
>>> ## will NOT pass - id is messed up
>>> assert_equal(dst.id, tmp.id,
>>> "Target node should be at the other end of the
link")
>>> end
>>>end
>>>EOF
>>>
>>>exit 0
>>
>>
>>--- has_and_belongs_to_many_association.rb.orig 2005-04-15
21:18:06.000000000 +0900
>>+++ has_and_belongs_to_many_association.rb 2005-04-15
21:18:42.000000000 +0900
>>@@ -147,7 +147,7 @@
>> def construct_sql
>> interpolate_sql_options!(@options, :finder_sql, :delete_sql)
>> @finder_sql = @options[:finder_sql] ||
>>- "SELECT t.*, j.* FROM #{@join_table} j,
#{@association_table_name} t " +
>>+ "SELECT j.*, t.* FROM #{@join_table} j,
#{@association_table_name} t " +
>> "WHERE t.#{@association_class.primary_key} =
j.#{@association_foreign_key} AND " +
>> "j.#{@association_class_primary_key_name} =
#{@owner.quoted_id} " +
>> (@options[:conditions] ? " AND " +
interpolate_sql(@options[:conditions]) : "") + " " +
>>
>>_______________________________________________
>>Rails mailing list
>>Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>>http://lists.rubyonrails.org/mailman/listinfo/rails
>>
>>
>>
Chris McGrath
2005-Apr-16 14:40 UTC
Re: limitation of act_as_tree (was Re: ActiveRecord bug with has_and_belongs_to_many, using association table with primary key "id")
Ah, I see. You can name the primary key field link_id if you want, and in the Link class do primary_key "link_id" That way you can use it as a first class model and a join table. HTH, Chris On 4/16/05, Taisuke Yamada <tyamadajp-PVDt0BYFe5m597m9RUd4JNi2O/JbrIOy@public.gmane.org> wrote:> Thanks for a reply. > It was written so clearly, there was no way I could have dropped it (shame, shame). > > But it seems acts_as_tree is too limited to handle tree-like data. > > === Limitations of "acts_as_tree" ==> 1. Both node and link must be on the same table > a. This means link itself is cannot be first-class data > > 2. There can be only one referring parent node > a. You cannot define N-to-N relation (many nodes pointing to one node, > and one node point to multiple nodes) > > Am I correct with this understanding? > > Since I need to manage multiple digraph relation (= need to manage graph > relation in separate tables), with relation itself holding different > attributes (= link must be an object itself), I guess I need to do some > coding myself... ><snipped>
Taisuke Yamada
2005-Apr-18 03:15 UTC
Re: limitation of act_as_tree (was Re: ActiveRecord bug with has_and_belongs_to_many, using association table with primary key "id")
As you suggested, I worked around the problem by making sure no field have same name with "node" table and "link" table. Thanks! But I still have some doubt with this habtm behavior. Isn''t it more natural to make wanted object''s field to take precedence over associating table''s field? That way, you''ll never get "broken" object returned through habtm. Anyway, thanks for the help.> Ah, I see. You can name the primary key field link_id if you want, and > in the Link class do > > primary_key "link_id" > > That way you can use it as a first class model and a join table.-- Taisuke Yamada