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');