CakePHP - hasMany not fetching?

Question

Maybe I am just having a slow day, but for the life of me I can't figure out why this is happening. I haven't done CakePHP in a while and I am trying to use the 1.3 version, but this doesn't seem to be working...

I have two models:

area.php

<?php
class Area extends AppModel {
    var $name = 'Area';
    var $useTable = 'OR_AREA';
    var $primaryKey = 'A_ID';

    var $belongsTo = array(
        'Building' => array(
            'className' => 'Building',
            'foreignKey' => 'FK_B_ID',
        ),
        'Facility' => array(
            'className' => 'Facility',
            'foreignKey' => 'FK_F_ID',
        ),
        'System' => array(
            'className' => 'System',
            'foreignKey' => 'FK_S_ID',
        )
    );
}
?>

building.php

<?php
class Building extends AppModel {
    var $name = 'Building';
    var $useTable = 'OR_BLDG';
    var $primaryKey = 'B_ID';

    var $hasMany = array(
        'Area' => array(
            'className' => 'Area',
            'foreignKey' => 'FK_B_ID',
        )                    
    );
}
?>

OR_AREA has a column titled FK_B_ID that refers to the B_ID.

If I run something like:

$this->Building->find('all', array('recursive' => 2));

I get empty [Area] arrays for all the Buildings even though there are plenty of Areas in the OR_AREA table that are associated to buildings. Not only that, the Query Table doesn't even show CakePHP attempted to find anything but all the records in OR_BLDG. All the more puzzling, if I do:

$this->Area->find('all');

I get all the Areas and the [Building] arrays are populated when appropriate.

What am I missing?

Edit:

The relevant parts of my schema:

CREATE TABLE ORDB_ADMIN.OR_AREA
(
  A_ID            NUMBER(8),
  NAME            VARCHAR2(255 BYTE)            NOT NULL,
  FK_F_ID         NUMBER(8),
  FK_S_ID         NUMBER(8),
  FK_B_ID         NUMBER(8)
)

CREATE TABLE ORDB_ADMIN.OR_BLDG
(
  B_ID           NUMBER(8),
  BLDG           VARCHAR2(90 BYTE)              NOT NULL
)

I do feel it is worthwhile to mention that I am using Oracle and I found a ticket that seems to describe what is happening to me but it was closed 18 months ago and no real details were provided on what was the problem/what was done to correct it.

I also found a comment in the "hasMany" section of the 1.2 CakePHP Book that reads:

If your hasMany association doesn't return any records, check if the column types of the primary key and the foreignKey are the same. If one is int and the other is char, you won't get any records from the association (and no warnings).

But, as you can see above, the key columns are NUMBERs on both ends (which is what CakePHP wants them to be for Oracle as well)

Answer 1

I'm not sure, whether this is correct or not but you have what looks like to me incorrect foreignKeys Area Model... is that the case?

Answer 2

It seems for me that there is something wrong with belogsTo associations in cakephp 1.3. I think it will everything ok if you change you foreign keys to area_id and building_id. Take a look at this post. Another person had the similar problem and that solution worked for him.

Answer 3

There appear to be some issues when dealing with custom foreign keys. I had a similar issue to yours. At first I tried using dynamic bind functions and they worked most of the time.

However, recently I went back and changed everything to xxx_id (cakephp does a lot of weird stuff with underbars so xxxx should not contain any underbars) and all the relationships just started working perfectly.

Therefore, I would suggest making all foreign keys xxxx_id and see if that clears up your problem.

Answer 4

Finally found out why this is happening. Although I defined my columns all uppercase in Oracle CakePHP brings them over lowercased and is unable to find them when you define your custom columns as uppercase. So changing all of my $primaryKey and foreignKey definitions to lowercase fixed my issue.

I opened a ticket on this issue before finding out the reason, but I am not sure there's much to be done other than perhaps making a note in the Book so that someone doesn't waste a few days on this again :)