active record recursive many-to-many association

Question

I want to set up a many-to-many recursive association with the model Projects

a Project has_many Comps (or comparables), which are other Projects

and a Project can be the Comp of many Projects

I'd like to add other columns to each record, so I need a join table

I've done the following research which has not helped:

1. This article is a clear basic explanation of sql recursive associations, but there is no specifics on activerecord implementation
2. this stack overflow article deals with a one-to-many relationship, and not a many-to-many
3. I tried this rails method of setting up what seems to be multiple join tables, but it is confusing and did not work for me.
4. I tried this rails method and it did not work for me, maybe because it assigns primary keys to two columns in a table, which I did not do

In the last link, here is the code in question:

CREATE TABLE tutorship ( tutor_id INTEGER, tutored_id INTEGER, hasPaid BOOLEAN, PRIMARY KEY (tutor_id, tutored_id) ); how can you have two primary keys in the same table? If that is correct, is this the issue, and how do i set this up?

Generally, how do i set this up in active record?

Answer 1

The term you are looking for seems to be "self referential many to many join". It's tricky to get right the first time definitely reccomend testing and retesting your associations in rails console as you build this out.

• rails many to many self join
• http://railscasts.com/episodes/163-self-referential-association?view=comments

```

class Project < ActiveRecord::Base

  has_many :comparables
  has_many :comps, :through => :comparables

  # rest of class omitted. 
end

class Comparables < ActiveRecord::Base
  belongs_to :project
  belongs_to :comp, :class_name => 'Project'
end

```

someone correct me if I err'd in the example above.

Answer 2

You can't have more than 1 primary key. By the very definition this doesn't make sense. You can have more than 1 unique index though. What you are describing is NOT recursive. It is a many to many relationship. I don't quite understand what you really want here because you have some code that talks about tutors but your original comment had to to with projects.

Let's say you have Projects. And any given project can have multiple comps. At the same time any given comp can be associated with more than 1 project. The way you do this is with a union table.

Something along these lines.

create table Projects
(
    ProjectID int,
    OtherColumnsHere
)

create table Comps
(
    CompID int,
    OtherColumnsHere
)

create table ProjectComps
(
    ProjectID int,
    CompID int,
    AuditingColumnsLikeDateCreatedHere
)