First off, it really depends on your cake version. I'm assuming you're
on 1.2 beta or above:
Try something like this:
function getHours($id = null) {
$this->recursive = 0;
if ($id === null) {
$id = $this->id;
}
$this->unbindModel(array('belongsTo' => array('Client', 'User')));
$this->bindModel(array('hasOne' => array(
'Milestone' => array(),
'Task' => array('foreignKey' => false, 'conditions' =>
'Task.milestone_id=Milestone.id',
'Hour' => array('foreignKey' => false, 'conditions' =>
'Hour.task_id=Task.id',
))));
$conditions = array('Project.id' => $id, '1=1 GROUP BY Project.id');
$fields = array('SUM(`Hour`.`time`)');
$result = $this->find('first', compact('fields', 'conditions'));
return $result['SUM(`Hour`.`time`)'];
}
On Jan 15, 3:31 pm, kristofer <[EMAIL PROTECTED]> wrote:
> The main model is a Project. A Project hasMany Milestone. A Milestone
> hasMany Task. A Task hasMany Hour.
>
> I'm hoping to use Model::find() to build this query (or something like
> it..):
> ====
> SELECT `Project`.`id`,
> SUM(`Hour`.`time`)
>
> FROM `projects` AS `Project`
>
> LEFT JOIN `milestones` AS `Milestone`
> ON `Milestone`.`project_id` = `Project`.`id`
>
> LEFT JOIN `tasks` AS `Task`
> ON `Task`.`milestone_id` = `Milestone`.`id`
>
> LEFT JOIN `hours` AS `Hour`
> ON `Hour`.`task_id` = `Task`.`id`
>
> WHERE 1 = 1
> GROUP BY `Project`.`id`;
> ====
>
> Here's the function in the Project model I'm using to try building the
> query.. if I comment out the $fields[] = 'SUM(..)' line it pulls all
> the model data recursively.. but it does it with a separate query per
> table instead of using joins.
>
> ====
> function getHours($id = null) {
> $this->recursive = 3;
> if($id == null)
> $id = $this->id;
>
> $this->unbindModel(array('belongsTo' => array('Client',
> 'User')));
> $fields = null;
> $conditions = array('`Project`.`id`' => $id);
> $fields[] = 'SUM(`Hour`.`time`)';
> $result = $this->find($conditions, $fields);
>
> return $result['Project']['SUM(`Hour`.`time`)'];
> }
> ====
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
