CakePHP multiple model conditions in find() with HABTM-Collection of common programming errors

11 years ago

admin

3 minutes

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'

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

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.