Foreign Database - Query Multiple Tables

219 Posts

David Pede Reply #1, 12 years ago

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!

Find me on Twitter, GitHub or Google+

5,888 Posts

Bruno17 Reply #2, 12 years ago

by which fields should the tables be joined together?

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

☆ A M B ☆
3,141 Posts

Mark Hamstra Reply #3, 12 years ago

As you haven't defined the relationships (see http://rtfm.modx.com/xpdo/2.x/getting-started/creating-a-model-with-xpdo/defining-a-schema/defining-relationships), you'll need to manually specify the join. Something like this:

$c = $xpdo->newQuery('DataEpl');
$c->innerJoin('MyRelation','DataSeriesA', 'Local.field = Foreign.related_field'); 
$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>";
}

Also see http://rtfm.modx.com/xpdo/2.x/getting-started/using-your-xpdo-model/working-with-related-objects though that relies on defined relations.

Mark Hamstra • Developer spending his days working on Premium Extras and a MODX Site Dashboard with the ability to remotely upgrade MODX and extras to make the MODX world a little better.

Tweet me @mark_hamstra, check my infrequent blog at markhamstra.com, my slightly more frequent ramblings at MODX.today or see code at Github.

MODX Staff
10,725 Posts

opengeek Reply #4, 12 years ago

You do need the join condition(s) in order to actually join the two tables...a relation is not necessary unless you want to automate joins via the graph methods.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

219 Posts

David Pede Reply #5, 12 years ago

Quote from: Bruno17 at Sep 30, 2014, 09:47 AM

by which fields should the tables be joined together?

That's the thing, I don't really know (not very experienced with SQL). Their not related or dependent on each other, just two separate tables containing data for different leagues.

How would you join tables like that usually?

Find me on Twitter, GitHub or Google+

219 Posts

David Pede Reply #6, 12 years ago

Thanks for the responses guys.

So, if I was to join the tables by the 'Div' field, what should the 'MyRelation' part be in Marks example?

$c->innerJoin('???','DataSerieA', 'Local.Div = Foreign.Div');

Find me on Twitter, GitHub or Google+

189 Posts

gissirob Reply #7, 12 years ago

If I'm understanding this, you don't want to join the tables at all. This implies a relationship that does not exist. What you want to do is union them so that you have a complete dataset returned (see http://dev.mysql.com/doc/refman/5.0/en/union.html).

For a Modx example, try this: https://forums.modx.com/thread/74732/newquery-with-more-than-one-table.

Hope this helps.

☆ A M B ☆
3,112 Posts

goldsky Reply #8, 12 years ago

wait........

different database, you said?

Rico
Genius is one percent inspiration and ninety-nine percent perspiration. Thomas A. Edison
MODx is great, but knowing how to use it well makes it perfect!

www.virtudraft.com

Security, security, security! | Indonesian MODx Forum | MODx Revo's cheatsheets | MODx Evo's cheatsheets

Author of Easy 2 Gallery 1.4.x, PHPTidy, spieFeed, FileDownload R, Upload To Users CMP, Inherit Template TV, LexRating, ExerPlan, Lingua, virtuNewsletter, Grid Class Key, SmartTag, prevNext

Maintainter/contributor of Babel

Because it's hard to follow all topics on the forum, PING ME ON TWITTER @_goldsky if you need my help.

5,888 Posts

Bruno17 Reply #9, 12 years ago

perhaps you want to explain from beginning, what you are after at all?

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

219 Posts

David Pede Reply #10, 12 years ago

Quote from: gissirob at Sep 30, 2014, 10:58 AM

If I'm understanding this, you don't want to join the tables at all. This implies a relationship that does not exist. What you want to do is union them so that you have a complete dataset returned (see http://dev.mysql.com/doc/refman/5.0/en/union.html).

For a Modx example, try this: https://forums.modx.com/thread/74732/newquery-with-more-than-one-table.

Hope this helps.

This is exactly what I'm looking for! Thanks gissirob!

My new snippet:

$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->query('
SELECT * FROM data_epl WHERE id = 1
UNION ALL
SELECT * FROM data_serie_a WHERE id = 1
');

$rows = $c->fetchAll(PDO::FETCH_ASSOC);

foreach ($rows as $row) {
  echo "<p>".$row[HomeTeam]." vs ".$row[AwayTeam]."</p>";
}

Does anyone know if there is a xPDO equivalent for this PDO Query?

Find me on Twitter, GitHub or Google+