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]). 

CodePudding user response：

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))