join tables rails postgresql

Question

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>

Hi and welcome to Stack Overflow. Here we expect you to have a go at it yourself, then show us the code you have (even/especially if it's not working). ie we're not going to write your code for you, but if you have a go at it, we can show you how to adapt it to fit your needs... In this case it'd help if you showed us what you actually need (eg how you expect these to be linked)... perhaps show us some pseudo-code of how you'd use them once they're linked? But you definitely need to have a go at the linkage yourself first. — Taryn East
– Taryn East, Commented Aug 4, 2016 at 0:41
You might also find the Rails Guide on Associations to be helpful: guides.rubyonrails.org/association_basics.html To give you a good grounding in the ways that Active Records can be linked to one another. I suspect you may be interested in the parts on has_many :through — Taryn East
– Taryn East, Commented Aug 4, 2016 at 0:42
can you pls add more details about the relationships between Firm and Revenue? Is it a 1:1, 1:n or n:n relationship? What does unique_id refer to? — davideghz
– davideghz, Commented Aug 4, 2016 at 4:14
@TarynEast, thanks for your response. I looked at Rails Guide, Rails Cast, youtube and stackoverflow and couldn't find information to help me with my problem. Yes, I know that I can join the tables via different associations, but my questions is: how do I publish the joined table in my views? This is where I'm having a disconnect. For example, for the firm model, if I did a: has_many: revenues, through:users and did the same for revenue: has_many: firsms, through:users, how do I take this association and link the two in a one table? — richiepop2
– richiepop2, Commented Aug 4, 2016 at 13:12
@davideghz, there is a 1:1 relationship between firm to revenue, and revenue to firm. The unique_id is a an integer that can associate the various tables. For example, in the firm model, unique_id:1 will represent Company A, and in the revenue model, unique_id: 1 will represent Company A's revenue and profit. Hope this helps. — richiepop2
– richiepop2, Commented Aug 4, 2016 at 13:25

davideghz · Accepted Answer · 2016-08-04 18:18:14Z

2

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.

edited Aug 4, 2016 at 18:18

answered Aug 4, 2016 at 14:35

davideghz

3,6955 gold badges29 silver badges53 bronze badges

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

2 Comments

richiepop2 Over a year ago

thanks for your response, @davideghz. Would the revenue's firm_id then correspond to the firm's unique_id? At some point there has to be a common connection between the two tables, which is why I put a unique ID in each table. Am I understanding this correctly? Sorry for the naivete, and I appreciate your patience. I never built a multi-table site before. It might be also be important to note, that the user will not create data. All the tables' data will be uploaded and will not be updated or destroyed by the user.

davideghz Over a year ago

when you create a table associated to a Model, Rails will automatically create a column id which serves as primary key for the table. When you create a dependent Model (in this case Revenue) and you add t.belongs_to :firm, index: true in the migration, Rails will add a firm_id in revenues table which serves as foreign key. Note that I just edit my answer since there was an error in migrations :)

Collectives™ on Stack Overflow

join tables rails postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related