Nooby Stuck - "has_and_belongs_to_many" relationship

arowan · October 11, 2010, 10:33pm

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:inexecute’
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:ininsert_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:ininsert’
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:incall’
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:ininsert’
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:intransaction’
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:intransaction’
from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/associations/association_collection.rb:132:in `<<’
from (irb):4>>

arowan · October 12, 2010, 1:42am

On Oct 11, 4:33pm, Al Rowan [email protected] wrote:

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.

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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

arowan · October 12, 2010, 1:42am

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

On Oct 11, 4:33pm, Al Rowan [email protected] wrote:
[…]

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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

arowan · October 12, 2010, 11:34am

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

arowan · October 12, 2010, 11:49am

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

arowan · October 12, 2010, 12:02pm

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.

arowan · October 12, 2010, 2:54pm

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

arowan · October 12, 2010, 5:10pm

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.

arowan · October 12, 2010, 2:35pm

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:inexecute’
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:inselect_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:infind_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:inall’
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:inwith_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:inwith_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:infind_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:incount_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:inempty?’
from (irb):10>>