Hi Robin
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
Check out the new CakePHP Questions site http://cakeqs.org and help others with
their CakePHP related questions.
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected] For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
