I reduced the scenario to the well-known Post <-> Tag situation (with
join table TagsPost), where the HABTM relations in the model are
correcly set.
If you want to find the Post with id 1 and Tag 1, the solution I come up
with is to do (I'm using the fixtures for the unit test):
$this->Post->recursive = 0;
$conditions = array(
'Post.id' => 1,
'PostsTag.tag_id' => 1
);
$this->Post->bindModel(array('hasOne'=>array('PostsTag')));
debug($this->Post->find('all',
array(
'conditions' => $conditions
)
));
$this->Post->bindModel(array('hasOne'=>array('PostsTag')));
debug($this->Post->find('all',
array(
'conditions' => $conditions
)
));
which correctly returns
Array
(
[0] => Array
(
[Post] => Array
(
[id] => 1
[title] => Lorem ipsum dolor sit amet
[body] => Lorem ipsum ...
[created] => 2009-12-12 16:56:43
[modified] => 2009-12-12 16:56:43
)
[PostsTag] => Array
(
[id] => 1
[post_id] => 1
[tag_id] => 1
)
)
)
Instead, the solution
$this->Post->recursive = 1;
$conditions = array(
'Post.id' => 1,
'PostsTag.tag_id' => 1
);
debug($this->Post->find('all',
array(
'conditions' => $conditions
)
));
raises an SQL error since the join is not automatically performed, and
thus PostsTag.tag_id is not visible.
Thus, what I was saying, is that you cannot perform queries (and by
queries I mean cakephp find operations) that spawn more than one table
even when HABTM relations are set: you need to make sure that joins are
performed, either by manually setting 'hasOne' relations or by explicit
joins (as in my original post).
am I right?
cheers
Lorenzo
Robin Scheldeman wrote:
> Hi Lorenzo!
>
> First of all, i don't think you should ever write a query yourself in
> Cake, let aside a join.
>
> Could you give me the function where you perform the find(), and give me
> the models?
>
>
> Regards!
>
>
> Azuma
>
> On wo, 2010-01-06 at 15:27 +0100, Lorenzo Bettini wrote:
>> 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
No comments:
Post a Comment