Hi,
I'm trying to get to grips with xPDO but have run into a problem that I'm struggling to solve.
I have a foreign database on the same server as my MODX database. It contains two NON-RELATED tables called 'data_epl' and 'data_serie_a'.
I can query a single table no problem, however I need to query both tables using one xPDO query. Believe I need to add an 'innerJoin' to the query but can't get it to work. Do I need to add a relationship to the schema or is possible without?
Schema:
<?xml version="1.0" encoding="UTF-8"?>
<model package="league_pack" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
<object class="DataEpl" table="data_epl" extends="xPDOSimpleObject">
<field key="Div" dbtype="varchar" precision="4" phptype="string" null="true" />
<field key="Date" dbtype="varchar" precision="8" phptype="string" null="true" />
<field key="HomeTeam" dbtype="varchar" precision="14" phptype="string" null="true" />
<field key="AwayTeam" dbtype="varchar" precision="14" phptype="string" null="true" />
</object>
<object class="DataSerieA" table="data_serie_a" extends="xPDOObject">
<field key="Div" dbtype="varchar" precision="4" phptype="string" null="true" />
<field key="Date" dbtype="varchar" precision="8" phptype="string" null="true" />
<field key="HomeTeam" dbtype="varchar" precision="10" phptype="string" null="true" />
<field key="AwayTeam" dbtype="varchar" precision="10" phptype="string" null="true" />
</object>
</model>
My Query:
$xpdo = new xPDO('mysql:host=localhost;dbname=leagues',$username,$password);
// Test your connection
echo $o = ($xpdo->connect()) ? 'Connected' : 'Not Connected';
if (!$xpdo->addPackage('league_pack','C:/xampp/htdocs/repo/revolution/core/components/league_pack/model/','')) {
return 'Can not load package';
}
$c = $xpdo->newQuery('DataEpl');
//$c->innerJoin('DataSerieA','DataSerieA'); // breaks the query if left in, works without but only for 'data_epl' table
$c->where(array(
'id' => 1,
));
$rows = $xpdo->getCollection('DataEpl',$c);
foreach ($rows as $row) {
$array = $row->toArray();
echo "<p>".$array[HomeTeam]." vs ".$array[AwayTeam]."</p>";
}
Many Thanks!