时间戳

首页 > 解决方案 > Rails + Postgres 内部加入聚合问题

ruby-on-rails - Rails + Postgres 内部加入聚合问题

问题描述

这种类型的问题到处都是……但这是我的问题。

我有导轨模型：Organization, Activity, Donation, DonationItem. 我试图得到每个Activity.hours和的总和。DonationItem.amountOrganization

我不能使用直接关系（例如：Organization.activities.pluck(:hours).sum因为我处于Organization.select("...").group(...)必须使用 a 的情况。

当前的导轨连接如下，尽管在此阶段它们显然是不正确的：

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

这是SQL结构（基本上......）+选择位于：http: //sqlfiddle.com/# !17/fb932e/6

这是不希望查看 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
;

我明白问题是什么，只是不是解决它的方法......

任何帮助，将不胜感激。谢谢你。

标签： ruby-on-railspostgresqlinner-join

解决方案

在 SQL 中，您可以使用CTEor 子句来解决小时和金额值不正确的问题，但不确定如何在 rails 查询构建器中转换这些

使用 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
;

使用子条款

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
;

演示

推荐阅读