Nooby Stuck - "has_and_belongs_to_many" relationship

trying to set up a "has_and_belongs_to_many" relationship

would very much appreciate the help, not sure what im doing wrong at all.

Scheme.rb

class Scheme < ActiveRecord::Base
  
  validates :schemename, :presence => true
  belongs_to :user
  has_many :levels, :dependent => :destroy
  
    has_and_belongs_to_many :works

end

Work.rb

class Work < ActiveRecord::Base

  validates :workname, :presence => true
  
  belongs_to :user
  belongs_to :unit
  has_many :marks
  has_and_belongs_to_many :schemes

end

migration for schemes_works

class CreateSchemesWorks < ActiveRecord::Migration
def self.up
  create_table :schemes_works, :id => false do |t|
    t.references :scheme
    t.references :work
end

THE ERROR

scheme = Scheme.last

=> #<Scheme id: 21, schemename: "another", colour: nil, share: nil, subject_id: nil, user_id: 2, created_at: "2010-10-05 08:17:03", updated_at: "2010-10-05 08:17:03">

work = Work.last

=> #<Work id: 5, workname: "thrd", unit_id: 4, user_id: nil, unit: nil, created_at: "2010-10-11 17:24:51", updated_at: "2010-10-11 17:24:51">

scheme.works << work

ActiveRecord::StatementInvalid: SQLite3::SQLException: near ")": syntax error: INSERT INTO "schemes_works" () VALUES ()
  from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/connection_adapters/abstract_adapter.rb:202:in `log'
  from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/connection_adapters/sqlite_adapter.rb:135:in `execute'
  from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/connection_adapters/abstract/database_statements.rb:263:in `insert_sql'
  from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/connection_adapters/sqlite_adapter.rb:149:in `insert_sql'
  from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/connection_adapters/abstract/database_statements.rb:44:in `insert'
  from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/connection_adapters/abstract/query_cache.rb:16:in `insert'
  from /Library/Ruby/Gems/1.8/gems/arel-1.0.1/lib/arel/engines/sql/engine.rb:30:in `create'
  from /Library/Ruby/Gems/1.8/gems/arel-1.0.1/lib/arel/algebra/relations/writes.rb:24:in `call'
  from /Library/Ruby/Gems/1.8/gems/arel-1.0.1/lib/arel/session.rb:17:in `create'
  from /Library/Ruby/Gems/1.8/gems/arel-1.0.1/lib/arel/algebra/relations/relation.rb:159:in `insert'
  from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/associations/has_and_belongs_to_many_association.rb:70:in `insert_record'
  from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/associations/association_collection.rb:136:in `<<'
  from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/associations/association_collection.rb:479:in `add_record_to_target_with_callbacks'
  from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/associations/association_collection.rb:135:in `<<'
  from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/associations/association_collection.rb:133:in `each'
  from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/associations/association_collection.rb:133:in `<<'
  from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/associations/association_collection.rb:158:in `transaction'
  from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/connection_adapters/abstract/database_statements.rb:139:in `transaction'
  from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/transactions.rb:204:in `transaction'
  from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/associations/association_collection.rb:157:in `transaction'
  from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/associations/association_collection.rb:132:in `<<'
  from (irb):4>>

trying to set up a "has_and_belongs_to_many" relationship

would very much appreciate the help, not sure what im doing wrong at all.

Scheme.rb

class Scheme < ActiveRecord::Base

validates :schemename, :presence => true

Just :name. There's no need to repeat your table name in your field
names.

belongs_to :user
has_many :levels, :dependent => :destroy

has\_and\_belongs\_to\_many :works

end

Work.rb

class Work < ActiveRecord::Base

validates :workname, :presence => true

Again, just :name.

belongs_to :user
belongs_to :unit
has_many :marks
has_and_belongs_to_many :schemes

end

You have User, Unit, and Mark already defined, right?

migration for schemes_works

class CreateSchemesWorks < ActiveRecord::Migration
def self.up
create_table :schemes_works, :id => false do |t|
t.references :scheme
t.references :work
end

This generally looks good so far. Make sure you install the Foreigner
gem so you can easily put foreign key constraints in your DB.

THE ERROR

>> scheme = Scheme.last

=> #<Scheme id: 21, schemename: "another", colour: nil, share: nil, subject_id: nil, user_id: 2, created_at: "2010-10-05 08:17:03", updated_at: "2010-10-05 08:17:03">>> work = Work.last

=> #<Work id: 5, workname: "thrd", unit_id: 4, user_id: nil, unit: nil, created_at: "2010-10-11 17:24:51", updated_at: "2010-10-11 17:24:51">>> scheme.works << work

ActiveRecord::StatementInvalid: SQLite3::SQLException: near ")": syntax error: INSERT INTO "schemes_works" () VALUES ()

That's peculiar. Seems like Rails is not generating the right SQL to
send to the DB. I gather you're doing this from the Rails console;
have you restarted it since adding the classes and running the
migrations given above? If not, then do so and see if the problem
persists.

Best,

After posting, I looked again, and I think I see your problem.

[...]

migration for schemes_works

class CreateSchemesWorks < ActiveRecord::Migration
def self.up
create_table :schemes_works, :id => false do |t|
t.references :scheme
t.references :work
end

THE ERROR

[...]

ActiveRecord::StatementInvalid: SQLite3::SQLException: near ")": syntax error: INSERT INTO "schemes_works" () VALUES ()

Notice the empty first pair of parentheses there. The field names
should go there (that is, the query should look like
INSERT INTO "schemes_works" (scheme_id, work_id) VALUES (21, 5)
), so presumably Rails doesn't realize there are any fields in that
table. Now the question is *why*.

Take a look at the schemes_works table in your DB. Does it have
scheme_id and work_id fields?

Take a look at your db/schema.rb file, specifically the part
describing the schemes_works table. Does it have those fields?

Best,

I had done a "reload!" before doing those console commands, i even
rebooted! lol

my schemes_work table contains a scheme and work field only. not
scheme_id nor work_id should i change them? i was following the
configuration from a tutorial where they did the same. so i just assumed
that was right. ill rename them now and see if it solves it. it does
make sense! lol

could it be because i've used "t.refrences" and not "t.integer"

I only say this because, I tried to rename the columns and it threw up
the error "Missing column schemes_works.scheme"

using

def self.up
rename_column :schemes_works, :scheme, :scheme_id
rename_column :schemes_works, :work, :work_id
end

ok so, i added the "_id" to both..same error. i also changed it from
refrences to integer and it gave me the same error.

dont know if this helps at all..

?> scheme.works.empty?
ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column:
schemes_works.scheme_id: SELECT * FROM "works" INNER JOIN
"schemes_works" ON "works".id = "schemes_works".work_id WHERE
("schemes_works".scheme_id = 21 )
  from
/Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/connection_adapters/abstract_adapter.rb:202:in
`log'
  from
/Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/connection_adapters/sqlite_adapter.rb:135:in
`execute'
  from
/Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/connection_adapters/sqlite_adapter.rb:284:in
`select'
  from
/Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in
`select_all'
  from
/Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/connection_adapters/abstract/query_cache.rb:56:in
`select_all'
  from
/Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/base.rb:467:in
`find_by_sql'
  from
/Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/relation.rb:64:in
`to_a'
  from
/Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/relation/finder_methods.rb:143:in
`all'
  from
/Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/associations/association_collection.rb:70:in
`find'
  from
/Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/base.rb:1128:in
`with_scope'
  from
/Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/associations/association_proxy.rb:203:in
`send'
  from
/Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/associations/association_proxy.rb:203:in
`with_scope'
  from
/Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/associations/association_collection.rb:63:in
`find'
  from
/Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/associations/association_collection.rb:467:in
`find_target'
  from
/Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/associations/association_collection.rb:409:in
`load_target'
  from
/Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/associations/has_and_belongs_to_many_association.rb:33:in
`count_records'
  from
/Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/associations/association_collection.rb:300:in
`size'
  from
/Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/associations/association_collection.rb:319:in
`empty?'
  from (irb):10>>

solved that last error by changing it back from the extra "_id"

through some strange coincidence, my mac crashed. after a reboot it now
all works. sorry for wasting your marnen and thanks very much for your
assistance. I'm quite stumped as to what actually triggered the fix, I
haven't done anything really. I am using Aptana Studio 3...(still in
beta) perhaps something strange is going on with it.