HABTM << producing incorrect insert sql?

Rails
1

Greetings railsers -

I’m trying to add to a collection through HABTM, but the sql
insert is trying to insert a PK rather than letting mysql produce the
auto_increment’ed PK.

@medication_dose holds a validated, saved model

@medication_dose.medication_frequencies << MedicationFrequency.find
(:all)

The above bails with,
	Mysql::Error: #23000Duplicate entry '1' for key 1: INSERT INTO

medication_dose_frequencies (medication_frequency_id, id,
medication_dose_id) VALUES (1, 1, 354)

The problem is that the primary key (‘id’) is duplicated. This is
happening because the sql generated by rails is not utilizing is not
using the auto_increment (verified as a mysql insert will work manually)

I suspect that HABTM is grabbing the id column from
MedicationFrequency and using it in the generation for the sql insert.

Is this normal behavior? Perhaps I’m adding to the collection
incorrectly?

If this looks like a but, is their a long winded way to express
this functionality?

Thanx for any advice.

cheers,
Jodi

Rails 1.1.2
ruby 1.8.4 (2005-12-24) [i686-darwin8.6.1]
activerecord (1.14.2)

mysql 4.1.18

class MedicationDose < ActiveRecord::Base
has_and_belongs_to_many :medication_frequencies, :join_table
=> ‘medication_dose_frequencies’
end

class MedicationDoseFrequency < ActiveRecord::Base
belongs_to :medication_dose
belongs_to :medication_frequency
end

class MedicationFrequency < ActiveRecord::Base
has_many :medication_dose_frequencies
end

DDL

CREATE TABLE medication_doses (
id int(11) NOT NULL auto_increment,
medication_id int(11) default NULL,
amount varchar(20) default NULL,
kind int(11) default NULL,
PRIMARY KEY (id)
)

CREATE TABLE medication_dose_frequencies (
id int(11) NOT NULL auto_increment,
medication_frequency_id int(11) default NULL,
medication_dose_id int(11) default NULL,
PRIMARY KEY (id)
)

CREATE TABLE medication_frequencies (
id int(11) NOT NULL auto_increment,
name varchar(10) default NULL,
kind int(11) default NULL,
sort int(11) default NULL,
PRIMARY KEY (id)
)

2

oye. so the id column in the join table is the problem. (no the api
docs did say something about this…slipped my mind)

hmm.

I do require an id column in the join - it’s referenced as a
belongs_to from another model.

Any suggestions given that I need an id in the join? Perhaps I need
to hand craft the join insert?

Thanx for your thoughts Chris.

cheers,
Jodi

3

given your design, you should not have a habtm relationship declared in
the
MedicationDose model.

you should have

class MedicationDose < ActiveRecord::Base
has_many :medication_dose_frequencies
end

if you want a true habtm, your join table would not have an id column,
you
would not have a model for the join table and your other models would
have
habtm to each other (assuming you follow rails conventions).

4

stupid question (ok, no stupid questions)

just to close off this thread, the following works.

   MedicationFrequency.find(:all).each {|mf|
     mdf = MedicationDoseFrequency.new(:medication_dose =>

@medication_dose, :medication_frequency => mf)
mdf.save
}

If there’s another dryer solution (preferably model declarative, not
functional) I’d like to hear about it.

Thanx again Chris.

Jodi

oye. so the id column in the join table is the problem. (no the api
docs did say something about this…slipped my mind)

hmm.

I do require an id column in the join - it’s referenced as a
belongs_to from another model.

Any suggestions given that I need an id in the join? Perhaps I need
to hand craft the join insert?

Thanx for your thoughts Chris.

cheers,
Jodi

5

if you want your join to be a model, then do

Foo < ActiveRecord::Base
has_many :things
has_many :foos, :through => things # creates convenient association to
foos through the things association
end

Bar < ActiveRecord::Base
has_many :things
has_many :bars, :through => :things # creates convenient association
to
bars through the things association
end

Thing < ActiveRecord::Base
belongs_to :foo
belongs_to :bar
end

and the following tables in your database

foos

id
…

bars

id
…

things

id
foo_id
bar_id
…

now onto your latest question.

bar = Bar.find(1)
Foo.find(:all).each do |foo|
thing.create(:foo => foo, :bar => bar)
end

which is pretty much what you have.

read this article on has_many :through and creating associations

http://blog.hasmanythrough.com/articles/2006/04/17/join-models-not-proxy-collections

6

typo…replace Foo and Bar classes in previous post with this. sorry.

Foo < ActiveRecord::Base
has_many :things
has_many :bars, :through => things # creates convenient association to
bars through the things association
end

Bar < ActiveRecord::Base
has_many :things
has_many :foos, :through => :things # creates convenient association
to
foos through the things association
end

7

YOu will have to use a has_many :through, not a HABTM

8

That’s great stuff Chris (and Chris).

Question: Given that the means to add to the ‘collection’ is
basically identical, what advantage does :through give us?

cheers,
Jodi

9

from the api documentation

Has Many associations can be configured with the :through option to use
an
explicit join model to retrieve the data. This operates similarly to a
has_and_belongs_to_many association. The advantage is that you’re able
to
add validations, callbacks, and extra attributes on the join model.

So basically :through allows you to have an association “through” an
existing has_many or belongs_to association. however, creating
associations
does not work the same as a habtm. ie, you can’t do foo.bars << bar via
a
:through relationship, hence why you have to create the associations in
the
join model manually.

On 5/17/06, Jodi S. [email protected] wrote:

That’s great stuff Chris (and Chris).

10

Thank you all. Jodi

11

Jodi S. wrote:

That’s great stuff Chris (and Chris).

Question: Given that the means to add to the ‘collection’ is basically
identical, what advantage does :through give us?

When you use through, you have a full-fledged object instead of a join
table. Then you can have Thing object, which can have attributes.

In your case, you have id, which is not very interesting, but this can
be any attribute. If you have Authors and Books, each of which has many
of the other through Authorships, then in your Authorships table you
could keep track of how much each Author was paid for each Book.

–

Ray