Cake 4.4, Mysql left join and sum?

Need Help
1

how can I do that with cake 4.4?

SELECT
  i.id,
  p.amount,
  pa.amount
FROM items AS i
  LEFT JOIN (SELECT
           id,
           item_id,
           SUM(amount)    amount
         FROM payments
         GROUP BY item_id) AS p
    ON p.item_id = i.id
  LEFT JOIN (SELECT
           payment_id,
           sUM(amount)    amount
         FROM payment_actions) AS pa
    ON pa.payment_id = p.id
group by i.id

thx for helping

2

My guess would something like

$query = $this->Items->find();
    ->select(['Items.id', 'payments_amount', 'payment_actions_amount'])
    ->leftJoinWith('Payments', function ($q) {
        return $q
            ->select([
               'Payments.id', 
               'Payments.item_id', 
               'payments_amount' => $q->func()
                    ->sum('Payments.amount'),
            ])
            ->group(['Payments.item_id']);
    })
    ->leftJoinWith('PaymentActions', function ($q) {
        return $q
            ->select([
               'PaymentActions.payment_id', 
               'payment_actions_amount' => $q->func()
                    ->sum('PaymentActions.amount'),
            ]);
    })
    ->group(['Items.id'])
    ->enableAutoFields();

This of course requires you to have your table classes properly configured so that e.g. ItemsTable has associations to both Payments and PaymentActions

I just wrote it down how I would think of it but there may be still some little bugs since I don’t have your DB schema.

But I hope you get the gist of it because as you can see with ->leftJoinWith() it basically behaves the same like ->contain() or ->matching() where the second param can be a callable to adjust the subquery however you like. Also see here and here