# can't use habtm at all

**URL:** <https://discuss.rubyonrails.org/t/cant-use-habtm-at-all/4952>\
**Category:** rubyonrails-talk\
**Created:** [November 24, 2006, 2:59am UTC](https://discuss.rubyonrails.org/t/cant-use-habtm-at-all/4952 "2006-11-24T02:59:52Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Phlip](https://avatars.discourse-cdn.com/v4/letter/p/ed8c4c/32.png) [@Phlip](https://discuss.rubyonrails.org/u/Phlip)\
**Post date:** [November 24, 2006, 2:59am UTC](https://discuss.rubyonrails.org/t/cant-use-habtm-at-all/4952/1 "2006-11-24T02:59:52Z")

</div>

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:

&nbsp;&nbsp;create\_table "users\_props", :force =\> true do |t| &nbsp;&nbsp;&nbsp;&nbsp;t.column "prop\_id", :integer &nbsp;&nbsp;&nbsp;&nbsp;t.column "user\_id", :integer &nbsp;&nbsp;end

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

class User \< ActiveRecord::Base &nbsp;&nbsp;&nbsp;&nbsp;has\_and\_belongs\_to\_many :props ...

class Prop \< ActiveRecord::Base &nbsp;&nbsp;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:

&nbsp;&nbsp;&nbsp;user.props \<\< prop

That produces a huge error message like this:

ActiveRecord::StatementInvalid: ActiveRecord::StatementInvalid &nbsp;&nbsp;&nbsp;&nbsp;/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active\_record/connection\_adapters/sqlite\_adapter.rb:259:in `table_structure' &nbsp;&nbsp;&nbsp;&nbsp;/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:

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Prop.find\_by\_sql(""" &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;insert into users\_props(user\_id, prop\_id) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;values(#{user.id}, #{prop.id}) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;""")

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

&nbsp;&nbsp;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?

---

<div class="post-metadata">

**Author:** ![David\_Mitchell](https://avatars.discourse-cdn.com/v4/letter/d/2bfe46/32.png) [@David\_Mitchell](https://discuss.rubyonrails.org/u/David_Mitchell)\
**Post date:** [November 24, 2006, 3:46am UTC](https://discuss.rubyonrails.org/t/cant-use-habtm-at-all/4952/2 "2006-11-24T03:46:12Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Michael\_Glaesemann](https://avatars.discourse-cdn.com/v4/letter/m/bc79bd/32.png) [@Michael\_Glaesemann](https://discuss.rubyonrails.org/u/Michael_Glaesemann)\
**Post date:** [November 24, 2006, 3:51am UTC](https://discuss.rubyonrails.org/t/cant-use-habtm-at-all/4952/3 "2006-11-24T03:51:47Z")

</div>

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

Michael Glaesemann grzm seespotcode net

---

<div class="post-metadata">

**Author:** ![Phlip](https://avatars.discourse-cdn.com/v4/letter/p/ed8c4c/32.png) [@Phlip](https://discuss.rubyonrails.org/u/Phlip)\
**Post date:** [November 24, 2006, 4:03am UTC](https://discuss.rubyonrails.org/t/cant-use-habtm-at-all/4952/4 "2006-11-24T04:03:57Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Phlip](https://avatars.discourse-cdn.com/v4/letter/p/ed8c4c/32.png) [@Phlip](https://discuss.rubyonrails.org/u/Phlip)\
**Post date:** [November 24, 2006, 4:04am UTC](https://discuss.rubyonrails.org/t/cant-use-habtm-at-all/4952/5 "2006-11-24T04:04:53Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![David\_Mitchell](https://avatars.discourse-cdn.com/v4/letter/d/2bfe46/32.png) [@David\_Mitchell](https://discuss.rubyonrails.org/u/David_Mitchell)\
**Post date:** [November 24, 2006, 5:39am UTC](https://discuss.rubyonrails.org/t/cant-use-habtm-at-all/4952/6 "2006-11-24T05:39:14Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![glenn1](https://avatars.discourse-cdn.com/v4/letter/g/3ec8ea/32.png) [@glenn1](https://discuss.rubyonrails.org/u/glenn1)\
**Post date:** [November 24, 2006, 8:55am UTC](https://discuss.rubyonrails.org/t/cant-use-habtm-at-all/4952/7 "2006-11-24T08:55:51Z")

</div>

> 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

---

<div class="post-metadata">

**Author:** ![Phlip](https://avatars.discourse-cdn.com/v4/letter/p/ed8c4c/32.png) [@Phlip](https://discuss.rubyonrails.org/u/Phlip)\
**Post date:** [November 25, 2006, 6:52am UTC](https://discuss.rubyonrails.org/t/cant-use-habtm-at-all/4952/8 "2006-11-25T06:52:56Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![Anthony\_Eden](https://avatars.discourse-cdn.com/v4/letter/a/edb3f5/32.png) [@Anthony\_Eden](https://discuss.rubyonrails.org/u/Anthony_Eden)\
**Post date:** [November 25, 2006, 1:03pm UTC](https://discuss.rubyonrails.org/t/cant-use-habtm-at-all/4952/9 "2006-11-25T13:03:25Z")

</div>

One other thing, you should add :id =\> false to create\_table:

create\_table "users\_props", :force =\> true, :id =\> false do |t| &nbsp;&nbsp;&nbsp;t.column "prop\_id", :integer &nbsp;&nbsp;&nbsp;t.column "user\_id", :integer end

HABTM join tables should not have an ID column.

V/r