Using the latest 1.2 release (and earlier ones), I'm finding that
queries that could be performed with a JOIN are breaking into multiple
single row queries at $recursive = 2 or more. In fact worse, there is
a JOIN and some single queries that duplicate part of the JOIN for one
of the tables. I may well be missing something, but it's not clear to
me what that might be. (Skip to "THE PROBLEM" to get to the issue
without the setup details.)
The models in this example are A_Thing, B_Thing, C_Thing. Having one
to one mappings, a C_Thing belongs to a B_Thing, and a B_Thing belongs
to an A_Thing. In the DB we have:
mysql> describe a_things;
+------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| b_thing_id | int(11) | NO | | | |
+------------+---------+------+-----+---------+----------------+
mysql> describe b_things;
+------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| c_thing_id | int(11) | NO | | | |
+------------+---------+------+-----+---------+----------------+
mysql> describe c_things;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
+-------+---------+------+-----+---------+----------------+
In my test DB we have A_Thing = values (1,5), B_Thing = values (5,10),
C_Thing = values (10)
Model classes are:
class AThing extends AppModel {
var $name = 'AThing';
var $useDbConfig = 'cake';
var $primaryKey = 'id';
var $recursive = 2;
var $belongsTo = array('BThing');
}
class BThing extends AppModel {
var $name = 'BThing';
var $useDbConfig = 'cake';
var $primaryKey = 'id';
var $belongsTo = array('CThing');
}
class CThing extends AppModel {
var $name = 'CThing';
var $useDbConfig = 'cake';
var $primaryKey = 'id';
}
THE PROBLEM:
If we set $recursive to 1, we get the A_Thing and it's B_Thing as
expected, and the query is a correct JOIN:
SELECT `AThing`.`id`, `AThing`.`b_thing_id`, `BThing`.`id`,
`BThing`.`c_thing_id`, `BThing`.`a_thing_id` FROM `a_things` AS
`AThing` LEFT JOIN `b_things` AS `BThing` ON (`AThing`.`b_thing_id` =
`BThing`.`id`) WHERE `AThing`.`id` = 1 LIMIT 1
However if we set $recursive to 2, we get:
SELECT `AThing`.`id`, `AThing`.`b_thing_id`, `BThing`.`id`,
`BThing`.`c_thing_id`, `BThing`.`a_thing_id` FROM `a_things` AS
`AThing` LEFT JOIN `b_things` AS `BThing` ON (`AThing`.`b_thing_id` =
`BThing`.`id`) WHERE `AThing`.`id` = 1 LIMIT 1
SELECT `BThing`.`id`, `BThing`.`c_thing_id`, `BThing`.`a_thing_id`
FROM `b_things` AS `BThing` WHERE `BThing`.`id` = 5
SELECT `CThing`.`id`, `CThing`.`b_thing_id` FROM `c_things` AS
`CThing` WHERE `CThing`.`b_thing_id` = 5
So instead of the retrieval of C being part of what should be still
one query, we get the original query, a duplicate query on B_Things,
plus the one for C_Things.
This behaviour is also the same if we use hasOne instead and the
schemas have back instead of forward linking fields (present in these
query results after modifying the DB to test that).
The returned data is as below, which seems to be correct although it's
not clear to me why C_Thing is nested inside B_Thing (as I'd expect)
but B_thing is not tested inside A_Thing - any explanation of that
would be welcome too.
Array
(
[AThing] => Array
(
[id] => 1
[b_thing_id] => 5
)
[BThing] => Array
(
[id] => 5
[c_thing_id] => 10
[a_thing_id] => 1
[CThing] => Array
(
[id] => 10
[b_thing_id] => 5
)
)
)
Any help much appreciated.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake
PHP" 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
-~----------~----~----~----~------~----~------~--~---
