Today I came across an interesting problem. When using the recursive features of CakePHP to return a results tree it isn’t possible to refine the results by a condition on the related table when it is a one to many relationship. Take the follow example:
I have 4 tables:
User
-------------------
id
username
password
country_idTemplate
--------------------
id
template
code
user_idTemplateCountry
---------------------
id
country_id
template_idCountry
--------------------
id
countryTemplates have many TemplateCountries
Countries have many TemplateCountries
Users have many Templates
TemplateCountries belongs to Templates
TemplateCountries belongs to Countries
For the purpose of my experiment I am only retrieving the TemplateCountry entries directly related to the template, for a list in a Flex application I am building.
Now, I wish to return all of the templates and other associated records for a certain country / user. I expected to be able to use something along the lines of:
$this->Templates->find('all',array('conditions'=>array("Template.user_id" =>$user_id, "TemplateCountry.id" => $country_id)));
Unfortunately this does not work. This is because when the query is built by the cakePHP engine several queries are built to return the nested tree of results. The first query is simply:
SELECT * from template as Templates where Template.user_id = $user_id AND TemplateCountry.id = $country_id;
This raises an error because the TemplateCountry table is not part of the initial query. My solution, all be it a cumborsome one, is to then take the result and apply a filter, much like is possible with arraycollections in Actionscript.
$result = $this->Templates->find('all',array('conditions'=>array("Template.user_id" =>$user_id));
$result = $this->filterTemplates($result, $country_id);
----------------------------------
function filterTemplates($tpl,$country_id) {
//declare the return as an empty array
$out = array();
//loop through the returns
foreach( $tpl as $item) {
//check each country the template is associated to
foreach( $item['TemplateCountry'] as $country) {
//if it is the same country push to the new list
if($country['country_id'] == $country_id) {
array_push($out, $item);
}
}
}
//$tpl = $out;
return $out;
}
This prunes every record which doesn’t match my condition from the passed array and then replaces the array in the results tree. It’s not the perfect solution, but it does the job. If anyone has any other means of doing this I would love to hear from you.