CakePHP - MySQL query in Find method

Question

I have this MySQL query:

SELECT DISTINCT hours.project_id
          , projects.name
          , sum(hours.spent_hours) AS expr1
          , sum(hours.invoiced_hours) AS expr2
              , hours.description
FROM
  projects
INNER JOIN hours
ON projects.id = hours.project_id
GROUP BY
  hours.project_id
, projects.id
, projects.name
, hours.description

How would you put pieces in FIND method in CakePHP? I tried several times but without any luck...

I have found this in documentation:

array(
'conditions' => array('Model.field' => $thisValue), //array of conditions
'recursive' => 1, //int
'fields' => array('Model.field1', 'DISTINCT Model.field2'), //array of field names
'order' => array('Model.created', 'Model.field3 DESC'), //string or array defining order
'group' => array('Model.field'), //fields to GROUP BY
'limit' => n, //int
'page' => n, //int
'offset'=>n, //int   
'callbacks' => true //other possible values are false, 'before', 'after'

)

Other informations about databases:

Project hasMany Hours. Hours belongs to Project. In hours is project_id.

CakePHP 2.1.2.

Thank you! :)

Jérémie Parker · Accepted Answer · 2012-05-16 11:14:21Z

1

I assume you are in the Project Model for the following lines.

$result = $this->find(
    'all',
    array(
        'fields' => array(
            'Hour.projetc_id', 
            'Project.id', 
            'Project.name', 
            'SUM(Hour.spent_hours) AS expr1', 
            'SUM(Hour.invoiced_hours) AS expr2', 
            'Hour.description'
        ),
        'group' => array(
            'Hour.projetc_id',
            'Project.id',
            'Project.name',
            'Hour.description'
        )
    )
);

You may also want to have a look there to manage calculated fields in you array.

And lastly, I would recommend having a look at the Containable Behavior as it will speed up your DB query and allow to really get only what you need from the DB.

edited May 16, 2012 at 11:14

answered May 16, 2012 at 10:59

Jérémie Parker

3,1742 gold badges22 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

CakePHP - MySQL query in Find method

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related