Rails + Postgres Inner Join with aggregates issue

Question

This type of question is all over the place... but here's my issue.

I have rails models: Organization, Activity, Donation, DonationItem. I'm trying to get the summation of Activity.hours and DonationItem.amount for each Organization.

I can't use the straight relations (ex: Organization.activities.pluck(:hours).sum because I'm in a situation where a Organization.select("...").group(...) must be used.

Current rails joins are below, though they're obviously incorrect at this stage:

 @relation = Organization.joins(:activities, donations: :donation_items)
                         .select("organizations.id, sum(activities.hours), sum(donation_items.amount)")
                         .group("organizations.id")

This is the SQL structure (basically...) + select is located here: http://sqlfiddle.com/#!17/fb932e/6

Here's the select for peoples that wish to not view the SQL Fiddle

select 
        o.id,
        sum(a.hours) as "hours", 
        sum(di.amount) as "amount"
    from organizations o
    inner join activities a 
        on a.organization_id = o.id
    inner join donations d 
        on d.organization_id = o.id
    inner join donation_items di
        on di.donation_id = d.id
    group by o.id
;

I understand what the problem is, just not the solution to solving it...

Any help would be appreciated. Thank you.

Answer 1

In SQL you can use CTE or sub-clause to resolve issue of incorrect values for hours and amount, but not sure how would you transform these in rails query builder

Using CTE

with organization_activity as (
  select organization_id, sum(hours) as "hours"
  from activities
  group by organization_id 
)
select 
        o.id,
        a.hours as "hours", 
        sum(di.amount) as "amount"
    from organizations o
    inner join organization_activity a 
        on a.organization_id = o.id
    inner join donations d 
        on d.organization_id = o.id
    inner join donation_items di
        on di.donation_id = d.id
    group by o.id, a.hours
;

Using sub-clause

select 
        o.id,
        a.hours as "hours", 
        sum(di.amount) as "amount"
    from organizations o
    inner join (
      select organization_id, sum(hours) as "hours"
      from activities
      group by organization_id
    ) a 
        on a.organization_id = o.id
    inner join donations d 
        on d.organization_id = o.id
    inner join donation_items di
        on di.donation_id = d.id
    group by o.id, a.hours
;

DEMO