Autoincrement on Join Table not working

Rails
1

Hi all, I’m trying to create a directed may to many relationship
against a single table as seen here:
http://wiki.rubyonrails.com/rails/pages/HowToUseManyToManyAgainstASingleTable

I’m running 1.2RC2 right now and am doing this on a Postgresql 8.2
database.

I’ve defined my model like this:

class Item < ActiveRecord::Base
has_and_belongs_to_many :tags,
:class_name => ‘Item’,
:join_table => ‘items_items’,
:foreign_key => “parent_id”,
:association_foreign_key => “child_id”

has_and_belongs_to_many :tasks,
:class_name => ‘Item’,
:join_table => ‘items_items’,
:foreign_key => “child_id”,
:association_foreign_key => “parent_id”

validates_presence_of :name
end

Then, I try it out in the console:

phone_context = Item.new(:name => “phone”)
phone_context.save
=> true
email_context = Item.new(:name => “email”)
email_context.save
=> true
alice_task = Item.new(:name => “speak with alice about account”)
alice_task.save
=> true
email_context.tasks << alice_task

=> [#<Item:0x32e4e30 @attributes={“end_date”=>nil, “start_date”=>nil,
“name”=>“speak with alice about account”, “done”=>false,
“updated_on”=>Thu Jan 18 11:31:37 -0500 2007, “id”=>26, “reset”=>nil,
“due”=>false, “end_time”=>nil, “start_time”=>nil},
errors#<ActiveRecord::Errors:0x32dc514 @errors={},
@base=#<Item:0x32e4e30 …>, new_recordfalse,
new_record_before_savetrue]

email_context.tasks[0].name
=> “speak with alice about account”
alice_task.tags[0].name
=> “email”

So far so good. Now, since I have Alice’s phone number too, I decide
to add the alice_task to the phone_context tasks.

phone_context.tasks << alice_task
ActiveRecord::StatementInvalid: RuntimeError: ERROR C23505
Mduplicate key violates unique constraint “items_items_pkey”
Fnbtinsert.c L277 R_bt_check_unique: INSERT INTO items_items
(“id”, “child_id”, “parent_id”) VALUES (26, 24, 26)
from
./script/…/config/…/config/…/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:128:in
log' from ./script/../config/../config/../vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb:152:in execute’
from
./script/…/config/…/config/…/vendor/rails/activerecord/lib/active_record/associations/has_and_belongs_to_many_association.rb:132:in
insert_record' from ./script/../config/../config/../vendor/rails/activerecord/lib/active_record/associations/association_collection.rb:26:in <<’
from
./script/…/config/…/config/…/vendor/rails/activerecord/lib/active_record/associations/association_collection.rb:23:in
each' from ./script/../config/../config/../vendor/rails/activerecord/lib/active_record/associations/association_collection.rb:23:in <<’
from
./script/…/config/…/config/…/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:59:in
transaction' from ./script/../config/../config/../vendor/rails/activerecord/lib/active_record/transactions.rb:95:in transaction’
from
./script/…/config/…/config/…/vendor/rails/activerecord/lib/active_record/transactions.rb:121:in
transaction' from ./script/../config/../config/../vendor/rails/activerecord/lib/active_record/associations/association_collection.rb:22:in <<’
from (irb):9

When I run “select * from items_items_id_seq” on my database, I get this
back:

sequence_name | last_value | increment_by | max_value
| min_value | cache_value | log_cnt | is_cycled | is_called
--------------------±-----------±-------------±--------------------±----------±------------±--------±----------±----------
items_items_id_seq | 1 | 1 | 9223372036854775807
| 1 | 1 | 1 | f | f
(1 row)

So it appears that Rails isn’t using the autoincrement for the
items_items table (a setting I missed with the habtm declarations?)

Also, seemingly in relation to this error, when I inspect the tags of
alice_task, I get this

alice_task.tags[0]
=> #<Item:0x32a32c8 @attributes={“end_date”=>nil, “start_date”=>nil,
“name”=>“email”, “done”=>“f”, “updated_on”=>“2007-01-18”,
“id”=>“26”, “child_id”=>“25”, “reset”=>nil, “parent_id”=>“26”,
“due”=>“f”, “end_time”=>nil, “start_time”=>nil}, readonlytrue

But I can easily see that the email_context’s id is not 26:
email_context.id
=> 25

If anyone has any suggestions as to what I’m doing wrong or if there’s
another approach that fits better with 1.2 or if you can help me
identify this as a rails bug (and that I’m not going crazy), I would
be very appreciative.

–
-Dan Nugent

2

I guess what I was doing was wrong, Here’s the updated model for
anyone that’s curious.

class Item < ActiveRecord::Base
has_many :notes
has_and_belongs_to_many :tags,
:class_name => ‘Item’,
:join_table => ‘items_items’,
:foreign_key => “parent_id”,
:association_foreign_key => “child_id”,
:select => “items.*”,
:insert_sql => ‘INSERT INTO items_items (“child_id”, “parent_id”)
VALUES (#{record.id}, #{id})’

has_and_belongs_to_many :tasks,
:class_name => ‘Item’,
:join_table => ‘items_items’,
:foreign_key => “child_id”,
:association_foreign_key => “parent_id”,
:select => “items.*”,
:insert_sql => ‘INSERT INTO items_items (“child_id”, “parent_id”)
VALUES (#{id}, #{record.id})’

validates_presence_of :name
end

What I was not aware of was that Rails would attempt to populate the
records found through the join with the attributes of the join table
(not something I particularly see the value of) and in so doing
overwrites the accessor for the real ids of the found records.
Further, and this one I can’t explain, the default insert statement
wants to make the association foreign key the primary key of the
record of the join table.

Is this stuff really intended to work this way?

On 1/18/07, Daniel N. [email protected] wrote:

:class_name => 'Item',

validates_presence_of :name
alice_task = Item.new(:name => “speak with alice about account”)
new_record_before_savetrue]
ActiveRecord::StatementInvalid: RuntimeError: ERROR C23505
`<<’
from ./script/…/config/…/config/…/vendor/rails/activerecord/lib/active_record/associations/association_collection.rb:22:in
(1 row)
“id”=>“26”, “child_id”=>“25”, “reset”=>nil, “parent_id”=>“26”,

–
-Dan Nugent

–
-Dan Nugent