PSQL 查询对产生重复项的"has many through"关系上的列求和

Question

我的目标是生成一个包含每个客户的 ActiveRecordRelation 及其余额(sale_line_items.total + sale_adjustments.effect_to_balance - sale_payment.amount)

class Customer < ActiveRecord:Base
has_many :sales
class Sale < ActiveRecord:Base
belongs_to :customer
has_many :sale_adjustments
has_many :sale_payments
class SaleLineItem < ActiveRecord:Base
belongs_to :sale
class SalePayment < ActiveRecord:Base
belongs_to :sale
class SaleAdjustment < ActiveRecord:Base
belongs_to :sale 

我当前的代码：

customers = Customer.all
customers = customers.joins("LEFT OUTER JOIN sales ON customers.id = sales.customer_id")
customers = customers.joins("LEFT OUTER JOIN sale_line_items ON sale_line_items.sale_id = sales.id")
customers = customers.joins("LEFT OUTER JOIN sale_adjustments ON sale_adjustments.sale_id = sales.id")
customers = customers.joins("LEFT OUTER JOIN sale_payments ON sale_payments.sale_id = sales.id")
customers = customers.select("customers.*,
COALESCE(SUM(sale_line_items.total),0) +
COALESCE(SUM(sale_adjustments.effect_to_balance),0) -
COALESCE(SUM(sale_payments.amount),0) AS customer_balance")
customers.group("customers.id").distinct 

问题在于，如果有多个付款或调整数，则行项目在生成的表中重复，实质上使总数增加了该数字的系数。我认为我有足够的了解来识别问题，但不足以提出解决方案。下面列出了轨道生成的查询。

[1m[36mCustomer Load (2.2ms)[0m  [1mSELECT DISTINCT customers.*,
COALESCE(SUM(sale_line_items.total),0) +
COALESCE(SUM(sale_adjustments.effect_to_balance),0) -
COALESCE(SUM(sale_payments.amount),0) AS customer_balance 
FROM "customers" LEFT OUTER JOIN sales ON customers.id = sales.customer_id
LEFT OUTER JOIN sale_line_items ON sale_line_items.sale_id = sales.id
LEFT OUTER JOIN sale_adjustments ON sale_adjustments.sale_id = sales.id
LEFT OUTER JOIN sale_payments ON sale_payments.sale_id = sales.id 
WHERE "customers"."business_id" = $1 GROUP BY customers.id[0m  [["business_id", "bd0c474c-db6e-43bc-95ca-90541d3840d1"]]

该错误的一个示例是： 有一笔销售的客户有一个 $50 的行项目

total sales: $50, balance: $50

添加一笔 10 美元的付款

total sales: $50, balance: $40

第二次付款时出现问题，假设 1 美元

total sales $100, balance $89

这笔额外付款会为订单项创建一个重复的行。第三次付款将达到 150 美元，依此类推......

非常感谢任何帮助 - 我整晚都在为此工作，在这一点上，我只是在兜圈子。

Answer 1

我在计算 PHP-MySQL 项目的发票余额时也做过类似的事情。我最终更改了数据库结构和应用程序流程。我修改了发票表以包含invoice_total和total_payment列，这些列将通过发票和付款模块的创建和版本进行更新。

尽管您可以使用子查询来获得所需的结果，例如：

SELECT DISTINCT customers.*,
COALESCE(SUM(sli.total),0) +
COALESCE(SUM(sa.effect_to_balance),0) -
COALESCE(SUM(sp.amount),0) AS customer_balance 
FROM "customers" LEFT OUTER JOIN sales ON customers.id = sales.customer_id
LEFT OUTER JOIN (SELECT sale_id, SUM(total) total FROM sale_line_items) sli ON sli.sale_id = sales.id
LEFT OUTER JOIN (SELECT sale_id, SUM(effect_to_balance) effect_to_balance FROM sale_adjustments) sa ON sa.sale_id = sales.id
LEFT OUTER JOIN (SELECT sale_id, SUM(amount) amountFROM sale_payments) sp ON sp.sale_id = sales.id 
WHERE "customers"."business_id" = $1 GROUP BY customers.id

希望这将解决您的问题:)

Answer 2

rails代码以Manoj Monga的答案为模型：

customers = Customer.all
customers = customers.joins("LEFT OUTER JOIN sales ON customers.id = sales.customer_id")
customers = customers.joins("LEFT OUTER JOIN (SELECT sale_id, SUM(total) total FROM sale_line_items GROUP BY sale_id) sli ON sli.sale_id = sales.id")
customers = customers.joins("LEFT OUTER JOIN (SELECT sale_id, SUM(effect_to_balance) effect_to_balance FROM sale_adjustments GROUP BY sale_id) sa ON sa.sale_id = sales.id")
customers = customers.joins("LEFT OUTER JOIN (SELECT sale_id, SUM(amount) amount FROM sale_payments GROUP BY sale_id) sp ON sp.sale_id = sales.id")
customers = customers.select("customers.*,
COALESCE(SUM(total), 0) as sales_total,
COALESCE(SUM(total), 0) + 
COALESCE(SUM(effect_to_balance),0) - 
COALESCE(SUM(amount),0) AS customer_balance")
customers.group("customers.id").distinct