CakePHP 2.3 model association using finderQuery works with Model->read() but not with Model->find()

Question

I have nearly got Cake to do what I want here but not quite and I think it's because there is a gap in my knowledge.

I have imported a database table of UK postcodes in to my cakePHP app. Here is the structure:

CREATE TABLE IF NOT EXISTS `postcodes` (
  `ref` varchar(6) NOT NULL DEFAULT '',
  `area` varchar(50) NOT NULL DEFAULT '',
  `uk_region` varchar(4) NOT NULL,
  `lat` decimal(6,4) NOT NULL DEFAULT '0.0000',
  `long` decimal(5,4) NOT NULL DEFAULT '0.0000',
  PRIMARY KEY (`ref`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Here is a row from that table in CSV format.

"AB10","Aberdeen","SCOT","57.1350","-2.1170"

'Accounts' and 'Orders' need to be able to look up these details from a 'Postcode Reference'

So, after a look around I came up with this (I'll just show the Account model) after reading this article http://www.visuallizard.com/blog/2009/02/19/210:

class Account extends AppModel {
    public $hasOne = array('Postcode' => 
        array(
          'className' => 'Postcode', 
          'finderQuery' => 'SELECT Postcode.* FROM accounts, postcodes AS Postcode WHERE accounts.id = {$__cakeID__$} AND accounts.postcode_ref = Postcode.ref', 'foreignKey' => false
));

}

Now, if I do either of these where '16' is the test account id:

$this->Account->read(null, 16);
$this->Account->find('first', array('conditions' => array('Account.id' => 16)));

to retrieve the data everything is fine. However, if I do:

$this->Account->find('all', array('conditions' => array('Account.id' => 16)));

I get an array with the correct result but 2,821 times; which is how many postcode entries there are.

Changing it from a $hasOne to a $hasMany also just returns the result once but it's inside $result['Postcode'][0] as all hasMany queries are and that's going to get on my wick as I'm sure some of you may understand.

Any clues as to what I have done here? Have I mis-understood something or is this a CakePHP bug?

try public $hasOne = array ( 'Postcode' => array ( 'className' => 'Postcode', 'conditions' => array('Account.postcode_ref' => 'Postcode.ref') ) ); — Dipesh Parmar
That was the first thing I did, just gives me a "Column not found: 1054 Unknown column 'Postcode.account_id' in 'on clause'" message. I need many accounts to be able to share the same post code entry. — Andrew Myers
Just as a heads up, finderQuery is not a valid key in a $hasMany definition, only in $hasMany and $hasAndBelongsToMany.api.cakephp.org/2.3/source-class-Model.html#550-560 — Scott
@Scott Well, on reflection, I suppose I'm perverting it; hasOne whould really be hasOne record to itself via an id. Because I set 'foreignKey' => false I reckon it's that causing the 1=1 — Andrew Myers

thaJeztah thaJeztah · Accepted Answer · 2013-03-15T20:29:07

Your best option is to 'turn' the relation around; Account belongsTo Postcode. As an account can only have a single Postcode, basically it 'belongs' to a Postcode and each Postcode (area) can have (contain) multiple accounts.

You allready seem to have the right naming for the foreignKey field in your accounts table, but be sure to specify 'ref' being the Primary Key inside the Postcode model. The relation will look like this;

Account extends AppModel {
    public $belongsTo = array(
        // additional settings are probably not
        // required because postcode_ref follows the
        // CakePHP conventions, so foreignKey will
        // automatically be detected
        'Postcode',
    );

}

And the Postcode model:

Postcode extends AppModel {
    // Important because of non-standard PK name
    public $primaryKey = 'ref';


    public $hasMany = array(
        'Account',
    );
}

This should probably work

CakePHP 2.3 model association using finderQuery works with Model->read() but not with Model->find()

1 Answers