Take the 2-minute tour ×

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?


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


id | name | matches
1    goal1  2
2    goal2  3
share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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(
                           'count(*) AS matches'
$options['joins'] = 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);
share|improve this answer
add comment

mysql should be that:

Select goal.id,goal.name,Count(*) From goal RIGHT JOIN tasks on goal.id=tasks.goal_id Group by goal.id

CakePHP, can't tell you without testing...

share|improve this answer
add comment

Your Answer


