Monday, March 3, 2008

Has_and_belongs_to_many Sqlite3 headache: "SQL logic error or missing database"

What?

How to get many to many relations working quickly with Rails 2.0.2 and sqlite3 3.4.0.

Why?

Many to Many database relations between two models (tables) requires a middle table which records the relation. This can be difficult to get right, but Rails has some awesome helper functions to get you up and running quickly.

... when it works.

When it doesn't, you're likely to see the following:

"SQL logic error or missing database"
Which is about as descriptive as "it's not working." The kink is that Active Record (rails' database interface) will try to set the id field of the relational table, which is reserved by default as the primary key and therefore cannot be set to the same value twice (which rails will do).

How?

First create the models

./script/generate scaffold Actor first_name:string last_name:string
./script/generate scaffold Movie name:string

Then create a migration to set up the relational table:

./script/generate migration AddActorsMoviesRelation

Edit ./db/migration/xyz_AddActorsMoviesRelation.rb:
(Thanks EyeDeal for the :id => false tip!)


class AddActorsMoviesRelation < ActiveRecord::Migration
def self.up
create_table :actors_movies, :id => false do |t|
t.integer :actor_id, :foreign_key => true
t.integer :movie_id, :foreign_key => true
end
end

def self.down
drop_table :actors_movies
end
end



Then build the database

rake db:migrate

And finally edit ./app/models/actor.rb and ./app/models/movie.rb:


class Actor < ActiveRecord::Base
has_and_belongs_to_many :movies
end

class Movie < ActiveRecord::Base
has_and_belongs_to_many :movies
end



You should now be able to add movies to actors and vice versa with a simple


crime_fiction = Movie.create :name => "Crime Fiction"
san_fransisco = Movie.create :name => "The Streets of San Francisco"
jon = Actor.create :first_name => 'Jonathan', :last_name => 'Elliot'
jesse = Actor.create :first_name => 'Jesse', :last_name => 'Friedman'
dan = Actor.create :first_name => 'Dan', :last_name => 'Bakkedahl'

crime_fiction.actors << jon
jesse.movies << crime_fiction
dan.movies << crime_fiction

crime_fiction.actors.each {|actor| puts "#{actor.last_name}, #{actor.first_name}" }
# => Elliot, Jonathan
# => Friedman, Jesse
# => Bakkedahl, Dan

san_fransisco.actors << jesse
jesse.movies.each {|movie| puts movie.name }
# => Crime Fiction
# => The Streets of San Francisco

6 comments:

Бранко Вукелић said...

You don't have to:

def self.up create_table :actors_movies, :primary_key => :primary_id

and

t.integer :id

if you don't intend to access records in the join table directly. You can simply say:

def self.up create_table :actors_movies, :id => false

At least, in cases when you don't want/need to access those records directly, the primary-key-less table should only contain IDs for records from tables it is joining and nothing else.

Marcus Westin said...

Indeed, thanks! Without the :id => false I kept having problems with one of the classes trying to write to the :id field of the relational table. This took care of that problem.

Out of curiosity, how does :id => false change the sql? Does it remove the id AUTO_INCREMENT and PRIMARY_KEY statements?

Бранко Вукелић said...

AFAIK (and I've started using Rails like 2 months ago), Rails tries to write an ID simply because it finds the column. Since it doesn't expect an AUTO_INC and PRIMARY_KEY it writes (I think) the default value of 1. It works the first time, but fails the second time, because it finds a record with the same ID which DB won't allow.

Now, I think it all boils down to Rails simply not expecting to find an auto-inc/pri-key. It's also in the docs.

http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#M001150

Take a look at the explanation for :id, which says:

"Defaults to true. Join tables for has_and_belongs_to_many should set :id => false."

Бранко Вукелић said...
This comment has been removed by the author.
Бранко Вукелић said...

I think someone also mentioned a more heavy-handed habtm+has_many combo with the :id => true, but I don't know how that works in reality and how useful it is in general. There is also a way to establish a "has_many through" relationship with a join _resource_ instead of a join table. That's useful when you want to work with the relationships themselves.

It's works more or less like habtm, but the join table belongs to a separate model (resource) which you can use on its own. The table has it's primary key as well for that reason.

Anonymous said...

marcuswestinblog.blogspot.com is very informative. The article is very professionally written. I enjoy reading marcuswestinblog.blogspot.com every day.
canadian payday loans
pay day loans