Rails Postgres many to many relationship with custom foreign key

Question

I have a fact table, clients with a bunch of businesses:

  bus_id, sales,  date  
    1,    $986,  1/1/2016  
    1,    $543,  1/2/2016  
    2,    $921,  1/1/2016  
    2,    $345,  1/2/2016

I want to create a table opportunities

  bus_id,  opportunity  
     1,     "Upsell"  
     1,    "Upsell More"

How do I create the opportunities table so that I could display the opportunities per bus_id?

You want to do a many to many relation with clients table and opportunities table? — Kumar
– Kumar, Commented Jun 19, 2016 at 17:03
Only the primary key of one table can be used as a foreign key of another table. So I guess you have to take a different approach for what you want to achieve. Tell us what exactly you want to do. — Kumar
– Kumar, Commented Jun 19, 2016 at 17:10
Edited question. I just want to show the opportunities per bus_id in a view. So, bus_id 1 would show the "Upsell" and "Upsell More" rows. — GavinBelson
– GavinBelson, Commented Jun 19, 2016 at 17:20

blnc · Accepted Answer · 2016-06-20 05:00:42Z

1

Here is the migration command:

bin/rails g model opportunity custom_foreign_bus_id:integer:index description

businesses.rb

has_many :opportunities, foreign_key: :custom_foreign_bus_id

opportunity.rb

belongs_to :business, foreign_key: :custom_foreign_bus_id

Then to get the Business Opportunities:

Business.find(1).opportunities

or simply:

Opportunity.where(custom_foreign_bus_id: 1)

To do a many-to-many you need a has_and_belongs_to_many association or a join model, is that what you really want?

2.8 Choosing Between has_many :through and has_and_belongs_to_many

answered Jun 20, 2016 at 5:00

blnc

4,4141 gold badge30 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

GavinBelson Over a year ago

So, in your solution, the :custom_foreign_bus_id isn't a primary key in any table? And that is ok?

blnc Over a year ago

:custom_foreign_bus_id lives on the opportunity.rb model, it does not exist anywhere else. Declaring ..., foreign_key: :custom_foreign_bus_id will tell rails how to associate that custom foreign_key to the real primary key name.

GavinBelson Over a year ago

In your opinion, it is also ok to switch the has_many and belongs_to to has_and_belongs to many as ideally this would be a many to many relationship?

blnc Over a year ago

You can do that, you would just need to create another table to do so and do some slightly different settings. If you make another question I'll go into detail on how that works, just link me to it in the comments here.

GavinBelson Over a year ago

Sounds good @blnc Here is the habtm question stackoverflow.com/questions/37953235/…

Collectives™ on Stack Overflow

Rails Postgres many to many relationship with custom foreign key

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related