[FIXED] Query Builder: Subquery with multiple SUMs on associated data

Issue

I'd like to request advice for an approach to use the Query Builder to get multiple sums from associated models.

There are three tables:

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

For each invoice, I want the sum of the items' total amount (qty * price), and also the sum of payment receipts.

This query (with subqueries) correctly gets the result I'm looking for:

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

I can execute this query directly in my CakePhp app and get the results I need, so it works that way.

However I'd like advice on a more elegant CakePHP way to do this via the Query Builder.

I have tried this:

        $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]);

But this results in doubling the two sums via creating this query:

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

I've tried subqueries following the documentation with myriad unsuccessful results. I've also played with joins but still no dice.

My question is: what is a good approach to write this query using the Query Builder?

Thanks in advance for any advice!

Solution

OK, finally after reviewing Alimon's answer and several other questions on subqueries such as this and this, I've arrived at the correct Query Builder solution for this. Here it is:

        $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]);

The results are the same as the direct query, though the SQL looks a bit different from the manual one, but the results are identical.

Thanks Alimon et al for the assistance.

Answered By - dividedbyzero