CODEWITHSUNDEEP
cakephpcakephp-2.0
Ari Takeshi
Ari Takeshi

Reputation: 934

Cakephp Find conditions on related table with hasAndBelongsToMany

I have the following code that gives an error from the moment I do a find condition on the associated Model (HABTM):

class Users extends Model{
    public $useTable = 'users';

    public $hasAndBelongsToMany = array(
        'UserCategories' => array(
            'className' => 'UserCategories',
            'joinTable' => 'user_categories_link',
            'foreignKey' => 'user_id',
            'associationForeignKey' => 'category_id',
            'unique' => false,
        )
    );
    public function getData($page = 0, $category = '', $subcategory = ''){
    return $this->find('all', array(
        'limit'     => 6,
        'page'      => $page,
        'conditions'=> array(
            'active'=> 1,
            'UserCategories.category_name' => $category, // THIS GIVES ERROR
            'UserCategories.category_subcategory' => $subcategory, // THIS GIVES ERROR

        )
    ));
}

In my Controller:

$this->Users->getData(0, 'somemaincategory', 'somesubcategory');

I can't seem to do conditions on the related HABTM-Model (UserCategories in this case). I also tried to use 'contain' (with $actsAs), but then he stills gives me all the User data even if there is no Category linked with it. The Category array is in that case just blank.

Hope someone can help me. Thanks in advance,

Aäron

Upvotes: 0

Views: 145

Answers (1)

Kai
Kai

Reputation: 3823

Do a manual join. You can use this to do an actual inner join (contain will act as a left join). http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#joining-tables

$this->find('all', 
     array(
         'conditions' => array(
              'active' => 1,
          ), 
         'joins' => array(
               array(
                   'table' => 'user_categories_link',
                   'alias' => 'UserCategoriesLink',
                   'type' => 'inner',
                   'conditions' => array(
                        'UserCategoriesLink.user_id = User.id'
                   ),
              ),
              array(
                   'table' => 'user_categories',
                   'alias' => 'UserCategories',
                   'type' => 'inner',
                   'conditions' => array(
                        'UserCategories.id = UserCategoriesLink.category_id',
                        'UserCategories.category_name' => $category,
                        'UserCategories.category_subcategory' => $subcategory,
                   ),
              )
         ),
     )
);

Upvotes: 2

Related Questions