Complex queries in ActiveRecord

query_playground.rb

# Trying to re-create a JOIN on a nested SELECT query first in Arel, and then convert to Rails

#   SELECT campers.*, payments.total_paid
#   FROM campers
#   LEFT OUTER JOIN (
#     SELECT payments.source_id, SUM(payments.amount_cents) AS total_paid
#     FROM payments
#     WHERE payments.source_type = 'Camper' AND payments.state = 'paid'
#     GROUP by payments.source_id
#   ) payments
#   ON payments.source_id = campers.id
#   WHERE campers.state = 'confirmed';

# First using strictly Arel

campers      = Arel::Table.new :campers
payments     = Arel::Table.new :payments
payments_cte = Arel::Table.new :payments_cte

payments_sub_query = payments.project(payments[:source_id], payments[:amount_cents].sum.as('total_paid')).
  where(payments[:source_type].eq('Camper')).
  where(payments[:state].eq('paid')).
  group(payments[:source_id]).
  as('payments_cte')

payments_sub_query.to_sql
#=> (
#     SELECT "payments"."source_id", SUM("payments"."amount_cents") AS total_paid
#     FROM "payments"
#     WHERE "payments"."source_type" = 'Camper' AND "payments"."state" = 'paid'
#     GROUP BY "payments"."source_id"
#   ) payments_cte

camper_query = campers.project(campers[Arel.star], payments_cte[:total_paid]).
  join(payments_sub_query, Arel::Nodes::OuterJoin).
  on(payments_cte[:source_id].eq(campers[:id])).
  where(campers[:state].eq('confirmed'))

camper_query.to_sql
#=> SELECT "campers".*, "payments_cte"."total_paid"
#   FROM "campers"
#   LEFT OUTER JOIN (
#     SELECT "payments"."source_id", SUM("payments"."amount_cents") AS total_paid
#     FROM "payments"
#     WHERE "payments"."source_type" = 'Camper' AND "payments"."state" = 'paid'
#     GROUP BY "payments"."source_id"
#   ) payments_cte
#   ON "payments_cte"."source_id" = "campers"."id"
#   WHERE "campers"."state" = 'confirmed'

# Success! Now let's give it a try in Rails...

class Camper < ActiveRecord::Base
  has_many :payments, as: :source
  
  scope :by_last_name, -> { order :last_name, :first_name }
  scope :confirmed,    -> { where state: 'confirmed' }
  
  def self.with_total_paid
    payments_cte = Arel::Table.new :payments_cte
    join_on      = arel_table.create_on(payments_cte[:source_id].eq(arel_table[:id]))
    join         = arel_table.create_join(Payment.total_paid_join_table,
                                          join_on, Arel::Nodes::OuterJoin)
    
    joins(join).select([arel_table[Arel.star], payments_cte[:total_paid]])
  end
end

class Payment < ActiveRecord::Base
  belongs_to :source, polymorphic: true
  
  scope :paid, -> { where(state: 'paid') }
  
  def self.total_paid_join_table
    arel_table.where(arel_table[:state].eq('paid')).
      where(arel_table[:source_type].eq('Camper')).
      project(arel_table[:source_id],
              arel_table[:amount_cents].sum.as('total_paid')).
      group(arel_table[:source_id]).
      as('payments_cte')
  end
end

# Success!!!

Camper.with_total_payments.class
#=> ActiveRecord::Relation
# ... Chainable!

Camper.confirmed.with_total_payments.by_last_name.to_sql
#=> SELECT "campers".*, "payments_cte"."total_paid"
#   FROM "campers"
#   INNER JOIN (
#     SELECT "payments"."source_id", SUM("payments"."amount_cents") AS total_paid
#     FROM "payments"
#     WHERE "payments"."source_type" = 'Camper' AND "payments"."state" = 'paid'
#     GROUP BY "payments"."source_id"
#   ) payments_cte
#   ON "payments_cte"."source_id" = "campers"."id"
#   WHERE "campers"."state" = 'confirmed'
#   ORDER BY last_name, first_name