How can you count matches from a join action in CakePHP?

cakephp, join, mysql, php

Solution

MySQL query :

SELECT goal.id, goal.name, Count( * ) AS matches
FROM goal
RIGHT JOIN task ON goal.id = task.goal_id
GROUP BY goal.id

CakePHP : [if you have model with name Goal and Task]

$options['fields'] = array(
                           'Goal.id', 
                           'Goal.name', 
                           'count(*) AS matches'
                   );
$options['joins'] = array(
                              array(
                                 'table' => 'tasks',
                                 'alias' => 'Task',
                                 'type' => 'Right',
                                 'conditions' => array(
                                    'Goal.id = Task.goal_id'
                                 )
                              ) 
                    );
$options['group'] = array('Goal.id');
                           
$result = $this->Goal->find('all', $options);

Problem

I want to count to an extra field created, the matches from a join. Is this possible in CakePHP? I have an example of my data that I curently have. And how would a query look in mySQL for this type of result? Table:goal ``` id | name ----------- 1 Goal X 2 Goal Y ``` Table: tasks ``` id | name | goal_id ------------------- 1 task1 1 2 task2 1 3 task3 2 4 task4 2 5 task5 2 ``` Result ``` id | name | matches ------------------- 1 goal1 2 2 goal2 3 ```

Original source