Forum: Ruby on Rails HABTM << producing incorrect insert sql ?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
B09a3f6cdc4797532647d2d264b5df49?d=identicon&s=25 Jodi Showers (jshow)
on 2006-05-17 17:31
(Received via mailing list)
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`)
)
A2c85dc5ee81b12e3cc0a6522e8d079d?d=identicon&s=25 Chris Hall (Guest)
on 2006-05-17 17:37
(Received via mailing list)
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).
B09a3f6cdc4797532647d2d264b5df49?d=identicon&s=25 Jodi Showers (jshow)
on 2006-05-17 17:47
(Received via mailing list)
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
B09a3f6cdc4797532647d2d264b5df49?d=identicon&s=25 Jodi Showers (jshow)
on 2006-05-17 18:00
(Received via mailing list)
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
96931bfe0c2948f47a98e15ae52e5637?d=identicon&s=25 Chris Carter (Guest)
on 2006-05-17 19:29
YOu will have to use a has_many :through, not a HABTM
A2c85dc5ee81b12e3cc0a6522e8d079d?d=identicon&s=25 Chris Hall (Guest)
on 2006-05-17 19:44
(Received via mailing list)
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...
A2c85dc5ee81b12e3cc0a6522e8d079d?d=identicon&s=25 Chris Hall (Guest)
on 2006-05-17 19:48
(Received via mailing list)
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
B09a3f6cdc4797532647d2d264b5df49?d=identicon&s=25 Jodi Showers (jshow)
on 2006-05-17 20:41
(Received via mailing list)
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
E28c35323f624b8b9ed8712e25105454?d=identicon&s=25 Ray Baxter (Guest)
on 2006-05-17 20:57
(Received via mailing list)
Jodi Showers 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
A2c85dc5ee81b12e3cc0a6522e8d079d?d=identicon&s=25 Chris Hall (Guest)
on 2006-05-17 21:47
(Received via mailing list)
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 Showers <jodi@nnovation.ca> wrote:

That's great stuff Chris (and Chris).
B09a3f6cdc4797532647d2d264b5df49?d=identicon&s=25 Jodi Showers (jshow)
on 2006-05-18 20:22
(Received via mailing list)
Thank you all. Jodi
This topic is locked and can not be replied to.