I'm getting closer..
SELECT SUM(`Hour`.`time`), `Project`.`id`
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` AND `Task`.`project_id`
= `Project`.`id`)
LEFT JOIN `hours` AS `Hour`
ON (`Hour`.`task_id`=`Task`.`id` AND `Hour`.`project_id` =
`Project`.`id`)
WHERE `Project`.`id` = 1 LIMIT 1
Why is it giving me the "AND `ModelName`.`project_id` =
`Project`.`id`" for all the related models though? They're not
associated like that..
On Jan 15, 11:28 am, grigri <[EMAIL PROTECTED]> wrote:
> That will probably be :
>
> return $result[0]['SUM(`Hour`.`time`)'];
>
> at the end
>
> On Jan 15, 4:25 pm, grigri <[EMAIL PROTECTED]> wrote:
>
> > 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
-~----------~----~----~----~------~----~------~--~---
