Belongs_to and multiple foreign keys


#1

Hi all,

I’m confused about how I’m supposed to specify a belongs_to relationship
when
the table includes multiple columns that reference the same parent
table.

In my case I have a “schedules” table with “opened_by” and “closed_by”
columns,
that both reference the “users” table. In theory, they can be two
different
user ids.

table schedules (

opened_by int,
closed_by int,
foreign key (closed_by) references users (id),
foreign key (opened_by) references users (id)
);

Do I simply specify multiple belongs_to?

Regards,

Dan


#2

On 12/14/05, Daniel B. removed_email_address@domain.invalid wrote:

table schedules (

opened_by int,
closed_by int,
foreign key (closed_by) references users (id),
foreign key (opened_by) references users (id)
);

Do I simply specify multiple belongs_to?

untested:

belongs_to :closer, :class_name=>‘User’, :foreign_key=>‘closed_by’
belongs_to :opener, :class_name=>‘User’, :foreign_key=>‘opened_by’


#3

the other half of this association is:

class User < ActiveRecord::Base
has_many :closed_schedules, :class_name => “Schedule”, :foreign_key =>
“closed_by” # schedules closed by user
has_many :open_schedules, :class_name => “Schedule”, :foreign_key =>
“opened_by” # schedules opened by user
end


#4

Sorry to resuscitate this post, but is it possible to do something with
a has_and_belongs_to_many relationship?

e.g. in this case, I want to be able to have the methods
Researcher.projects, Project.principal_investigator, and
Project.co_investigators

class Researcher < ActiveRecord::Base
has_and_belongs_to_many :projects
end

class Projects < ActiveRecord::Base
has_one :principal_investigator, :class_name => “Researcher”,
:foreign_key => “princ_inv” # A Project can only have one principal
investigator
has_many :co_investigator, :class_name => “Researcher”, :foreign_key
=> “co_inv” # Projects can have many co-investigators
end

Is this possible? What would the join table look like?

Thanks!


#5

Edward OG wrote:

Sorry to resuscitate this post, but is it possible to do something with
a has_and_belongs_to_many relationship?

e.g. in this case, I want to be able to have the methods
Researcher.projects, Project.principal_investigator, and
Project.co_investigators

class Researcher < ActiveRecord::Base
has_and_belongs_to_many :projects
end

class Projects < ActiveRecord::Base
has_one :principal_investigator, :class_name => “Researcher”,
:foreign_key => “princ_inv” # A Project can only have one principal
investigator
has_many :co_investigator, :class_name => “Researcher”, :foreign_key
=> “co_inv” # Projects can have many co-investigators
end

Looks to be an interesting problem you are tackling. Being a PI, I’d
love to hear more about your application.

Meanwhile…

In this particular case, I think you have your models set up
incorrectly.
In theory, PIs, co-investigators, and researchers are all people (or
users) who happen to have different roles. The role will be different
on different projects, perhaps you need something like…

class User < ActiveRecord::Base
has_many :collaborations
end

class Collaboration < ActiveRecord::Base
belongs_to :user
belongs_to :project
has_one :role
end

class Project < ActiveRecord::Base
has_many :collaborations
end

you could do this with a HABTM table with attributes, but this seems a
little more natural to me, and I can see the collaboration model
becoming more complex.


#6

Kevin O. wrote:

Edward OG wrote:

Sorry to resuscitate this post, but is it possible to do something with
a has_and_belongs_to_many relationship?

e.g. in this case, I want to be able to have the methods
Researcher.projects, Project.principal_investigator, and
Project.co_investigators

class Researcher < ActiveRecord::Base
has_and_belongs_to_many :projects
end

class Projects < ActiveRecord::Base
has_one :principal_investigator, :class_name => “Researcher”,
:foreign_key => “princ_inv” # A Project can only have one principal
investigator
has_many :co_investigator, :class_name => “Researcher”, :foreign_key
=> “co_inv” # Projects can have many co-investigators
end

Looks to be an interesting problem you are tackling. Being a PI, I’d
love to hear more about your application.

Meanwhile…

In this particular case, I think you have your models set up
incorrectly.
In theory, PIs, co-investigators, and researchers are all people (or
users) who happen to have different roles. The role will be different
on different projects, perhaps you need something like…

class User < ActiveRecord::Base
has_many :collaborations
end

class Collaboration < ActiveRecord::Base
belongs_to :user
belongs_to :project
has_one :role
end

class Project < ActiveRecord::Base
has_many :collaborations
end

you could do this with a HABTM table with attributes, but this seems a
little more natural to me, and I can see the collaboration model
becoming more complex.

Hi Kevin,

The application’s intention is to rework my university’s research
funding department’s database by making it easier to make queries and
reports. In its current MS Access form, it’s pretty ugly and there’s
already data corruption (missing or duplicated fields where there
shouldn’t be, etc.). I consider myself lucky that there’s isn’t too
much existing data to sift through and that my supervisor is open to
changing away from Access.

I’ll hopefully get permission to open source the code when I’m done, as
it might be a handy tool for some people.

I’m pretty sure you’re correct on the assumption that the relationship
model in the Access db was setup incorrectly, but now’s the time to
change over.

I like the way your idea for a schema would lay out. It certainly seems
much less complicated.

Brilliant. Thanks!

-Edward