Notes on Java, Solaris, PHP, LDAP…

November 17, 2007

What dual references can bring

Filed under: Uncategorized — negev @ 4:33 pm
Tags: ,

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`

rather than

`Assets`.`office_code`=Office`.`office_code`

Solution: add var $primaryKey=’office_code’ in Office model class, along with findAll(..) injecting extra condition to make office_code unique

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: