为了通过两个＆QUOT总和的查询;的has_many＆QUOT;子表？ [英] Order a query by the sum of two "has_many" sub-tables?

问题描述

在我的应用程序，发票的has_many item_numbers 和发票的has_many支付。每个发票有一个平衡，这是ItemNumber量的总和属性，支付金额少的总和属性

In my app, Invoice has_many item_numbers and Invoice has_many payments. Each invoice has a balance, which is the sum of the ItemNumber amount attributes, less the sum of the Payment amount attributes.

的平衡是很容易计算出发票的模式，但我想写通过平衡排序发票查询，这是证明更难做的ActiveRecord / SQL。

The balance is very easy to calculate in the invoice model, but I am trying to write a query that sorts invoices by balance and this is proving much harder to do in ActiveRecord/SQL.

我成功设法订购item_numbers与下面的​​查询总数（感谢丹尼尔Rikowski）的发票：

I have successfully managed to order the invoices on the total of the item_numbers with the following query (thanks Daniel Rikowski):

Invoice.where(user_id: 1, deleted: false, status: 'Sent')
       .joins(:item_numbers)
       .select('invoices.*, sum(item_numbers.amount)')
       .group('invoices.id')
       .order('sum(item_numbers.amount) asc')
       .limit(20)

我试图延长本与下面的平衡命令;

I have tried to extend this to order by the balance with the following;

Invoice.where(user_id: 1, deleted: false, status: 'Sent')
       .joins(:item_numbers)
       .joins("FULL OUTER JOIN payments ON payments.invoice_id = invoices.id")
       .select("invoices.*, sum(item_numbers.amount_with_gst) - COALESCE(sum(payments.amount), 0)")
       .group("invoices.id")
       .order("sum(item_numbers.amount_with_gst) - COALESCE(sum(payments.amount), 0) #{dir}")/

有两个问题与此查询。首先，它的窘况丑陋，二，这是行不通的。我用在支付表的完整外连接，因为不是所有的发票有付款，如果我用刚刚加入（：付款）没有支付任何发票被排除的结果。聚结放在那里，以应对空额。

There are two problems with this query. First, it's horrendously ugly, and second, it doesn't work. I used the full outer join on the payments table as not all invoices have a payment and if I used just joins(:payments) any invoice without a payment was excluded from the results. The COALESCE was put there to deal with null amounts.

查询接近，但说，有3 item_numbers和1付款（一个pretty的典型场景），支付金额将被减去导致平衡比实际量要少得多（通常一个负的3倍平衡量）。

The query comes close, but say there are 3 item_numbers and 1 payment (a pretty typical scenario), the payment amount will be subtracted 3 times resulting in a balance much less than the actual amount (and usually a negative balance).

这可能是pretty的清楚如何走出我的深度我。我已经投入了大量的精力投入到这个查询（约4个小时的阅读和失败的尝试），并不能很钉子了。我的数据库是PostgreSQL的。

It's probably pretty clear how out of my depth I am. I've put a lot of effort into this query (about 4 hours of reading and failed attempts) and can't quite nail it. My database is PostgreSQL.

推荐答案

您的问题被列相乘所致。想象一下，有一个付款和属于发票3 Item_numbers。的常规连接，其结果将是这样的：

Your problem is caused by columns multiplying. Imagine having one Payment and three Item_numbers belonging to a Invoice. The result of a regular join would be something like this:

| invoice.id | item_number.amount | payment.amount |
| 1          | 4                  | 5              |
| 1          | 7                  | 5              |
| 1          | 2                  | 5              |

因此​​，总和（payment.amount）将返回15，而不是5。要得到正确的总和，你必须直接获取和：

Because of this, sum(payment.amount) will return 15 and not 5. To get the correct sum, you have to fetch the sum directly:

Invoice.select('invoices.id, (SELECT SUM(item_numbers.amount) from item_numbers WHERE item_numbers.invoice_id = invoices.id) - (SELECT COALESCE(SUM(payments.amount),0) from payments WHERE payments.invoice_id = invoices.id) AS balance').group('invoices.id')

这篇关于为了通过两个＆QUOT总和的查询;的has_many＆QUOT;子表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！