Search code examples
cakephpassociationsinner-joinquery-buildercakephp-3.x

Totals of a record in an associated table (sum ())

In Cakephp 3.8 calculate the sum of a column from an associated table. As in this SQL:

SELECT orders.id, orders.number, SUM(items.total_uncovered) FROM orders
INNER JOIN items ON orders.id = items.order_id
GROUP BY orders.id

I'm trying like this:

$orders = $this->Orders->find();
$orders->contain(['Items']);
$orders->select([
             'id',
             'number',
             'order_total_uncovered' => $orders
                 ->newExpr()
                 ->add($orders->func()
                 ->sum(new identifierExpression('Items.total_uncovered')))
         ]);
$orders->limit(20);

and I get the message:

"Unknown column 'Items.total_uncovered' in 'field list'"

Can someone help me?

Thank you in advance

Solution

  • Since you want to utilize SUM(), and I don't see any join tables, I'd assume that the Items association is hasMany.

    By default hasMany (and belongsToMany) associated data is being retrieved in a separate query when contained, hence the field won't show up in your main query. You'll have to explicitly join in the association instead, which can be done using the innerJoinWith() and leftJoinWith() methods.

    Also note that you don't need to wrap the function into another expression, and you can use a callback for select() to obtain a reference to the query, and last but not least, there's also an identifier() shorthand method for creating identifier expressions:

    $orders = $this->Orders
    ->find()
    ->select(function (\Cake\ORM\Query $query) {
        return [
            'Orders.id',
            'Orders.number',
            'order_total_uncovered' => $query->func()->sum(
                $query->identifier('Items.total_uncovered')
            )
        ];
    })
    ->innerJoinWith('Items')
    ->group('Orders.id')
    ->limit(20);

    See also