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.
Nate C. (Guest)
on 2006-05-24 01: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?
Brian H. (Guest)
on 2006-05-24 01: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
Riko (Guest)
on 2006-05-24 01:42
(Received via mailing list)
Nate C. 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/     |
Nate C. (Guest)
on 2006-05-24 01: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
unknown (Guest)
on 2006-05-24 02: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
Nate C. (Guest)
on 2006-05-24 03: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.
Mathias W. (Guest)
on 2006-05-26 20:27
(Received via mailing list)
On 5/23/06, Brian H. <removed_email_address@domain.invalid> 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.
Mathias W. (Guest)
on 2006-05-26 20:30
(Received via mailing list)
On 5/26/06, Mathias W. <removed_email_address@domain.invalid> 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.