Need help with hasAndBelongsToMany (HABTM) Cakephp find

Question

Hello, I am trying to query a hasAndBelongsToMany relationship in Cakephp 1.3, but it looks like the SQL query being run is not doing a Join on the many to many table.

I have a users table, projects table, and users_projects table. I want to get a list of all projects a user is associated with in a separate Allocations controller.

I have been reading the cakephp book on the topic: http://book.cakephp.org/view/1044/hasAndBelongsToMany-HABTM

So I have been trying this:

var_dump($this->Allocation->User->Project->find('list',array('conditions'=>array('Project.user_id'=>'21'))));

However that does not work, It returns nothing and this error:

Warning (512): SQL Error: 1054: Unknown column 'Projects.user_id' in 'where clause' [CORE/cake/libs/model/datasources/dbo_source.php, line 681]

Query: SELECT `Project`.`id`, `Project`.`name` FROM `projects` AS `Project` WHERE `Projects`.`user_id` = '21' 

It looks like cakephp is not doing the required join.

This is in my projects model:

var $hasAndBelongsToMany = array(
    'User' => array(
        'className' => 'User',
        'joinTable' => 'users_projects',
        'foreignKey' => 'project_id',
        'associationForeignKey' => 'user_id',
        'unique' => true,
        'conditions' => '',
        'fields' => '',
        'order' => '',
        'limit' => '',
        'offset' => '',
        'finderQuery' => '',
        'deleteQuery' => '',
        'insertQuery' => ''
    )

And this is in my users model:

var $hasAndBelongsToMany = array(
    'Project' => array(
        'className' => 'Project',
        'joinTable' => 'users_projects',
        'foreignKey' => 'user_id',
        'associationForeignKey' => 'project_id',
        'unique' => true,
        'conditions' => '',
        'fields' => '',
        'order' => '',
        'limit' => '',
        'offset' => '',
        'finderQuery' => '',
        'deleteQuery' => '',
        'insertQuery' => ''
    )

So, what is the correct syntax to get the list of projects for a given user on a many to many relationship in cakephp?

Answer 1

You are getting this error because you are using the find type 'list'. Cake's default behaviour is to select the ID and NAME fields when using this find type.

Change your find type to 'all' to retrieve the results you are expecting:

var_dump($this->Allocation->User->Project->find('list',array('conditions'=>array('Project.user_id'=>'21'))));

Answer 2

Perhaps that's because you didn't make the two table names connected in alphabetical order.users_projects should be projects_users.