richiepop2 richiepop2 - 4 months ago 23
Ruby Question

join tables rails postgresql

I have three models: user, firm and revenue. I'd like to join the firm and revenue models, in order to publish the joined model results. Could someone please point me in the right direction on how to go about joining these tables and publish the results? Note, firm and revenue model can be joined through a unique_id number. Here is some of my code:

Revenue Model

class Revenue < ActiveRecord::Base
belongs_to :user

def self.import(file)
CSV.foreach(file.path, headers: true) do |row|
Revenue.create! row.to_hash
end
end

end


Firm Model

class Firm < ActiveRecord::Base
belongs_to :user

def self.import(file)
CSV.foreach(file.path, headers: true) do |row|
Firm.create! row.to_hash
end
end

end


User Model

class User < ActiveRecord::Base

# Include default devise modules. Others available are:
# :confirmable, :lockable, :timeoutable and :omniauthable
before_save { self.email = email.downcase }

has_many :revenues
has_many :firms


devise :database_authenticatable, :registerable,
:recoverable, :rememberable, :trackable, :validatable,
:session_limitable, :confirmable

validates :name, :lastname, :industry, :company, :title, :address, :state, :city, :zip, presence: true
validates :phone, presence: true, length: { maximum: 11 }


end


Revenue DB

class CreateRevenues < ActiveRecord::Migration
def change
create_table :revenues do |t|
t.integer :unique_id
t.integer :revenue
t.integer :profit
t.references :user, index: true, foreign_key: true

t.timestamps null: false
end
end
end


Firm DB

class CreateFirms < ActiveRecord::Migration
def change
create_table :firms do |t|
t.integer :unique_id
t.string :name
t.string :state
t.string :city
t.references :user, index: true, foreign_key: true

t.timestamps null: false
end
end
end


View

<h2>Firm Data</h2>

<body>
<table>
<tr>
<th>unique_id</th>
<th>name</th>
<th>state</th>
<th>city</th>
</tr>

<body>
<% @firms.each do |firm| %>
<tr>
<td><%= firm.unique_id %> </td>
<td><%= firm.name %> </td>
<td><%= firm.state %> </td>
<td><%= firm.city %> </td>
<tr>
<% end %>
</table>
</body>

<h2>Revenue Data</h2>

<body>
<table>
<tr>
<th>unique_id</th>
<th>revenue</th>
<th>profit</th>
</tr>

<body>
<% @revenues.each do |rev| %>
<tr>
<td><%= rev.unique_id %> </td>
<td><%= rev.revenue %> </td>
<td><%= rev.profit %> </td>
<tr>
<% end %>
</table>
</body>

Answer

As per your question and comments it looks right to me to set up relationships as follow:

A user has_many firms (companies). A firm has_one revenue. A user has_many revenues through firms.

# app/models/user.rb
class User < ActiveRecord::Base
  has_many :firms
  has_many :revenues, through :firms
end

# app/models/firm.rb
class Firm < ActiveRecord::Base
  has_one :revenue
end

# app/models/revenue.rb
class Revenue < ActiveRecord::Base
  belongs_to :firm
end

Instead of storing a unique_id in both firms and revenues tables, it would be better to use a foreign_key in revenues, like firm_id.

The corresponding migrations are:

class CreateFirm < ActiveRecord::Migration
  def change
    create_table :firm do |t|
      t.string :name
      t.string :state
      t.string :city

      t.timestamps null: false
    end
  end
end

class CreateRevenue < ActiveRecord::Migration
  def change     
    create_table :firm do |t|
      t.belongs_to :firm, index: true
      t.integer :revenue
      t.integer :profit

      t.timestamps null: false
    end
  end
end

This will enable you to use, for example, firm.revenue.profit to display the value of profit in the app/views/firms/show.html.erb view.

Looking at your models and migrations syntax it seems you are not using Rails 5. You can find Rails 4.2 docs about the has_one relationship here.