I'm developing a site in which users create profiles ("Profile"
model). These profiles have a significant number of 'belongsTo'
bindings. When I create a standard find('first') call on the model,
it performs a single SELECT statement to retrieve the model data and
each associated belongsTo model data. However, when I create a
find('first') call that makes use of the ContainableBehavior, it makes
the same call as in the first case with an additional (seemingly
unnecessary) SELECT for every belongsTo relationship in the provided
list.
-----
Here is an example of the the simple find('first'):
$profile = $this->Profile->find('first', array(
'conditions' => array(
'Profile.id' => $id,
'Profile.visible' => 'Y',
),
));
-----
And the resulting *single* SQL statement from this call:
SELECT `Profile`.`id`, `Profile`.`member_id`, `Profile`.`geocode_id`,
`Profile`.`gender_id`, `Profile`.`photo_id`, `Profile`.`birthdate`,
`Profile`.`name`, `Profile`.`headline`, `Profile`.`description`,
`Profile`.`marital_status_id`, `Profile`.`have_children`,
`Profile`.`want_children`, `Profile`.`hair_color_id`,
`Profile`.`height`, `Profile`.`physique_id`, `Profile`.`ethnicity_id`,
`Profile`.`religion_id`, `Profile`.`politics_id`, `Profile`.`smokes`,
`Profile`.`drinks`, `Profile`.`drugs`, `Profile`.`visible`,
`Profile`.`created`, `Profile`.`updated`, `Member`.`id`,
`Member`.`username`, `Member`.`password`, `Member`.`email`,
`Member`.`active`, `Member`.`created`, `Member`.`updated`,
`Geocode`.`id`, `Geocode`.`latitude`, `Geocode`.`longitude`,
`Geocode`.`city`, `Geocode`.`state`, `Geocode`.`zipcode`,
`Geocode`.`country_id`, `Gender`.`id`, `Gender`.`name`,
`Gender`.`order`, `ProfilePhoto`.`id`, `ProfilePhoto`.`profile_id`,
`ProfilePhoto`.`filename_big`, `ProfilePhoto`.`filename_thumb`,
`ProfilePhoto`.`created`, `MaritalStatus`.`id`,
`MaritalStatus`.`name`, `MaritalStatus`.`order`, `HairColor`.`id`,
`HairColor`.`name`, `HairColor`.`order`, `Physique`.`id`,
`Physique`.`name`, `Physique`.`order`, `Ethnicity`.`id`,
`Ethnicity`.`name`, `Ethnicity`.`order`, `Religion`.`id`,
`Religion`.`name`, `Religion`.`order`, `Politics`.`id`,
`Politics`.`name`, `Politics`.`order`, `Smokes`.`id`, `Smokes`.`name`,
`Smokes`.`order`, `Drinks`.`id`, `Drinks`.`name`, `Drinks`.`order`,
`Drugs`.`id`, `Drugs`.`name`, `Drugs`.`order` FROM `profiles` AS
`Profile` LEFT JOIN `members` AS `Member` ON (`Profile`.`member_id` =
`Member`.`id`) LEFT JOIN `geocodes` AS `Geocode` ON
(`Profile`.`geocode_id` = `Geocode`.`id`) LEFT JOIN `genders` AS
`Gender` ON (`Profile`.`gender_id` = `Gender`.`id`) LEFT JOIN `photos`
AS `ProfilePhoto` ON (`Profile`.`photo_id` = `ProfilePhoto`.`id`) LEFT
JOIN `marital_statuses` AS `MaritalStatus` ON
(`Profile`.`marital_status_id` = `MaritalStatus`.`id`) LEFT JOIN
`hair_colors` AS `HairColor` ON (`Profile`.`hair_color_id` =
`HairColor`.`id`) LEFT JOIN `physiques` AS `Physique` ON
(`Profile`.`physique_id` = `Physique`.`id`) LEFT JOIN `ethnicities` AS
`Ethnicity` ON (`Profile`.`ethnicity_id` = `Ethnicity`.`id`) LEFT JOIN
`religions` AS `Religion` ON (`Profile`.`religion_id` =
`Religion`.`id`) LEFT JOIN `politics` AS `Politics` ON
(`Profile`.`politics_id` = `Politics`.`id`) LEFT JOIN
`frequency_types` AS `Smokes` ON (`Profile`.`smokes` = `Smokes`.`id`)
LEFT JOIN `frequency_types` AS `Drinks` ON (`Profile`.`drinks` =
`Drinks`.`id`) LEFT JOIN `frequency_types` AS `Drugs` ON
(`Profile`.`drugs` = `Drugs`.`id`) WHERE `Profile`.`id` = 2 AND
`Profile`.`visible` = 'Y' LIMIT 1
-----
Now here is an example of the find('first') call that uses
Containable:
$profile = $this->Profile->find('first', array(
'contain' => array('Geocode', 'Geocode.Country', 'Gender.name',
'Physique.name', 'HairColor.name', 'Ethnicity.name',
'MaritalStatus.name', 'Religion.name', 'Politics.name',
'Seeking.name', 'EncounterType.name', 'Interest.name', 'Photo',
'ProfilePhoto'),
'conditions' => array(
'Profile.id' => $id,
'Profile.visible' => 'Y',
),
));
-----
And here is the resulting *multiple* SQL SELECT dump that using
Containable generates:
SELECT `Profile`.`id`, `Profile`.`member_id`, `Profile`.`geocode_id`,
`Profile`.`gender_id`, `Profile`.`photo_id`, `Profile`.`birthdate`,
`Profile`.`name`, `Profile`.`headline`, `Profile`.`description`,
`Profile`.`marital_status_id`, `Profile`.`have_children`,
`Profile`.`want_children`, `Profile`.`hair_color_id`,
`Profile`.`height`, `Profile`.`physique_id`, `Profile`.`ethnicity_id`,
`Profile`.`religion_id`, `Profile`.`politics_id`, `Profile`.`smokes`,
`Profile`.`drinks`, `Profile`.`drugs`, `Profile`.`visible`,
`Profile`.`created`, `Profile`.`updated`, `Geocode`.`id`,
`Geocode`.`latitude`, `Geocode`.`longitude`, `Geocode`.`city`,
`Geocode`.`state`, `Geocode`.`zipcode`, `Geocode`.`country_id`,
`Gender`.`name`, `ProfilePhoto`.`id`, `ProfilePhoto`.`profile_id`,
`ProfilePhoto`.`filename_big`, `ProfilePhoto`.`filename_thumb`,
`ProfilePhoto`.`created`, `MaritalStatus`.`name`, `HairColor`.`name`,
`Physique`.`name`, `Ethnicity`.`name`, `Religion`.`name`,
`Politics`.`name` FROM `profiles` AS `Profile` LEFT JOIN `geocodes` AS
`Geocode` ON (`Profile`.`geocode_id` = `Geocode`.`id`) LEFT JOIN
`genders` AS `Gender` ON (`Profile`.`gender_id` = `Gender`.`id`) LEFT
JOIN `photos` AS `ProfilePhoto` ON (`Profile`.`photo_id` =
`ProfilePhoto`.`id`) LEFT JOIN `marital_statuses` AS `MaritalStatus`
ON (`Profile`.`marital_status_id` = `MaritalStatus`.`id`) LEFT JOIN
`hair_colors` AS `HairColor` ON (`Profile`.`hair_color_id` =
`HairColor`.`id`) LEFT JOIN `physiques` AS `Physique` ON
(`Profile`.`physique_id` = `Physique`.`id`) LEFT JOIN `ethnicities` AS
`Ethnicity` ON (`Profile`.`ethnicity_id` = `Ethnicity`.`id`) LEFT JOIN
`religions` AS `Religion` ON (`Profile`.`religion_id` =
`Religion`.`id`) LEFT JOIN `politics` AS `Politics` ON
(`Profile`.`politics_id` = `Politics`.`id`) WHERE `Profile`.`id` = 2
AND `Profile`.`visible` = 'Y' LIMIT 1
SELECT `Geocode`.`id`, `Geocode`.`latitude`, `Geocode`.`longitude`,
`Geocode`.`city`, `Geocode`.`state`, `Geocode`.`zipcode`,
`Geocode`.`country_id` FROM `geocodes` AS `Geocode` WHERE
`Geocode`.`id` = 1
SELECT `Country`.`id`, `Country`.`name`, `Country`.`enabled` FROM
`countries` AS `Country` WHERE `Country`.`id` = 204
SELECT `Gender`.`name` FROM `genders` AS `Gender` WHERE `Gender`.`id`
= 1
SELECT `MaritalStatus`.`name` FROM `marital_statuses` AS
`MaritalStatus` WHERE `MaritalStatus`.`id` = '0'
SELECT `HairColor`.`name` FROM `hair_colors` AS `HairColor` WHERE
`HairColor`.`id` = '0'
SELECT `Physique`.`name` FROM `physiques` AS `Physique` WHERE
`Physique`.`id` = '0'
SELECT `Ethnicity`.`name` FROM `ethnicities` AS `Ethnicity` WHERE
`Ethnicity`.`id` = '0'
SELECT `Religion`.`name` FROM `religions` AS `Religion` WHERE
`Religion`.`id` = '0'
SELECT `Politics`.`name` FROM `politics` AS `Politics` WHERE
`Politics`.`id` = '0'
-----
It seems that the additional SELECT statements are redundant when
using Containable since belongsTo relationships are automatically
retrieved at the same time the parent model data is retrieved. How
can I prevent this? Is this a bug or enhancement opportunity in
ContainableBehavior that I should submit to Trac?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
