Rails Nested Joins - Rails 4

Question

I have 3 models.

class Saving
  has_many :contributions
end

class Contribution
  belongs_to :saving
  has_many :deposits
end

class Deposit
  belongs_to :contribution
end

Now I want to query all the savings and its contributions and calculate the sum of deposits.amount. I also want the records where there are no deposits also.

If i use

Saving.joins(contributions: [:deposit])

This will give me all savings which has contributions which has deposits. But I want joins on contributions and left_outer_joins on deposits.

How can I achieve this? I am using Rails 4. So I am unable to do

Saving.left_outer_joins(contributions: [:deposit]).

Does this answer your question? [LEFT OUTER JOIN in Rails 4](https://stackoverflow.com/questions/24358805/left-outer-join-in-rails-4) — Eyeslandic, Oct 01 '21 at 15:01

max · Accepted Answer · 2021-10-02T12:58:47.743

The most straigt forward way to do an OUTER join in legacy apps is by using a SQL string:

Saving.select(
        'savings.*',
        'SUM(deposits.amount) AS total_deposits'
      )
      .joins(:contributions)
      .joins(%q{
         LEFT OUTER JOINS depostits 
         ON depostits.contribution_id = contributions.id
      })

You can also (mis)use .includes as it does an outer join:

class Saving
  has_many :contributions
  has_many :deposits, through: :contributions
end

Saving.includes(:deposits)
      .references(:deposits) # forces a single query
      .joins(:contributions) # optional - creates an additional inner join
      .select(
        'savings.*',
        'SUM(deposits.amount) AS total_deposits'
      )

And you can also use Arel:

module LegacyOuterJoiner
  # clunky hack for outer joins in legacy apps.
  def arel_outer_joins(association_name, fkey = nil)
     raise "This method is obsolete! Use left_joins!" if respond_to?(:left_joins)
     other_model = reflect_on_association(association_name).klass
     right = other_model.arel_table
     fkey ||= "#{other_model.model_name.singular}_id".intern
     arel_table.joins(right, Arel::Nodes::OuterJoin)
       .on(arel_table[fkey].eq(right[:id])) 
       .join_sources
  end
end

class Deposit
  extend LegacyOuterJoiner
end

Saving.select(
        Saving.arel_table[Arel.star],
        Deposit.arel_table[:amount].sum.as('total_deposits')
      )
      .joins(:contributions)
      .joins(Deposit.arel_outer_joins(:contribution))

score 0 · Answer 2 · answered Jul 01 '22 at 10:24

If you can get your app to Rails 4.2 then just install the gem "brick" and it automatically adds the Rails 5.0 implementation of left_outer_joins. You would probably want to turn off the rest of its functionality, that is unless you want an automatic "admin panel" kind of thing available in your app!

Rails Nested Joins - Rails 4

2 Answers2