Sum of associated Fields

Need Help
1

I have a problem with 2 Tables, i need a sum of associated table

Table 1 projects:

id title
1 Project A
2 Project B

Table 2 jobs:

id project_id hours
1 1 3
2 1 2
3 1 1
4 2 4

Association in ProjectsTable:
$this->hasMany(‘Jobs’);

Expected output:
Project A : 6 hours
Project B : 4 hours

Can anyone help me? I tried a lot but it wont work.

2

Please share an example or two of what you tried, and how they failed. It’s often easier to fix something that’s close than to create it from scratch for you.

3

I tried something like this in many variations…

$qry = $this->find();
$qry->select([
‘title’,
‘hours’ => $query->func()->SUM(‘Jobs.hours’)
])
->group(‘Jobs.project_id’);

the result after $qry->all(), toArray, toList ist often Jobs.hours does not exist. i also tried to add contain.

4

Is this what you’re looking for?

Query

$queryJobs = TableRegistry::getTableLocator()->get('Jobs')->find();
$jobsByProject = $queryJobs
	->select(
		[
			'title' => 'Projects.title',
			'hours' => $queryJobs->func()->sum('Jobs.hours'),
		]
	)
	->innerJoinWith('Projects')
	->group('Jobs.project_id')
	->toArray();

Output

\Cake\Log\Log::debug(json_encode($jobsByProject));

[
  {
    "title": "Project A",
    "hours": 6
  },
  {
    "title": "Project B",
    "hours": 4
  }
]
5

Thanks Brent. Thats exactly what i am looking for. You made my day!