Forum: Ruby on Rails has_and_belongs_to_many question

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
A06242f9d05b5925ef65e3fa5f52ad4b?d=identicon&s=25 Nate Constant (natcon67)
on 2006-05-23 23:12
Hello,
I am trying to make the "switchover" to programming my apps in Rails and
have a question about database structure.

In the past i would structure a many to many like this:

categories
----------------------------------------------------
|  id  |  name                         |
----------------------------------------------------
   1       Some Category
   2       Another Category
   3       One More Category



sub_categories
-----------------------------------------------------
|  id  |  name                         |  category_id  |
-----------------------------------------------------
   1       SubCategory One           1,2
   2       SubCategory Two           2
   3       SubCategory Three         1,2,3



is there a way for Active Record to handle this type of table linking or
would it be better for me to create the third table to link these
together?
7c4087d053eb02d099a17d91ba5e33b5?d=identicon&s=25 Brian Hughes (Guest)
on 2006-05-23 23:30
(Received via mailing list)
Um... Wow. I can honestly say I would have never thought to build a
many-to-many relationship with that kind of a table structure. In
fact, this is the first time I've ever seen, or heard, of someone
building a many-to-many relation without the use of a join table.

In Rails, you must use a join table (or a full-blown join model) if
you want to take advantage of the built in relationship management
features. So, if you have a simple many-to-many, between categories
and subcategories (I dropped the extra _ for neatness ;), you would
have:

categories
---------------------
|  id  |  name                         |
---------------------
    1       Some Category
    2       Another Category

sub_categories
---------------------
|  id  |  name                         |
---------------------
    1       SubCategory One
    2       SubCategory Two

categories_subcategories
------------------------
| category_id | subcategory_id |
------------------------
        1                  1
        1                  2
        2                  1

Notice how the join table is named, how the foreign key fields are
named, and also the fact that the join table has no "id" column of
its own. This is how you structure things so you can have:

class Category < ActiveRecord::Base
   has_and_belongs_to_many :subcategories
end

class Subcategory < ActiveRecord::Base
   has_and_belongs_to_many :categories
end

-Brian
47d626d416d9da060a44ed9f684e32cf?d=identicon&s=25 Riko (Guest)
on 2006-05-23 23:42
(Received via mailing list)
Nate Constant wrote:

> is there a way for Active Record to handle this type of table linking or
> would it be better for me to create the third table to link these
> together?
>

Have you tried to use the "acts_as_tree" specifier?

--
blog:  http://www.akropolix.net/rik0/blogs |
site:  http://www.akropolix.net/rik0/      |
forum: http://www.akropolix.net/forum/     |
A06242f9d05b5925ef65e3fa5f52ad4b?d=identicon&s=25 Nate Constant (natcon67)
on 2006-05-23 23:47
Brian,
I have used the relationship that you specified in the past but for many
to many relationships that are not *unlimited* i have used the way i
specified previously.  Specifically, in other apps i have used that way
to link code modules to user's roles like this:

roles
----------------------------------------------------
|  id  |  name                         |
----------------------------------------------------
   1       Some Role
   2       Another Role
   3       One More Role



modules
-----------------------------------------------------
|  id  |  name                   |  roles  |
-----------------------------------------------------
   1       Module One           1,2
   2       Module Two           2
   3       Module Three         1,2,3


I guess it would techinically be *better* to use the third table in this
situation but because the data in the table is not changing regularly
(or at all) then i tried to make it easier...  But that was WAAAYYY BACK
when i was using php!  LOL

thanks again for your response; i'll use the 3 table method so i can use
Active Record like it was intended.

Nate
D5145c421cd25af6fa577c15219add90?d=identicon&s=25 unknown (Guest)
on 2006-05-24 00:07
(Received via mailing list)
Well, I have come across that way of organising many-to-many
relationships and it's a real hit on performance once stuff get's
big... although you did say that the relationship is limited, so maybe
it wouldn't be. But arraying data like that just generally isn't a
good idea.
-N
A06242f9d05b5925ef65e3fa5f52ad4b?d=identicon&s=25 Nate Constant (natcon67)
on 2006-05-24 01:03
unknown wrote:
> Well, I have come across that way of organising many-to-many
> relationships and it's a real hit on performance once stuff get's
> big... although you did say that the relationship is limited, so maybe
> it wouldn't be. But arraying data like that just generally isn't a
> good idea.
> -N

Thank you for the advice.. until now, i have just created ways that
worked.. regardless if it is proper or not.
A7736d622f35f8725cc9a0c835d633a3?d=identicon&s=25 Mathias Wittlock (Guest)
on 2006-05-26 18:27
(Received via mailing list)
On 5/23/06, Brian Hughes <brianvh@alum.dartmouth.org> wrote:
> Notice how the join table is named, how the foreign key fields are
> named, and also the fact that the join table has no "id" column of
> its own. This is how you structure things so you can have:

I just ran into some issues which I believe is due to Rails havnig
added that id-column on its own. Is there any proper way to create
them or do I just have to use execute and create it myself in SQL?
Could I perhaps just run a remove_column after I've created it?

Thanks,
Mathias.
A7736d622f35f8725cc9a0c835d633a3?d=identicon&s=25 Mathias Wittlock (Guest)
on 2006-05-26 18:30
(Received via mailing list)
On 5/26/06, Mathias Wittlock <wittlock+rubyonrailslist@gmail.com> wrote:
> I just ran into some issues which I believe is due to Rails havnig
> added that id-column on its own. Is there any proper way to create
> them or do I just have to use execute and create it myself in SQL?
> Could I perhaps just run a remove_column after I've created it?
>
> Thanks,
> Mathias.

And as always I stumble upon the solution a few minutes after I posted
it to the mailing list.

create_table :table_name, :id => false do........

That did the trick. Thanks for listening. ;)

Mathias.
This topic is locked and can not be replied to.