Joins in rails between three tables

Question

I have three tables parents, children and fundings.

parent.rb

class Parent < ApplicationRecord
  has_many :children, dependent: :destroy
end

child.rb

class Parent < ApplicationRecord
  belongs_to :parent
  has_many :fundings, dependent: :destroy
end

funding.rb

class Funding < ApplicationRecord
      belongs_to :child
end

Joins between children and fundings

create_table "children_fundings", id: false, force: :cascade do |t|
    t.integer "child_id", null: false
    t.integer "funding_id", null: false
    t.index ["child_id", "funding_id"], name: 
    "index_children_fundings_on_child_id_and_funding_id"
    t.index ["funding_id", "child_id"], name: 
    "index_children_fundings_on_funding_id_and_child_id"
end

join between children and parents

  create_table "children_parents", id: false, force: :cascade do |t|
    t.integer "parent_id", null: false
    t.integer "child_id", null: false
    t.index ["child_id", "parent_id"], name: 
    "index_children_parents_on_child_id_and_parent_id"
    t.index ["parent_id", "child_id"], name: 
    "index_children_parents_on_parent_id_and_child_id"
  end

children table has parent_id, fundings table has child_id. How can I create a join between parents children and fundings table. Please help

SRack · Accepted Answer · 2018-06-12T15:39:50.007

You don't need join tables here - rather a parent ID column on the child record. So, in your case:

Child needs an integer column parent_id
Funding needs an integer column child_id

Join tables only come into play when you're implementing a has_and_belongs_to_many or has_many through relationship.

If you think about how you tie the records together, for a child to belong to a parent, the child merely needs to know which parent its tied to, hence the column.

Now, imagine parents had many children, children had many parents: a single parent ID wouldn't cut it, so the join table comes in to tie the two together, containing (for example) both parent_id and child_id in each row of data.

The database ties the records together using these methods, querying the id or join table as needed.

To access the fundings from the parent record, you can either link the two together if their independently related, or access via the children if not.

For the latter, you can use something like the following.

In your controller:

@parents = Parent.includes(children: :fundings) # includes preloads the data, avoiding expensive N + 1 queries

In your view:

<% @parents.each do |parent| %>
  <#% whatever you want to display regarding the parent here %>

  <% parent.children.each do |child| %>
    <#% whatever you want to display regarding the child here %>

    <% child.fundings.each do |funding| %>
      <#% whatever you want to display regarding the funding here %>
    <% end %>
  <% end %>
<% end %>

That's a little messy, so it would be worth separating the data in the controller or partials as suits. Hope that gives you an idea of how to work this though?

child already has a parent_id and funding already has a child_id. I need this join table to view fundings table in parent view page — , Jun 12 '18 at 15:33
What `fundings` do you need to view? A parent's children's fundings? — SRack, Jun 12 '18 at 15:34
Thanks Navroop - in that case, this updated answer should work (@widjajayd also makes a good point about using as has many through shortcut). Let me know how you get on, or if you have any other questions! — SRack, Jun 12 '18 at 17:03

score 0 · Answer 2 · answered Jun 12 '18 at 15:49

if you need to access fundings from parent you can use has_many through with declaration in rails as follows:

parent.rb

class Parent < ApplicationRecord
  has_many :children, dependent: :destroy
  has_many :fundings, through: :children
end

child.rb

class Child < ApplicationRecord
  belongs_to :parent
  has_many :fundings, dependent: :destroy
end

funding.rb

class Funding < ApplicationRecord
  belongs_to :child
end

you can access funding record through parent with @parent.fundings

here is reference link for has_many through

Joins in rails between three tables

2 Answers2