Query Builder: Subquery with multiple SUMs on associated data

Question

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!

Let me correct that. If there is only one invoice item, the results are correct. If there are more than one invoice item the total invoice amount is the SUM * 2, and the total payment amount is the SUM * the count of invoice items. — dividedbyzero, Sep 10 '21 at 08:16
Note that running either the InvoiceItems SUM or the PaymentReceipts SUM alone, both work correctly. It when we put both join queries together that it multiplies the totals. — dividedbyzero, Sep 10 '21 at 08:33

score 0 · Answer 1 · answered Sep 10 '21 at 04:01

You can easily do it by cakephp JOIN. Check this solution it may help you. Direct written in editor , not guaranty without syntax error !

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

Thanks for your reply. Actually this approach (with the addition of ->group(['Invoices.id'])) gets a result for each invoice, but the SUMs for both are a huge total, and the same value for each invoice.id. — dividedbyzero, Sep 10 '21 at 08:26
Based on the raw SQL which works, we need to get a SELECT inside the JOINS as subqueries.... I think. — dividedbyzero, Sep 10 '21 at 08:29

score 0 · Accepted Answer · answered Sep 15 '21 at 10:53

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.

Query Builder: Subquery with multiple SUMs on associated data

2 Answers2