How to return a sum() from a contained Table?

Question

I have a custom finder method in my Matches table which contains some other tables and I would like to return the summed value for one of my contained fields.

So far I have the following.

/**
 * Custom finder to find the latest matches played
 *
 * @param Query $query
 * @param array $options
 * @return $this
 */
    public function findLatestMatches(Query $query, array $options) {
        return $query->contain([
                'Venues' => function ($q) {
                    return $q->select(['id', 'name', 'location']);
                },
                'Formats' => function ($q) {
                    return $q->select(['id', 'name']);
                },
                'Teams' => [
                    'Clubs' => function ($q) {
                        return $q->select(['id', 'image', 'image_dir']);
                    }
                ],
                'Innings' => [
                    'InningsTypes',
                    'Batsmen' => function ($q) {
                        /* @var \Cake\ORM\Query $q */
                        return $q->select(['totalRuns' => $q->func()->sum('runs')]);
                    },
                    'Bowlers',
                    'Wickets' => function ($q) {
                        /* @var \Cake\ORM\Query $q */
                        return $q->select(['totalWickets' => $q->func()->count('id')]);
                    }
                ]
            ])
            ->order(['when_played' => 'DESC']);
    }

This code will execute fine and produce no errors. Also checking the SQL tab in DebugKit shows the sql as having been executed.

SELECT (SUM(runs)) AS `totalRuns` FROM batsmen Batsmen WHERE Batsmen.innings_id in ('841fce60-0178-450f-99e8-ad1670f5c84f','93daddf5-256b-4420-b636-0db626baae72','b398d1a0-2c7d-41f7-b2c3-8ea00ddfcece','f949bb45-3d8b-46f5-8967-cc1340a9c1e7')

However the data isn't available in any of the returned entities. Where can I find my aggregated data?

Answer 1

I have since done a bit of refactoring with this and moved the Bowlers wickets out into their own custom finder, to use in this finder.

Here is how I've approached my Bowlers custom finder.

public function findBowlersWickets(Query $query, array $options)
{
    return $query->contain([
        'Innings' => [
            'Wickets' => [
                'PlayerBowledWicket',
            ]
        ]
    ])
        ->select(['totalWickets' => $query->func()->count('*')])
        ->matching('Innings.Wickets.PlayerBowledWicket', function ($q) {
            return $q->where([
                'AND' => [
                    'Wickets.bowler_player_id = Bowlers.player_id',
                    'Wickets.innings_id = Innings.id'
                ]
            ]);
        })
        ->group(['Innings.id', 'Bowlers.player_id'])
        ->order(['totalWickets' => 'DESC'])
        ->autoFields(true);
}