Answered Query/Join Multiple Tables with xPDO

$classname = 'Data1';

$c = $modx->newQuery($classname);
$c->select($modx->getSelectColumns($classname, $classname));

$joinclass = 'Data2';
$jalias = 'Data2';
$on = 'Data2.model_number=Data1.model_number';

$c->leftjoin($joinclass, $jalias, $on);
$c->select($modx->getSelectColumns($joinclass, $jalias, $jalias . '_'));

$joinclass = 'Data3';
$jalias = 'Data3';
$on = 'Data3.model_number=Data1.model_number';

$c->leftjoin($joinclass, $jalias, $on);
$c->select($modx->getSelectColumns($joinclass, $jalias, $jalias . '_'));


if ($collection = $modx->getCollection($classname, $c)) {
    foreach ($collection as $object) {
        $row = $object->toArray();
        $output .= $modx->getChunk($tpl, $row);
    }
}

$classname = 'Data1';

$c = $modx->newQuery($classname);
$c->select($modx->getSelectColumns($classname, $classname));

$joinclass = 'Data2';
$jalias = 'Data2';
$on = 'Data2.model_number=Data1.model_number';

$c->leftjoin($joinclass, $jalias, $on);
$c->select($modx->getSelectColumns($joinclass, $jalias, $jalias . '_'));

$joinclass = 'Data3';
$jalias = 'Data3';
$on = 'Data3.model_number=Data1.model_number';

$c->leftjoin($joinclass, $jalias, $on);
$c->select($modx->getSelectColumns($joinclass, $jalias, $jalias . '_'));

$c->prepare();
$rows = array();
if ($c->stmt->execute()) {
    if (!$rows = $c->stmt->fetchAll(PDO::FETCH_ASSOC)) {
        $rows = array();
    }
}

foreach ($rows as $row) {
    $output .= $modx->getChunk($tpl, $row);
}

core\model\schema\modx.mysql.schema.xml

SELECT s.id AS subject_id, s.subject_name_en, ls.is_active, l.location_name_en, tg.group_name
FROM cw_subject s 
JOIN cw_location_subject ls ON (s.id = ls.subject_id)
JOIN cw_location l ON (ls.location_id = l.id)
JOIN cw_term_group tg ON (l.id = tg.locationsubject_id);

cw_subject ---- cw_location_subject ---- cw_location
                       |
                       |
                 cw_term_group

cw_subject          = Subject/Subject
cw_location_subject = LocationSubject/LocationSubject
cw_location         = Location/Location
cw_term_group       = TermGroup/TermGroup

$query = $foreignDB->newQuery('Subject');
$query->select('Subject.id,subject_name_en');
$query->innerJoin('LocationSubject','LocationSubject');
$query->select($foreignDB->getSelectColumns('Location','Location','',array('Location.id','location_name_en')));
$query->leftJoin('Location','Location','LocationSubject.location_id = Location.id');

$query->prepare();
print $query->toSQL();

$subjects = $foreignDB->getCollection('Subject',$query);
foreach($subjects as $subject) {
    $subjectArray = $subject->toArray('',false,true); // must set third arg to true due to toArray() lazy load.
    echo '<pre>';
    print_r($subjectArray);
    echo '</pre>';
}

$results = $foreignDB->query("
SELECT s.id AS subject_id, s.subject_name_en, ls.is_active, l.location_name_en 
FROM cw_subject s 
JOIN cw_location_subject ls ON (s.id = ls.subject_id) 
JOIN cw_location l ON (ls.location_id = l.id)");
echo '<pre>';
while ($r = $results->fetch(PDO::FETCH_ASSOC)) {
    print_r($r);
}
echo '</pre>';

$on = 'Data3.model_number=Data2.model_number';

<?xml version="1.0" encoding="UTF-8"?>
<model package="cwregister" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">

    <object class="Location" table="location" extends="xPDOSimpleObject">
        <field key="location_name_en" dbtype="varchar" precision="20" phptype="string" null="true" />
        <composite alias="LocationSubject" class="LocationSubject" local="id" foreign="location_id" cardinality="many" owner="local" />
    </object>

    <object class="Subject" table="subject" extends="xPDOSimpleObject">
        <field key="subject_name_en" dbtype="varchar" precision="100" phptype="string" null="true" />
        <composite alias="LocationSubject" class="LocationSubject" local="id" foreign="subject_id" cardinality="many" owner="local" />
    </object>

    <!-- Many to Many. Assigns a subject to a location -->
    <object class="LocationSubject" table="location_subject" extends="xPDOSimpleObject">
        <field key="location_id" dbtype="int" precision="10" phptype="integer" null="false" />
        <field key="subject_id" dbtype="int" precision="10" phptype="integer" null="false" />
        <aggregate alias="Location" class="Location" local="location_id" foreign="id" cardinality="one" owner="foreign" />
        <aggregate alias="Subject" class="Subject" local="subject_id" foreign="id" cardinality="one" owner="foreign" />
    </object>
    
</model>

	public function getInvoiceGraph($key = 0, $useReferralCode = false) {
		$out = false;
		/* Block the database hit for a fake company user */
		if (is_numeric ( $key ) && ($key > 0)) {
			
			/* Use the remote key or the primary key */
			$criteria = ($useReferralCode) ? array (
					'referralCode' => $key 
			) : array (
					'id' => $key 
			);
			
			/*
			 * Retrieve objectGraph of an Invoice, its items, and the sku details
			 */
			$out = $this->modx->getObjectGraph ( 'xxInvoice', '{"Items":{}}', $criteria );
		}
		return $out;
	}

	
	/**
	 * Retrieves all the users found in the modUserGroup RR
	 *
	 * @return array Collection of modUserGroupMember
	 */
	private function getRRUsers() {
		$criteria = array (
				'name' => 'RR',
				'User.active' => true 
		);
		
		/*
		 * Retrieve objectGraph of a modUserGroup and any active modUserGroupMember attached to the group
		 */
		return $this->modx->getObjectGraph ( 'modUserGroup', '{"UserGroupMembers":{"User":{}}}', $criteria, false );
	}