Problem with habtm and resulting SQL insert


#1

Cheers,

I have a problem with 1.0 and a habtm relationship between User and
Article.

I want to save all articles that users read. I have these models:

class User < ActiveRecord::Base
has_and_belongs_to_many :read_articles, :class_name => “Article”,
:join_table => “read_articles”

end

class Article < ActiveRecord::Base
has_and_belongs_to_many :readers, :class_name => “User”, :join_table
=> “read_articles”

end

When I do a

a = User.find(1)
b = Article.find(12)

a.read_articles << b

The SQL looks like:

INSERT INTO read_articles (updated_at, id, article_id, user_id,
created_at) VALUES (‘2005-12-10 23:26:56’, 12, 12, 1, ‘2005-12-05
03:44:01’)

The problem is that the id field will be set with the article_id. The
id should be set by the database.

The SQL definition of read_articles looks like this:

mysql> describe read_articles;
±-----------±---------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±-----------±---------±-----±----±--------±---------------+
| id | int(11) | | PRI | NULL | auto_increment |
| user_id | int(11) | | | 0 | |
| article_id | int(11) | | | 0 | |
| created_at | datetime | YES | | NULL | |
| updated_at | datetime | YES | | NULL | |
±-----------±---------±-----±----±--------±---------------+

Any solutions to my problem?

Thanks,
Jonathan


#2

Any solutions to my problem?

Yes, don’t have an id field in read_articles. It’s just a join table,
not an entity table. Only entity tables should have ids.

David Heinemeier H.
http://www.loudthinking.com – Broadcasting Brain
http://www.basecamphq.com – Online project management
http://www.backpackit.com – Personal information manager
http://www.rubyonrails.com – Web-application framework


#3

On 1/9/06, Jonathan W. removed_email_address@domain.invalid wrote:

INSERT INTO read_articles (updated_at, id, article_id, user_id,
created_at) VALUES (‘2005-12-10 23:26:56’, 12, 12, 1, ‘2005-12-05
03:44:01’)

The problem is that the id field will be set with the article_id. The
id should be set by the database.

The Allow HABTM Primary Key Plugin fixes this (it’s a one line change
to HasAndBelongsToManyAssociation#insert_record). See
http://wiki.rubyonrails.com/rails/pages/Plugins. This is a long
standing issue with Rails (see
http://dev.rubyonrails.org/ticket/1031). I consider it a major bug,
but I don’t think everyone would agree.


#4

Why does ActiveRecord set the id to article.id?

Actually, it doesn’t, your database determines which id makes it out.
Thing is that Rails is doing a SELECT * FROM which includes all the
columns from both join table and entity table.

David Heinemeier H.
http://www.loudthinking.com – Broadcasting Brain
http://www.basecamphq.com – Online project management
http://www.backpackit.com – Personal information manager
http://www.rubyonrails.com – Web-application framework


#5

David Heinemeier H. wrote:

Any solutions to my problem?

Yes, don’t have an id field in read_articles. It’s just a join table,
not an entity table. Only entity tables should have ids.

Ahh, that makes sense :slight_smile:

I created this table with a migration and forgot that id will be
created automatically if you do not specify :id => false.

Why does ActiveRecord set the id to article.id?

Thanks,
Jonathan