CakePHP Model Query - Complex - Multi Table - Sum - Count - Group By

Question

Hello and happy holidays everyone.

Recently I have been tasked with transforming a beta application from pure PHP/jQuery to CakePHP/ExtJS (Which I am new to).

My issue is with the most complex query that populates the main grid.

To keep things simple there are 3 tables with correct baked relationships and models: Projects, ToDo, ExtraToDo

Projects hasMany ToDo and ExtraToDo.

ToDo and ExtraToDo have columns Title and Complete.

My goal is to get a completion percent for each project based on these three tables. The way I have gone about this is the SUM of the Complete column divided by the COUNT of the Complete column. I am trying in a CakePHP way for readability/performance/otherstuffIdontknowyet.

Originally, in raw SQL I had done it like this:

SELECT
  `idProject`,

  (SELECT
    ROUND((SUM(`Complete`) / COUNT(`Complete`)) * 100),
   FROM
    (SELECT `Complete`, `ProjectID` FROM `ToDo`
      UNION ALL
     SELECT `Complete`, `ProjectID` FROM `ExtraToDo`) orders
   WHERE
    `ProjectID` = `idProject`
  ) AS 'Completion'

FROM 
  `Projects`

I also got this to work in the Kohana PHP MVC framework fairly easily which I tried before deciding on CakePHP. I LOOOVED how their queries were created...:

private function get_completion() {
  $Query = DB::select('ProjectID', array(DB::expr('ROUND((SUM(`Complete`) / COUNT(`Complete`)) * 100)'), 'Completion'))
    ->from(array('ToDo', 'ExtraToDo'))
    ->group_by('ProjectID');

  return $Query;
}

public function get_all() {
  $Query = DB::select()
    ->from('Projects')
    ->join(array(self::get_completion(), 'Completion'))
    ->on('projects.id', '=', 'Completion.ProjectID')
    ->execute()
    ->as_array();

  return $Query;      
}

Unfortunately I have completely struggled to get this working in CakePHP while doing it the CakePHP way.

I'm pretty sure virtualFields are the key to my answer but after reading the documents and trying x, y, AND z. I have been unable to comprehend them and how they relate.

Thank you in advance -T6

Answer 1

That is a lot of nested selects. IMO you would be better off building a better query.

This should get you going.

class Project extends AppModel {
    public $findMethods = array(
        'completion' => true
    );

    // other code

    protected function _findCompletion($state, $query, $results = array()) {
        if ($state == 'before') {
            $this->virtualFields['total'] = 'ROUND((SUM(Todo.Complete + TodoExtra.Complete) / (COUNT(Todo.Complete) + COUNT(TodoExtra.Complete))) * 100)';

            $query['fields'] = array(
                $this->alias . '.' . $this->primaryKey,
                'total'
            );

            $query['joins'] = array(
                array(  
                    'table' => 'todos', 
                    'alias' => 'Todo', 
                    'type' => 'left', 
                    'foreignKey' => false, 
                    'conditions'=> array('Todo.project_id = ' , $this->alias . '.' . $this->primaryKey) 
                ),
                array(  
                    'table' => 'todo_extras', 
                    'alias' => 'TodoExtra', 
                    'type' => 'left', 
                    'foreignKey' => false, 
                    'conditions'=> array('TodoExtra.project_id = ' . $this->alias . '.' . $this->primaryKey) 
                ),
            );

            $query['group'] = array(
                $this->alias . '.' . $this->primaryKey
            );
            return $query;
        }

        return $results;
    }

    // other code
}

Now you have a custom find method that can be used like find('first') or find('all').

From the controller:

$this->Project->find('completion');

Or in the Project model

$this->find('completion');

It should return something like this:

$results = array(
    0 => array(
        'Project' => array(
            'id' => 1,
            'total' => 50
        )
    ),
    1 => array(
        'Project' => array(
            'id' => 2,
            'total' => 75
        )
    )
);

Answer 2

I would suggest either creating an afterFind() function to the Project model class, or simply just adding a function that you would call when you need to perform this calculation.

The function to perform the calculation would look like:

getPercentageComplete($project){
{
$total_todos = count($project['ToDo']);
$completed_todos = 0;
foreach($project['ToDo'] as $todo){
   if($todo['Complete']) //assuming this is a boolean field
        $completed_todos++;
}
return $completed_todos / $total_todos;
}

Then, your afterFind would look something like this:

function afterFind(&$results)
{
  foreach ($results as &$project)
  {
   $project['Project']['percentageComplete'] = $this->Project->getPercentageComplete($project);
   }
return $results;
}

You can see more about afterFind() at the CakePHP Bakery - > Callback Methods