Topic: has_and_belongs_to_many and unique table entries

I have two models, Entry and Keyword. An entry can be described by any number of keywords. The user specifies these keywords when they are creating a new entry.

So I basically have:

class Entry < ActiveRecord::Base
    has_and_belongs_to_many :keywords

class Keyword < ActiveRecord::Base

I also have a table in my db called entries_keywords, which looks like:

| id         | int(11) | NO   | PRI | NULL    | auto_increment |
| entry_id   | int(11) | NO   |     | 0       |                |
| keyword_id | int(11) | NO   |     | 0       |                |

Generally speaking, this is working well. Rails does what I expect, my Entry objects have a Keywords array attached to them, etc etc.

But I want to ensure all entries in the Keyword table are unique. So instead of just always doing whenever the user creates a new entry, I instead hand it off to this method in the Keyword class

class Keyword < ActiveRecord::Base

    def self.find_or_create_by_word(word)
        k = self.find(:first, :conditions => [ "word = ?", word])

        if k.nil?
            k = => word)

        return k

So I am effectively having the same Keyword object/row assigned to more than one Entry. I would have expected my entries_keyword table would reflect that properly, but instead I am getting:

Mysql::Error: #23000Duplicate entry '1' for key 1: INSERT INTO entries_keywords (`entry_id`, `id`, `keyword_id`) VALUES (3, 1, 1)

/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/connection_adapters/abstract_adapter.rb:120:in `log'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/connection_adapters/mysql_adapter.rb:184:in `execute'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/associations/has_and_belongs_to_many_association.rb:132:in `insert_record'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/callbacks.rb:354:in `callback'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/associations/association_proxy.rb:110:in `method_missing'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/associations/has_and_belongs_to_many_association.rb:81:in `method_missing'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/callbacks.rb:354:in `callback'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/callbacks.rb:346:in `callback'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/callbacks.rb:341:in `callback'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/callbacks.rb:266:in `create_without_timestamps'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/timestamp.rb:30:in `create'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:1718:in `create_or_update_without_callbacks'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/callbacks.rb:253:in `create_or_update'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/base.rb:1392:in `save_without_validation'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/validations.rb:736:in `save_without_transactions'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/transactions.rb:126:in `save'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/connection_adapters/abstract/database_statements.rb:51:in `transaction'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/transactions.rb:91:in `transaction'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/transactions.rb:118:in `transaction'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/transactions.rb:126:in `save'
app/controllers/entries_controller.rb:56:in `create'

I assume the duplicate value is for the id column, considering it's the only one that enforces uniqueness. Why is it doing that? Is it not valid to have two Entries use the same Keyword? Isnt't that what has_and_belongs_to_many allows?

Re: has_and_belongs_to_many and unique table entries

My bad, I fixed this. Here's the solution for other newbies smile

Just make sure the join table (in my case entries_keywords) has no id column. So create the table with

create_table :entries_keywords, :id => false do |t|
    t.column :entry_id, :integer
    t.column :keyword_id, :integer

note the addition of ":id => false"

works great now!

I found the answer in here

it's a very concise reference on rails

Last edited by tortoise (2006-11-01 22:32:39)

Re: has_and_belongs_to_many and unique table entries

Tortoise, you can also use the following plugin which will basically set up keywords (tags) for you: … s_taggable

Re: has_and_belongs_to_many and unique table entries

Yeah, Vin pointed that out in another thread. I'm prefering to roll my own for now just to get more comfortable with RoR. But thanks for the suggestion, I'm sure I'll make use of it in the future.

Re: has_and_belongs_to_many and unique table entries

BTW, you shouldn't need to specify find_or_create_by_word manually. ActiveRecord will automatically handle this method for you. Try commenting it out entirely and see if everything still works.

Railscasts - Free Ruby on Rails Screencasts

Re: has_and_belongs_to_many and unique table entries

On a related note, I have a habtm relation and was running into the same
error when I used the << method.

I created another primary key for the two foreign keys and started getting
the same error.

I'm used to trying an insert and then checking for an error, instead
with rails you get a big nasty error screen...

I guess you have to check the databsae first, which is an unfortunate
extra database query.