has_and_belongs_to_many & Duplicate entry Error

In one of my Rails projects at eSpace, i had a Many to Many association between two models. this allowed me to look more deeply into the has_and_belongs_to_many association.

When you say:

class Question < ActiveRecord::Base
   has_and_belongs_to_many :surveys
end

class Survey < ActiveRecord::Base
   has_and_belongs_to_many :questions
end

Rails will assume there is a table questions_surveys that include foreign keys to the two entities.
So this table should be created by the following migration

create_table :questions_surveys,:id => false do |t|
    t.integer :question_id, :null => false
    t.integer :survey_id, :null => false
end

:id=>false will prevent the creation of the default primary key for that table.This is very important for has_and_belongs_to_many associations. as the API documentation say; other attributes in thatrelation will be loaded with the objects and will be read only, including the :id. So failing to disable the id generationfor that table will cause the loaded objects to have and "id" attribute holding the value of the id or the questions_surveys entries instead of the ids of the target entity (question or survey in our example).

That's why u may have only 20 questions but when you try "survey.questions.collect(&:id) you'll find values that are totally out of the range.

This is also the cause of the "Mysql::Error: Duplicate entry '#' for key #" entry error you'll find while adding entries. (survey.questions << question )

It is highly recommended in the rails documentation to use a real model to represent the many to many association.But if your case was just a simple two foreign keys table, use has_and_belongs_to_many, just don't forget to disable the id generation.

Comments

Anonymous said…
very thanks!
mahmoud said…
You are welcome..

I must admit.. most of us don't read the API documentation so carefully and fall into this

Popular posts from this blog

Ruby script for downloading rails casts

Stress Testing with Siege and Bombard, know your limits

Exporting data from remote mysql instance in csv, using mysql client and sed