我在我的项目中有一个复杂的查询，我在本地SQL中运行。
$r = $this->User->Project->query("
SELECT
Project.id,
Project.name,
Customer.name,
(SELECT SUM(hours)
FROM entries
WHERE project_id = Project.id
AND createdby = $id
AND deleted = 0
AND YEAR(entry_date) = YEAR(NOW())
AND DAYOFYEAR(entry_date) = DAYOFYEAR(NOW())) as TodayHours,
(SELECT SUM(hours)
FROM entries
WHERE project_id = Project.id
AND createdby = $id
AND deleted = 0
AND YEAR(entry_date) = YEAR(NOW())
AND WEEKOFYEAR(entry_date) = WEEKOFYEAR(NOW())) as WeekHours,
(SELECT SUM(hours)
FROM entries
WHERE project_id = Project.id
AND deleted = 0) as SpentHours,
Project.budget_hours as TotalHours
FROM projects as Project
INNER JOIN users_projects UserProject on UserProject.project_id = Project.id
AND UserProject.user_id = $id
INNER JOIN customers Customer on Customer.id = Project.customer_id
WHERE Project.deleted = 0
AND Project.archived = 0
");
当结果回来时，几乎一切看起来都很完美。唯一的问题是CakePHP将我的SUM列扔到它们自己的[0]组中。如果可能的话，我想把它们和Project放在一起。有什么办法可以把这些字段混叠起来吗?
Array
(
[0] => Array
(
[Project] => Array
(
[id] => 7
[name] => Some Project Name
[TotalHours] => 67.00
)
[Customer] => Array
(
[name] => Some Customer Name
)
[0] => Array
(
[TodayHours] => 2.25
[WeekHours] => 27.25
[SpentHours] => 27.25
)
)
)
抱歉回答得这么快…我希望它能帮助到其他有同样问题的人。这就是解决方案。你基本上必须为你的模型设置空的虚拟字段，然后用MyModel__VirtualFieldName别名列。之后就完美了!
@jose_zap是Twitter上的CakePHP核心开发人员，非常非常有帮助。再次感谢!//Add the virtual fields for this specific case
$this->User->Project->virtualFields += array(
'TodayHours' => 0,
'WeekHours' => 0,
'SpentHours' => 0
);
//Run the query
$projects = $this->User->Project->query("
SELECT
Project.id,
Project.name,
Customer.name,
(SELECT SUM(hours)
FROM entries
WHERE project_id = Project.id
AND createdby = $id
AND deleted = 0
AND YEAR(entry_date) = YEAR(NOW())
AND DAYOFYEAR(entry_date) = DAYOFYEAR(NOW())) as Project__TodayHours,
(SELECT SUM(hours)
FROM entries
WHERE project_id = Project.id
AND createdby = $id
AND deleted = 0
AND YEAR(entry_date) = YEAR(NOW())
AND WEEKOFYEAR(entry_date) = WEEKOFYEAR(NOW())) as Project__WeekHours,
(SELECT SUM(hours)
FROM entries
WHERE project_id = Project.id
AND deleted = 0) as Project__SpentHours,
Project.budget_hours as TotalHours
FROM projects as Project
INNER JOIN users_projects UserProject on UserProject.project_id = Project.id
AND UserProject.user_id = $id
INNER JOIN customers Customer on Customer.id = Project.customer_id
WHERE Project.deleted = 0
AND Project.archived = 0
");