Search for records through conditions in HABTM

Question

I am trying to find posts in a category associated with a category. Right now I have this:

$this->set('posts', $this->Category->Post->find('all', array('conditions' => array('Category.uri' => $uri)))); 

But this does not seem to work. This is indicated by an error:

 Warning (512): SQL Error: 1054: Unknown column 'Category.uri' in 'where clause' [CORE/cake/libs/model/datasources/dbo_source.php, line 684] ..<snipped>... Query: SELECT `Post`.`id`, `Post`.`title`, `Post`.`uri`, `Post`.`body`, `Post`.`created`, `Post`.`modified` FROM `posts` AS `Post` WHERE `Category`.`uri` = 'holidays'. 

I read that when you have a HABTM between models, you can get it like this. However, the SQL shown does not connect to the category table.

 // Category Model class Category extends AppModel { var $name = 'Category'; var $hasAndBelongsToMany = array( 'Post' => array( 'className' => 'Post' ) ); } // Post Model class Post extends AppModel { var $name = 'Post'; var $hasAndBelongsToMany = array( 'Category' => array( 'className' => 'Category' ) ); } 

Answer 1

This is more efficient code:

 $this->set('posts', $this->Category->find( 'first', array( 'conditions' => array( 'Category.uri' => $uri ), 'contain' => array('Post') ) )); 

Answer 2

I read that when you have a HABTM between models you should be able to get it like that.

I don’t know where you are reading this, but your source is wrong. The HABTM association cake documentation has a script that is almost identical to yours and the steps necessary to achieve the results you are after.

My answer to another HABTM question may be informative in understanding how Cake ORM works under cover art.

Answer 3

It seems to have worked. Is there a more efficient way to do this?

 $options['joins'] = array( array('table' => 'categories_posts', 'alias' => 'CategoriesPosts', 'type' => 'LEFT', 'conditions' => array( 'Category.id = CategoriesPosts.category_id' ) ), array('table' => 'posts', 'alias' => 'Post', 'type' => 'LEFT', 'conditions' => array( 'CategoriesPosts.post_id = Post.id', ) ) ); $options['conditions'] = array( 'Category.uri' => $uri ); $options['fields'] = array( 'DISTINCT(Category.uri), Post.*, Category.*' ); $this->set('posts', $this->Category->find('all', $options));