I had to join two models together in order to search them properly. In this special case, it was pagination. I was browsing the cookbook for anything that could help and I did in fact find joining tables.
I was doing something kind of bizarre and probably bad practice, but with CakePHP, there are no practices. Anyway, my original index() method looked something like this (where I wanted to paginate a table):
public function index() {
$this->paginate = array("limit" => 10,
"order" => array("Problem.created" => "desc"));
$problems = $this->paginate("Problem");
$this->set("problems", $problems);
};
This particular App deals with Math problems, if you’re wondering. Anyway, I wanted to make it so that these tables could display only the problems from particular group sets. Since that was a fancy hasAndBelongsToMany table, the joining method mentioned in the link was the only viable solution.
Using an if and an empty check with a named url-parameter, I adapted my paginator’s configuration on-demand:
public function index() {
// ... same code as before
if ( !empty($this->params["named"]["group"]) ) {
$groupID = $this->params["named"]["group"];
$this->paginate["joins"] = array("table" => "groups_problems",
"alias" => "Group",
"type" => "inner",
"conditions" => array("Group.id = " .$groupID));
}
// ... same code as before
}
This looks like it will work, according the examples they give in the cookbook. But nothing’s really that simple. When running the code above, loads of scary errors pop out of the oven.
Undefined offset: 0 [CORE/cake/libs/model/datasources/dbo_source.php, line 1403]
Undefined offset: 1 [CORE/cake/libs/model/datasources/dbo_source.php, line 1403]
Undefined offset: 2 [CORE/cake/libs/model/datasources/dbo_source.php, line 1403]
Undefined offset: 3 [CORE/cake/libs/model/datasources/dbo_source.php, line 1403]
Array to string conversion [CORE/cake/libs/model/datasources/dbo_source.php, line 1415]
SQL 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 ‘groups_problems Group inner Array WHERE 1 = 1′ at line 1 [CORE/cake/libs/model/datasources/dbo_source.php, line 635]
Query: SELECT COUNT(*) AS `count` FROM `problems` AS `Problem` groups_problems Group inner Array WHERE 1 = 1
… and it continues …
Frightening, isn’t it? I wasn’t sure by looking at my code and the examples as to the cause. So I did the next best thing; looked at the line in the dbo_source.php that was causing the errors. The easiest to pick out was line 1415, the Array to String conversion.
Once there, I noticed, 'joins' => implode(' ', $query['joins']), was the insidious code being executed. It was expecting an array of arrays! Who knew? After this realization, the fix was easy. I simply wrapped my entire join array in another array.
public function index() {
// ... same code as before
if ( !empty($this->params["named"]["group"]) ) {
$groupID = $this->params["named"]["group"];
$this->paginate["joins"] = array(array(/*... all the same configuration here ... */));
}
// ... same code as before
}
That fixed all of the error messages in one fell swoop. CakePHP often gets me with the errors and the fickle nature of the methods requiring very particular arguments when nobody knows what they are supposed to be.
Happy baking.
This post was written with CakePHP 1.2 in mind. Also, sorry for the source code mess. These source plugins just do not deliver justice.
