Cakephp HABTM association conditions causes Column not found SQL error

Question

I have the problem in Cakephp that when I define

public $hasAndBelongsToMany = array(
    'User' => array(
        'className' => 'User',
        'joinTable' => 'groups_users',
        'foreignKey' => 'group_id',
        'associationForeignKey' => 'user_id',
        'unique' => 'keepExisting',
        'conditions' => 'User.active=1',
        'fields' => '',
        'order' => '',
        'limit' => '',
        'offset' => '',
        'finderQuery' => '',
        'deleteQuery' => '',
        'insertQuery' => ''
    )
);

with the conditions field set, so that only users that are active are fetched from the DB, that I get the error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'User.active' in 'where clause' for the SQL Query:

SELECT `GroupsUser`.`user_id` FROM `groups_users` AS `GroupsUser` WHERE  `GroupsUser`.`group_id` = 123 AND `User`.`active`=1

Because obviously it only fetches the results from the join table. So I found here: http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#hasandbelongstomany-habtm in the description for conditions.

conditions: an array of find() compatible conditions or SQL string. If you have conditions on an associated table, you should use a ‘with’ model, and define the necessary belongsTo associations on it.

What is a 'with' model and how would I implement it?

Thanks a lot in advance!

James F James F · Accepted Answer · 2013-05-02T08:47:34

Although this question is quite old I've recently encountered the same problem and wanted to document my solution. In the above example instead of referencing the User I've had to do a simple sub query to get the associated field like this:

public $hasAndBelongsToMany = array(
    'User' => array(
        'className' => 'User',
        'joinTable' => 'groups_users',
        'foreignKey' => 'group_id',
        'associationForeignKey' => 'user_id',
        'unique' => 'keepExisting',
        //'conditions' => 'User.active=1',
        'conditions' => array('(SELECT active FROM users WHERE users.id = GroupsUser.user_id)'=>1),
        'fields' => '',
        'order' => '',
        'limit' => '',
        'offset' => '',
        'finderQuery' => '',
        'deleteQuery' => '',
        'insertQuery' => ''
    )
);

In my similar example finds & saves now work.

Cakephp HABTM association conditions causes Column not found SQL error

1 Answers