My schema has the following relations:<\/p>\n
User hasMany Transaction belongsTo User \nItem hasMany Transaction belongsTo Item\nUser hasManyAndBelongsTo Item using Transaction as join table \n<\/code><\/pre>\nI’d like to return all items of a given item symbol<\/strong> that belong to a given user id<\/strong>. I’m able to retrieve all the items that belong to the user with<\/p>\n$this->User->find('all', array( 'conditions' => \n array( 'User.id' => $userId ) ) )\n<\/code><\/pre>\nand I’m able to retrieve all the Items with a given item name with<\/p>\n
$this->Item->find( 'all', array( 'conditions =>\n array( 'Item.symbol' => $itemSymbol ) );\n<\/code><\/pre>\nbut 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<\/p>\n
SQL Error: 1054: Unknown column 'Item.id' in 'where clause'\n<\/code><\/pre>\nor<\/p>\n
SQL Error: 1054: Unknown column 'Item.symbol' in 'where clause'\n<\/code><\/pre>\nI’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.<\/p>\n
Relevant Info: \/\/ User.php associations<\/p>\n
\/\/ a user hasMany transactions\n var $hasMany = array('Transaction' =>\n array('className' => 'Transaction',\n 'order' => 'Transaction.created DESC', \/\/ order by descending time of transaction\n \/\/'limit' => '1', \/\/ change this if we need more, for example, if we need a user transaction history\n 'foreignKey' => 'user_id',\n )\n ); \n \/\/ a user hasAndBelongsTo items through transactions\n var $hasAndBelongsToMany = array('Item' =>\n array('className' => 'Item',\n 'joinTable' => 'transactions',\n 'foreignKey' => 'user_id',\n 'associationForeignKey' => 'item_id',\n 'order' => '',\n 'limit' => '',\n 'unique' => true,\n 'finderQuery' => '',\n 'deleteQuery' => '',\n )\n );\n<\/code><\/pre>\n\/\/ Item.php associations<\/p>\n
var $hasMany = array('Transaction' =>\n array('className' => 'Transaction',\n 'order' => 'Transaction.created DESC',\n \/\/'limit' => '1', \/\/ change this if we need more, for example, if we need a item transaction history\n 'foreignKey' => 'item_id',\n )\n );\n \/\/ a item hasAndBelongsTo users through transactions\n var $hasAndBelongsToMany = array('User' =>\n array('className' => 'User',\n 'joinTable' => 'transactions',\n 'foreignKey' => 'item_id',\n 'associationForeignKey' => 'user_id',\n 'order' => '',\n 'limit' => '',\n 'unique' => true,\n 'finderQuery' => '',\n 'deleteQuery' => '',\n )\n );\n<\/code><\/pre>\n\/\/ Transaction.php associations:<\/p>\n
var $belongsTo = array('User', 'Item');\n<\/code><\/pre>\n\n- \n
You’re looking for the Containable behavior; e.g.<\/p>\n
\/\/ app_model.php\nvar $actsAs = array(\n 'Containable'\n);\n<\/code><\/pre>\nI’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.<\/p>\n
\/\/ users_controller.php\n$this->User->find('first', array(\n 'conditions' => array('User.id' => $userId),\n 'contain' => array(\n 'Item' => array(\n 'conditions' => array('Item.symbol' => $itemSymbol)\n )\n )\n);\n<\/code><\/pre>\nCake 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.<\/p>\n<\/li>\n<\/ol>\n
Originally posted 2014-02-15 11:35:12. <\/small><\/p>","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-2776","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/2776","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/comments?post=2776"}],"version-history":[{"count":0,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/2776\/revisions"}],"wp:attachment":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/media?parent=2776"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/categories?post=2776"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/tags?post=2776"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}