22 Feb 07
Symptoms of the problem: Weird – inconsistent DB results in CakePHP.
Root of evil: non-standard conventions. I have a DB table Office that I refer to via 2 columns:
- id – unique within the table
- office_id – same for current and historical records of an office. I differentiate current and historical offices by another column – disposition, which is ‘active’ or ‘historical’. You can also have ‘pending’ and ‘lameduck’ if the office was edited but the change was not confirmed yet.
The actual problem: My other tables referred to Office via office_code and filtered them by disposition=’active’. However, I didn’t tell CakePHP about it. Therefore CakePHP generated queries like
LEFT JOIN `officies` AS `office` ON `Assets`.`office_code`=`Office`.`id`
Solution: add var $primaryKey=’office_code’ in Office model class, along with findAll(..) injecting extra condition to make office_code unique