Forum: Ruby on Rails belongs_to and multiple foreign keys

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.
Daniel B. (Guest)
on 2005-12-14 18:45
(Received via mailing list)
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
Jeremy E. (Guest)
on 2005-12-14 19:00
(Received via mailing list)
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'
Chris H. (Guest)
on 2005-12-14 20:01
(Received via mailing list)
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
Edward OG (Guest)
on 2006-01-07 05:09
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!
Kevin O. (Guest)
on 2006-01-07 05:39
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.
Edward O. (Guest)
on 2006-01-07 06:23
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
This topic is locked and can not be replied to.