CakePHP Model to query data from two different tables

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'.


// 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

      $this->loadModel('Citystate');
$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);

Students Tech Life