How to return sum () from a table?
I have a custom lookup method in my table Matches
that 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 not throw errors. Also, checking the SQL bookmark in DebugKit shows sql as done.
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, no data is available in any of the returned objects. Where can I find my aggregated data?
+3
source to share
1 answer
Since then, I've refactored a bit and moved Bowlers wickets into my own custom search to use in that search.
This is how I approached my bowlers custom search.
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);
}
+2
source to share