CakePHP multiple model conditions in find() with HABTM

Question

My schema has the following relations:

User hasMany Transaction belongsTo User 
Item hasMany Transaction belongsTo Item
User hasManyAndBelongsTo Item using Transaction as join table

I'd like to return all items of a given item symbol that belong to a given user id. I'm able to retrieve all the items that belong to the user with

$this->User->find('all', array( 'conditions' => 
    array( 'User.id' => $userId ) ) )

and I'm able to retrieve all the Items with a given item name with

$this->Item->find( 'all', array( 'conditions =>
    array( 'Item.symbol' => $itemSymbol ) );

but if I try to use the condition array( 'Item.symbol' => $itemSymbol, 'User.id' => $userId ) on $this->Item->find(), $this->Item->User->find(), $this->User->find(), or $this->User->Item->find(), I get either the error

SQL Error: 1054: Unknown column 'Item.id' in 'where clause'

or

SQL Error: 1054: Unknown column 'Item.symbol' in 'where clause'

I've devoured HABTM in all versions of the cookbook and read tens of forum / SO posts about it, but I haven't had any luck in forming this query.

Relevant Info: // User.php associations

// a user hasMany transactions
     var $hasMany = array('Transaction' =>
      array('className' => 'Transaction',
      'order' => 'Transaction.created DESC', // order by descending time of transaction
      //'limit' => '1', // change this if we need more, for example, if we need a user transaction history
      'foreignKey' => 'user_id',
      )
      ); 
    // a user hasAndBelongsTo items through transactions
    var $hasAndBelongsToMany = array('Item' =>
        array('className' => 'Item',
            'joinTable' => 'transactions',
            'foreignKey' => 'user_id',
            'associationForeignKey' => 'item_id',
            'order' => '',
            'limit' => '',
            'unique' => true,
            'finderQuery' => '',
            'deleteQuery' => '',
        )
    );

// Item.php associations

var $hasMany = array('Transaction' =>
        array('className' => 'Transaction',
            'order' => 'Transaction.created DESC',
            //'limit' => '1', // change this if we need more, for example, if we need a item transaction history
            'foreignKey' => 'item_id',
        )
    );
    // a item hasAndBelongsTo users through transactions
    var $hasAndBelongsToMany = array('User' =>
        array('className' => 'User',
            'joinTable' => 'transactions',
            'foreignKey' => 'item_id',
            'associationForeignKey' => 'user_id',
            'order' => '',
            'limit' => '',
            'unique' => true,
            'finderQuery' => '',
            'deleteQuery' => '',
        )
    );

// Transaction.php associations:

var $belongsTo = array('User', 'Item');

Could you elaborate on the (1=1) group by user_id and why you have a hasMany and as HABTM relationship with transaction on both models? — Jason McCreary
I've eliminated the (1=1) group by user_id part. I copied it from some tutorial and it's probably superfluous - removing it had no impact on the question or result. — Myer
A User hasMany Transactions (and vice versa) because there is Transaction data that I have to record that has nothing to do with joining a User to an Item, such as the transaction quantity, the transaction price, etc. A User HABTM Items because a user can have many Items and each Item can have many Users. Commenting out just the hasMany relationship in both User and Item has no impact on the results. Commenting out just the HABTM relationship in both User and Item has no impact on the results, either. — Myer

Wayne Wayne · Accepted Answer · 2010-11-03T02:22:55

You're looking for the Containable behavior; e.g.

// app_model.php
var $actsAs = array(
    'Containable'
);

I'm assuming User extends AppModel, so Containable will be set for the child (User). Now, in your controller, conditions for the related class can be placed in the 'contain' key of the second argv of find(); e.g.

// users_controller.php
$this->User->find('first', array(
    'conditions' => array('User.id' => $userId),
    'contain' => array(
        'Item' => array(
            'conditions' => array('Item.symbol' => $itemSymbol)
        )
    )
);

Cake will find the matching User and retrieve matching Items (with the same user_id and the Item.Symbol specified. But be very careful to use 'contain' => array() when it is not required, otherwise Cake will retrieve all of the defined associations, which will tax your database.

CakePHP multiple model conditions in find() with HABTM

1 Answers