Planet CakePHP

This one took me a few to figure out. On VACentral, there are schedules, which have an arrival and departure point. These points are all stored in one table, so one row in schedule refers to multiple entries in the airports table. It looks something like (ok, not something like, but exactly):

Schedules:
id | departure_icao | arrival_icao

Airports
id | icao

So two ICAO columns in routes map to one same column in airports. The ICAO is a unique 4 character identifier, which is assigned to an airport. It’s quite simple actually, but took me a while to figure it it. First the Airports model:

class Airport extends AppModel
{
public $name = 'Airport';
public $primaryKey = 'id';
public $actAs = array('Containable');
}

And then our Schedules model:

class Schedule extends AppModel {
public $name = 'Schedule';
public $primaryKey = 'id';
public $actsAs = array('Containable');

public $belongsTo = array(
'DepartureAirport' => array(
'className' => 'Airport',
'foreignKey' => false,
'conditions' => 'DepartureAirport.icao = Schedule.departure_icao',
'fields' => '',
'order' => ''
),

'ArrivalAirport' => array(
'className' => 'Airport',
'foreignKey' => false,
'conditions' => 'ArrivalAirport.icao = Schedule.arrival_icao',
'fields' => '',
'order' => ''
)
);
}

So we used the $belongTo relationship, and we will define two relationships – “DepartureAirport” and “ArrivalAirport”. We also select the class we will use (which IMO, should really be called “modelName” or “useModel”, that really tripped me up, but I digress). Next, we define the conditions – we’ll use the relationship name (DepartureAirport or ArrivalAirport), and the column name, along with the column name on the current table it should join on. And that’s pretty much it. You don’t really need a relationship on the “receiving” end (the Airports table), unless you will be querying airports, and finding out what schedules go there. I’ll leave that upto you
And then for the query itself:

$this->Schedule->contain('DepartureAirport', 'ArrivalAirport');
$schedule = $this->Schedule->find('first');

// Our Airport specific data will be contained in:
$schedule['DepartureAirport']
$schedule['ArrivalAirport']

Which will now return something like (etc fields ommitted):

Array
(
[Schedule] => Array
(
[schedule_id] => 4178
[airline_id] => 2
[code] => AEA
[flightnum] => 6371
[depicao] => CYUL
[arricao] => KJFK
)

[DepartureAirport] => Array
(
[airport_id] => 597
[iata] => YUL
[icao] => CYUL
[name] => Montreal / Pierre Elliot Trudeau International Airport, Quebec
[timezone] => US/Eastern
[location] => Montreal QC Canada
[lat] => 45.470556
[lng] => -73.740833
)

[ArrivalAirport] => Array
(
[airport_id] => 268
[iata] => JFK
[icao] => KJFK
[name] => JFK Airport
[timezone] => US/Eastern
[location] => New York-Kennedy NY
[lat] => 40.6398262
[lng] => -73.7787443
)

)

Note how it's using the Containable behavior; this is so it doesn't pull every relationship you've defined with that table (the schedules table above has many more relationships, but for brevity, I only pulled the relevant ones). Not specifying Containable() is REALLY expensive, especially when you don't need all those relationships to be included in every time! To speed it up even more, you should specify the actual field names to pull (the SQL * operator is expensive).