Query Builder:在关联数据上具有多个sum的子查询

Question

我想请求建议使用查询生成器从关联模型中获取多个总和的方法。

有三个表:

invoices    invoice_items                               payment_receipts
--------    -------------                               -------------
id | name   id| invoice_id  | invoice_qty   unit_price  id| invoice_id  | receipt_amount
===|======  ==========================================  ================================    
1 |INV01   1| 1            | 1300          |12.00      1 | 1           | 1000
2 |INV02   2| 1            | 2600          |9.00       2 | 1           | 2000
3 |INV03   3| 2            | 1100          |15.00      3 | 3           | 900
4| 3            | 900           |12:00

对于每张发票，我想要项目的总金额(数量*价格)和付款收据的总和。

这个查询(与子查询)正确地得到我正在寻找的结果:

SELECT Invoices.id, Invoices.invoice_name, InvoiceItemSum.SumOfAmount, PaymentSum.SumOfPaymentAmount
FROM Invoices
INNER JOIN (
SELECT invoice_id, SUM(Invoice_items.invoice_qty * Invoice_items.unit_price) AS SumOfAmount
FROM Invoice_items
GROUP BY Invoice_id
) InvoiceItemSum ON InvoiceItemSum.Invoice_id = Invoices.id
LEFT JOIN (
SELECT Invoice_id, SUM(Payment_receipts.receipt_amount) AS SumOfPaymentAmount
FROM Payment_receipts
GROUP BY Invoice_id
) PaymentSum ON PaymentSum.Invoice_id = Invoices.id
WHERE Invoices.invoice_id = 33

我可以直接在我的CakePhp应用程序中执行这个查询并获得我需要的结果，所以它是这样工作的。

然而，我想建议一个更优雅的CakePHP方式，通过查询生成器做到这一点。

我已经试过了:

$query = $this->Invoices->find()->contain(['InvoiceItems', 'PaymentReceipts']);
$query->select([
'Invoices.id',
'Invoices.invoice_name',
]);
$query->select([
'total_inv_amt' => $query->func()->sum('InvoiceItems.invoice_qty * InvoiceItems.unit_price'),
'total_paid_amt' => $query->func()->sum('PaymentReceipts.receipt_amount')
])
->innerJoinWith('InvoiceItems')
->leftJoinWith('PaymentReceipts')
->group(['Invoices.id']);
$query->where(['Invoices.id' => 33]);

但是，通过创建以下查询，这两个总和将翻倍:

SELECT 
Invoices.id AS Invoices__id, 
Invoices.invoice_name AS Invoices__invoice_name, 
(
SUM(
InvoiceItems.invoice_qty * InvoiceItems.unit_price
)
) AS total_inv_amt, 
(
SUM(PaymentReceipts.receipt_amount)
) AS total_paid_amt 
FROM 
invoices Invoices 
INNER JOIN invoice_items InvoiceItems ON Invoices.id = (InvoiceItems.invoice_id) 
LEFT JOIN payment_receipts PaymentReceipts ON Invoices.id = (PaymentReceipts.invoice_id) 
WHERE 
Invoices.id = 33 
GROUP BY 
Invoices.id

我尝试了以下的子查询文档无数不成功的结果。我也玩过连接，但仍然没有骰子。

我的问题是:使用查询生成器编写此查询的好方法是什么?

提前感谢您的任何建议!

Answer 1

您可以通过cakephp JOIN轻松地做到这一点。检查一下这个解决方案，它可能对你有帮助。直接在编辑器中编写，不保证没有语法错误!

$this->Invoices->find()
->select([
'Invoices.id',
'Invoices.invoice_name',
'total_inv_amt' => $query->func()->sum('InvoiceItems.invoice_qty * InvoiceItems.unit_price'),
'total_paid_amt' => $query->func()->sum('PaymentReceipts.receipt_amount')
])
->join([
'table' => 'InvoiceItems',
'alias' => 'InvoiceItems',
'type' => 'Inner',
'conditions' => [
// your condition for InvoiceItems
],
])
->join([
'table' => 'PaymentReceipts',
'alias' => 'PaymentReceipts',
'type' => 'LEFT',
'conditions' => [
// condition for PaymentReceipts
],
])

Answer 2

好的，最后在审查Alimon的回答和其他几个关于子查询的问题，如这个和这个之后，我已经达到了正确的查询生成器解决方案。

$subquery_a = $this->Invoices->InvoiceItems->find('all');
$subquery_a
->select(['totalinvoiceamt' => $subquery_a->func()->sum('invoice_qty * unit_price') ])
->where([
'InvoiceItems.invoice_id = Invoices.id'
]);

$subquery_b = $this->Invoices->PaymentReceipts->find('all');
$subquery_b
->select(['totalpaymentamt' => $subquery_b->func()->sum('receipt_amount') ])
->where([
'PaymentReceipts.invoice_id = Invoices.id'
]);
$query = $this->Invoices->find('all')
->select([
'Invoices.id',
'Invoices.invoice_name',
'InvoiceItems__total_invoice_amount' => $subquery_a,
'PaymentReceipts__total_payments_amount' => $subquery_b
])
->join([
[
'table'     => 'invoice_items',
'alias'     => 'InvoiceItems',
'type'      => 'INNER',
'conditions'=> [
'Invoices.id = InvoiceItems.invoice_id'
]
]
])
->join([
[
'table'     => 'payment_receipts',
'alias'     => 'PaymentReceipts',
'type'      => 'LEFT',
'conditions'=> [
'Invoices.id = PaymentReceipts.invoice_id'
]
]
])
->group('InvoiceItems.invoice_id');
$query->where(['Invoices.id' => 33]);

结果与直接查询相同，尽管SQL看起来与手动查询有点不同，但结果是相同的。

感谢Alimon等人的协助。