CakePHP - 另一个关系表中的SUM数据

Question

i have no idea in getting a result for sum of hours that staff has been attend to courses and assembly. Here are my database:-

staffs
+---+-------+-----------+
| id| Name  | Department|
+---+-------+-----------+
| 1 | Joe   | HR        |
| 2 | Andra | Support   |
| 3 | Khan  | HR        |
| 4 | Toll  | HR        |
| 5 | Gosh  | Support   |
| 6 | Rama  | Support   |
+---+-------+-----------+



attendances
+---+---------+-----------+--------------+
|id | name_id | course_id | assembly_id  |
+---+---------+-----------+--------------+
| 1 |    1    |  101      |              |
| 2 |    1    |  102      |              |
| 3 |    1    |           | 501          |
| 4 |    2    |  102      |              |
| 5 |    3    |           | 502          |
| 6 |    3    |  103      |              |
| 7 |    4    |  101      |              |
| 8 |    4    |           | 501          |
| 9 |    4    |  102      |              |
+---+---------+-----------+--------------+    


courses
+-------+-----------+-------+
| id    | name      | hours |
+-------+-----------+-------+
| 101   | courses_1 | 8     |
| 102   | courses_2 | 6     |
| 103   | courses_3 | 9     |
+-------+-----------+-------+


assembly
+-------+---------------+-------+
| id    | name          | hours |
+-------+---------------+-------+
| 501   | assembly_1    | 2     |
| 502   | assembly_2    | 4     |
+-------+---------------+-------+

In Model > Attendance i already add a relation:-

public $belongsTo = array('Staff','Course','Assembly'); 

In my Controller i try write this code:-

$view = $this->Staff->find('all',array(
'fields' => array('SUM(COALESCE(Course.hours,0))+SUM(COALESCE(Assembly.hours,0)) as ec_count'),'recursive'=>2));
$this->set('view', $view); 

But it will return an errors..(Seem like i do some wrong here)

How to write a codes with this condition and it will appears through a view like this:-

+---+-------+-----------+------------+
| id| Name  | Attend    | Total Hour |
+---+-------+-----------+------------+
| 1 | Joe   | course_1  |      16    |
|   |       | course_2  |            |
|   |       | assembly_1|            |
| 2 | Andra | course_2  |       6    |
| 3 | Khan  | assembly_2|      13    |
|   |       | course_3  |            |
| 4 | Khan  | course_1  |      16    |
|   |       | assembly_1|            |
|   |       | course_2  |            |
| 5 | Gosh  | -         |      0     |
| 6 | Rama  | -         |      0     |
+---+-------+-----------+------------+

Answer 1

Unless you're doing Joins (see CakePHP Joins), you will not be able to get the SUMs like that, as CakePHP will actually run individual queries, then combine and return the data.

I think you're best bet would be to keep track of the total hours in Staff.course_hours and Staff.assembly_hours fields within the 'staffs' table. You can still keep the individual rows in the Assembly.hours and Course.hours, but then on an afterSave() (see CakePHP afterSave) callback (or something similar), update the Staff hours fields.

Then, you can just set up a virtual field (see CakePHP Virtual Fields) called total_hours that you can sort by, or display, or use for anything you need.

OR, you could just build out the Joins and do it in a single (though slightly more complex) query.