Re: find in the presence of HABTM

Hi Azuma

probably my original post wasn't clear: I already setup the correct
$hasAndBelongsToArray fields; everything works fine, but when you try to
perform a find which involves a related table, such as:

$this->Paper->find('all', array('conditions' => array('Author.id' => 2)))

you get an sql error since Author.id is not known.

This is because the sql query does not perform a JOIN on related tables
in find.

In fact, when performing a find('all') with "recursion" the sql query
does not perform a join either: first a select is performed on the main
table and then further selects are performed to retrieve related tables.

Thus a find like I'd like to perform requires a manual join.

am I right about this?
cheers
Lorenzo

Azuma wrote:
> First of all, your join-table must be called authors_papers or
> papers_authors, you don't need any model or controller for this table, cake
> will detect it automatically.
>
> Put this code in the models who're connected to eachother:
> Author:
> var $hasAndBelongsToMany = array(
> 'Paper' => array(
> 'className' => 'Paper',
> 'joinTable' => 'authors_papers',
> 'foreignKey' => 'author_id',
> 'associationForeignKey' => 'paper_id',
> 'unique' => true,
> 'conditions' => '',
> 'fields' => '',
> 'order' => '',
> 'limit' => ''
> )
> );
>
> Paper:
> var $hasAndBelongsToMany = array(
> 'Author' => array(
> 'className' => 'Author',
> 'joinTable' => 'authors_papers',
> 'foreignKey' => 'paper_id',
> 'associationForeignKey' => 'author_id',
> 'unique' => true,
> 'conditions' => '',
> 'fields' => '',
> 'order' => '',
> 'limit' => ''
> )
> );
>
> This should do the trick.
> More info: http://book.cakephp.org/view/83/hasAndBelongsToMany-HABTM
>
>
> Lorenzo Bettini wrote:
>> Hi
>>
>> I have Paper and Author in HABTM relation (AuthorsPaper is the join
>> table model).
>>
>> If I try to perform a find on the Paper model which involves also the
>> authors in the condition I get an error since only the papers table is
>> used for the conditions (even though recursive is set to 1).
>>
>> Thus, I made an explicit join:
>>
>> $joins[] = array(
>> 'table' => $this->AuthorsPaper->tablePrefix.$this->AuthorsPaper->useTable,
>> 'alias' => 'AuthorsPaper',
>> 'conditions' =>
>> array('AuthorsPaper.paper_id = Paper.id AND AuthorsPaper.author_id' =>
>> $authors)
>> );
>>
>> $this->find('all',
>> array(
>> 'joins' => $joins,
>> 'conditions' => $conditions
>> )
>> );
>>
>> where $conditions includes conditions on papers' fields.
>>
>> Is this the correct approach?
>>
>> In particular, I haven't found any "official" documentation on joins but
>> on some blog posts or on bakery (e.g.,
>> http://bakery.cakephp.org/articles/view/quick-tip-doing-ad-hoc-joins-in-model-find).
>>
>> I'm asking since using joins requires specifying an explicit table name
>> (which can be achieved anyway using tablePrefix and useTable so that it
>> works also with fixtures in unit tests).
>>
>> thanks in advance
>> Lorenzo
>>

--
Lorenzo Bettini, PhD in Computer Science, DI, Univ. Torino
HOME: http://www.lorenzobettini.it MUSIC: http://www.purplesucker.com
BLOGS: http://tronprog.blogspot.com http://longlivemusic.blogspot.com