can't use habtm at all

Railsers:

Please blame SQLite3, not my feeb capacity to read the Agile book, and all the blogs.

I want to do many-to-many between two tables. Classic relational stuff, right?

So my schema looks a little bit like this:

  create_table "users_props", :force => true do |t|     t.column "prop_id", :integer     t.column "user_id", :integer   end

That links out to users and props tables, right? So their models contain the sacred habtm incantations:

class User < ActiveRecord::Base     has_and_belongs_to_many :props ...

class Prop < ActiveRecord::Base   has_and_belongs_to_many :users ...

All too obvious to post, right? And all straight out of the Daves' Agile book, right?

Now let's put them in motion:

   user.props << prop

That produces a huge error message like this:

ActiveRecord::StatementInvalid: ActiveRecord::StatementInvalid     /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/connection_adapters/sqlite_adapter.rb:259:in `table_structure'     /usr/lib/ruby/gems/1.8/gems/activesupport-1.3.1/lib/active_support/core_ext/object/misc.rb:23:in `returning'

I know how to fix that. I replace the syntactic sugar with this old-skool SQL:

            Prop.find_by_sql("""                     insert into users_props(user_id, prop_id)                                    values(#{user.id}, #{prop.id})                     """)

Okay. The record is probably there (I honestly didn't check.) Now I try to read it out:

  assert_equal 1, quentin.props.count

Now check what _that_ gives:

ActiveRecord::StatementInvalid: SQLite3::SQLException: no such table: props_users: SELECT count(*) AS count_all FROM props INNER JOIN props_users ON props.id = props_users.prop_id WHERE (props_users.user_id = 1 )

Why is it asking for props_users when I have a users_props?

You need to rename your users_props table to props_users; Rails expects the table names to be joined in alphabetical order, and "props" comes before "users" alphabetically.

Regards

Dave M.

I believe ActiveRecord by default assumes join tables to be named with the joined table names in alphabetical order.

Michael Glaesemann grzm seespotcode net

David Mitchell wrote:

You need to rename your users_props table to props_users; Rails expects the table names to be joined in alphabetical order, and "props" comes before "users" alphabetically.

Thaaanks.

And, yep, the Agile book says that.

They shouldn't have put it above the paragraphs where anyone with >5 years experience coding Ruby would have started skimming!!!

David Mitchell wrote:

You need to rename your users_props table to props_users; Rails expects the table names to be joined in alphabetical order, and "props" comes before "users" alphabetically.

Oooh, one more wisecrack:

Well, I'm off to edit my schema.rb file, right below where it says not to edit it!

Yep - I fell into this same trap the first time I tried writing a Rails app, and it took someone else to point it out to me... Now it's your turn to carry on the tradition!

Regards

Dave M.

And, yep, the Agile book says that.

They shouldn't have put it above the paragraphs where anyone with >5 years experience coding Ruby would have started skimming!!!

It's an excellent book, the only problem I had with it was that I had development experience and wanted to get straight into applying it. After stumbling through it once I then realised that reading it cover to cover before playing made much more sense as there is plenty of solid content later in the book (especially in the testing chapters) that cement things that looked like voodoo earlier.

Just my thoughts,

Glenn

glenn wrote:

It's an excellent book, the only problem I had with it was that I had development experience and wanted to get straight into applying it. After stumbling through it once I then realised that reading it cover to cover before playing made much more sense as there is plenty of solid content later in the book (especially in the testing chapters) that cement things that looked like voodoo earlier.

Hoo-yah. I take it with me whenever I have to go somewhere that's computer-hostile. Just avoid the pages I have already read too much!

One other thing, you should add :id => false to create_table:

create_table "users_props", :force => true, :id => false do |t|    t.column "prop_id", :integer    t.column "user_id", :integer end

HABTM join tables should not have an ID column.

V/r