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?
public $hasOne = array ( 'Postcode' => array ( 'className' => 'Postcode', 'conditions' => array('Account.postcode_ref' => 'Postcode.ref') ) );
– Dipesh Parmar'foreignKey' => false
I reckon it's that causing the 1=1 – Andrew Myers