Nooby Stuck - "has_and_belongs_to_many" relationship

rubyonrails-talk
1

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>>

2

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,

3

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,

4

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

5

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

6

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.

7

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>>

8

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

9

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.