We have two tables with the name of the 'cities_extended' and 'states' now to get the data from the both tables the 'cities_extended' table hasOne relation to the 'states' table. Below also will show the table structure of the both tables. 'states' table have primary key column name is 'state_code' and the same name of the field uses as the foreign key in the 'cities_extended' table.
We need to crate two model classes 'State.php' and 'Citystate.php' State model query data only from the 'states' table but the 'Citystate.php' model query data from the both tables 'cities_extended' and 'states'.
App::uses('AppModel', 'Model');
class State extends AppModel
{
public $name = "State";
public $useTable = "states";
public $actsAs = array('Containable');
}
// THIS CODE FOR THE MODEL Citystate Citystate.php
App::uses('AppModel', 'Model');
class Citystate extends AppModel
{
public $name = "Citystate";
public $useTable = "cities_extended";
public $actsAs = array('Containable');
public $belongsTo = array(
'State' => array(
'className' => 'State',
'foreignKey' => 'state_code'
)
);
}
// Now there is code to fetch data from the Citystate on a condition of zip
$this->loadModel('Citystate');
$cts = $this->Citystate->find('first',array('conditions'=>array('zip'=>10009)));
print_r($cts);
// RESULTS OF PREVIOUS QUERY
Also we can this with the simple join query
$this->loadModel('Citystate');We need to crate two model classes 'State.php' and 'Citystate.php' State model query data only from the 'states' table but the 'Citystate.php' model query data from the both tables 'cities_extended' and 'states'.
// THIS CODE FOR THE MODEL State State.php
App::uses('AppModel', 'Model');
class State extends AppModel
{
public $name = "State";
public $useTable = "states";
public $actsAs = array('Containable');
}
// THIS CODE FOR THE MODEL Citystate Citystate.php
App::uses('AppModel', 'Model');
class Citystate extends AppModel
{
public $name = "Citystate";
public $useTable = "cities_extended";
public $actsAs = array('Containable');
public $belongsTo = array(
'State' => array(
'className' => 'State',
'foreignKey' => 'state_code'
)
);
}
// Now there is code to fetch data from the Citystate on a condition of zip
$this->loadModel('Citystate');
$cts = $this->Citystate->find('first',array('conditions'=>array('zip'=>10009)));
print_r($cts);
// RESULTS OF PREVIOUS QUERY
Array
(
[Citystate] => Array
(
[city] => New York
[state_code] => NY
[zip] => 10009
[latitude] => 40.726188
[longitude] => -73.979591
[county] => New York
)
[State] => Array
(
[state] => New York
[state_code] => NY
)
)
Also we can this with the simple join query
$joins = array(
array(
'table' => 'states',
'alias' => 'states',
'foreignKey' => false,
'type' => 'INNER',
'conditions' => array('Citystate.state_code = states.state_code')
)
);
$fields_arr = array('Citystate.*, states.*');
$cts = $this->Citystate->find('first',array('fields' => $fields_arr, 'conditions'=>array('zip'=>10009), 'joins' => $joins));
print_r($cts);