@Mindzai, thanks good to know about Cake's guts a little.
For some reason enabling xdebug and disabling the Zend optimiser has fixed the previous problem so I can now look at what is going wrong in my code.
I've been checking my scripts and I've found that this part may be the problem. The story goes as follows.
I have a model called Field, each Field MAY have a parent field related to by the foreign key "parent_id".
I have the name of a parent field and would like to fetch all fields whose parent is that field. Following instructions on the CakePHP website (http://book.cakephp.org/view/74/Complex-Find-Conditions) I created the following statement:
$conditionsSubQuery = array('"Parent"."name"' => urldecode($this->params["named"]["field"]));
$dbo = $this->Field->getDataSource();
$subQuery = $dbo->buildStatement(
array(
'fields' => array('"Parent"."id"'),
'table' => $dbo->fullTableName($this->Field),
'alias' => 'Parent',
'limit' => null,
'offset' => null,
'joins' => array(),
'conditions' => $conditionsSubQuery,
'order' => null,
'group' => null
),
$this->Field);
$subQuery = ' "Field"."parent_id" IN (' . $subQuery . ') ';
$subQueryExpression = $dbo->expression($subQuery);
$conditions[] = $subQueryExpression;
$this->Field->find('all', compact('conditions'));
Unfortunately I get the following mysql error:
1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '."parent_id" IN (SELECT "Parent"."id" FROM `fields` AS `Parent` WHERE "Parent"' at line 1
the SQL generated is:
SELECT `Field`.`id`, `Field`.`name`, `Field`.`description`, `Field`.`parent_id`, `Parent`.`id`, `Parent`.`name`, `Parent`.`description`, `Parent`.`parent_id` FROM `fields` AS `Field` LEFT JOIN `fields` AS `Parent` ON (`Field`.`parent_id` = `Parent`.`id`) WHERE "Field"."parent_id" IN (SELECT "Parent"."id" FROM `fields` AS `Parent` WHERE "Parent"."name" = 'Test Field' )
I also tried another method:
$this->set("fields", $this->Field->find("all", array(
"joins" => array(
array(
"table" => "fields",
"alias" => "Parent",
"type" => "inner",
"foreignKey" => false,
"conditions" => array("Parent.name LIKE \"" . urldecode($this->params["named"]["field"]) . "\"")
),
array(
"table" => "fields",
"alias" => "Field",
"type" => "inner",
"foreignKey" => false,
"conditions" => array("Field.parent_id = Parent.id")
)
)
)));
Which produces the following SQL and error:
SELECT `Field`.`id`, `Field`.`name`, `Field`.`description`, `Field`.`parent_id`, `Parent`.`id`, `Parent`.`name`, `Parent`.`description`, `Parent`.`parent_id` FROM `fields` AS `Field` inner JOIN fields AS `Parent` ON (`Parent`.`name` LIKE "Test Field") inner JOIN fields AS `Field` ON (`Field`.`parent_id` = `Parent`.`id`) LEFT JOIN `fields` AS `Parent` ON (`Field`.`parent_id` = `Parent`.`id`) WHERE 1 = 1
error:
1066: Not unique table/alias: 'Field'
I'm really just not sure how to achieve this now and it's evidently causing problems...