CakePHP Find with HABTM using only the Join Table

Question

I have a Goal model that HasAndBelongsToMany Users. This HABTM is named Participant. When I try to find all the Participants of a goal, the join table for this HABTM is not being used. Here is the related code in the goal model.

class Goal extends AppModel {
    var $hasAndBelongsToMany = array(
    'Participant' => array(
        'className' => 'User',
        'joinTable' => 'goal_participants',
        'foreignKey' => 'goal_id',
        'associationForeignKey' => 'user_id',
        'unique' => true,
        'conditions' => '',
        'fields' => '',
        'order' => '',
        'limit' => '',
        'offset' => '',
        'finderQuery' => '',
        'deleteQuery' => '',
        'insertQuery' => ''
    ));

    function getParticipantIDs($goalID) {
        $this->bindModel(array('hasOne' => array('Participant')));
        return $this->find('list', array(
            'fields' => array('Participant.user_id'),
            'conditions' => array('Participant.goal_id' => $goalID)
        ));
    }
}

I am binding the Participant as hasOne so that it will create a join in the query, but I get the following error:

Warning (512): SQL Error: 1054: Unknown column 'Participant.user_id' in 'field list' [CORE\cake\libs\model\datasources\dbo_source.php, line 525]
Query: SELECT `Participant`.`id`, `Participant`.`user_id` FROM `users` AS `Participant`   WHERE `Participant`.`goal_id` = '19' AND `Participant`.`status` != 2

G.J G.J · Accepted Answer · 2012-02-23T13:43:31

I edited the answer after OP's comment.

If i understand well what you intend to do, this piece of code might help :

function getParticipantIDs($goalID) {
    $participants = $this->GoalParticipant->find('list', array(
        'fields' => array('user_id'),
        'conditions' => array('goal_id' => $goalID)
    ));
    return array_values($participants);
}

I'm not 100% sure GoalParticipant is the correct syntax. I'm pretty sure that if the join table was named goals_participants, the correct syntax would be GoalsParticipant but as it's named goal_participants I guess it might be GoalParticipant.

CakePHP Find with HABTM using only the Join Table

1 Answers