xPDO Retrieving joined rows out of many-to-many tables

82 Posts

maciej.m Reply #1, 14 years, 6 months ago

Hi,

It’s me again. I’ve started working with xPDO and I like it very much.
I have a problem with retrieving records after leftjoin - actually with outputting them.

I have to tables and one reference table.
One is for firms other for regions. One firm could be in many regions.
So in my reference table i put: nazwa (reference to firmname in firms table) and region (reference to regions table)
My XML Schema looks like that:

<?xml version="1.0" encoding="UTF-8"?>
<model package="klimatyzacja" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM">
	<object class="klimaFirma" table="klima_firmy" extends="xPDOSimpleObject">
	  <composite alias="FirmaRegion" class="klimaFirmaRegion" local="id" foreign="firma" cardinality="many" owner="local" />
		<field key="nazwa" dbtype="varchar" precision="250" phptype="string" null="false" default="" index="index" />
		<field key="dzialalnosc" dbtype="text" phptype="string" null="false" default="" />
		<field key="dzialalnosc_en" dbtype="text" phptype="string" null="false" default="" />
		<field key="www" dbtype="varchar" precision="250" phptype="string" null="false" default="" />
		<field key="email" dbtype="varchar" precision="250" phptype="string" null="false" default="" />
		<field key="tel" dbtype="varchar" precision="30" phptype="string" null="false" default="" />
		<field key="fax" dbtype="varchar" precision="30" phptype="string" null="false" default="" />
		<field key="adres" dbtype="varchar" precision="100" phptype="string" null="false" default="" />
		<field key="miasto" dbtype="varchar" precision="30" phptype="string" null="false" default="" />
		<field key="kod" dbtype="varchar" precision="10" phptype="string" null="false" default="" />
		<field key="wpis" dbtype="enum" precision="'2','1','0'" phptype="string" null="false" default="0" />
		<field key="logo" dbtype="varchar" precision="250" phptype="string" null="false" default="" />
		<field key="logo_a" dbtype="enum" precision="'0','1'" phptype="string" null="false" default="0" />
		<field key="rozsz" dbtype="varchar" precision="100" phptype="string" null="false" default="" />
		<field key="o_kont" dbtype="varchar" precision="200" phptype="string" null="false" default="" />
		<field key="t_kont" dbtype="varchar" precision="50" phptype="string" null="false" default="" />
		<field key="em_kont" dbtype="varchar" precision="50" phptype="string" null="false" default="" />
		<field key="nazwa2" dbtype="varchar" precision="250" phptype="string" null="false" default="" index="index" />
		<field key="adres2" dbtype="varchar" precision="100" phptype="string" null="false" default="" />
		<field key="kod2" dbtype="varchar" precision="10" phptype="string" null="false" default="" />
		<field key="miasto2" dbtype="varchar" precision="30" phptype="string" null="false" default="" />
		<field key="nip" dbtype="varchar" precision="30" phptype="string" null="false" default="" />
		<field key="kolejnosc" dbtype="int" precision="11" phptype="integer" attributes="unsigned" null="false" default="0" />
		<field key="aktywacja" dbtype="enum" precision="'0','1'" phptype="string" null="false" default="0" />
		<field key="data" dbtype="date" precision="11" phptype="integer" attributes="unsigned" null="false" default="" />
		<field key="koniec" dbtype="date" precision="11" phptype="string" attributes="unsigned" null="false" default="" />
		<field key="polecamy" dbtype="enum" precision="'0','1'" phptype="string" null="false" default="0"/>
	</object>
	<object class="klimaRegion" table="klima_regiony" extends="xPDOSimpleObject">
	  <composite alias="FirmaRegion" class="klimaFirmaRegion" local="id" foreign="region" cardinality="many" owner="local" />
		<field key="pid" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
		<field key="region" dbtype="varchar" precision="100" phptype="string" null="false" default="" index="index" />	  
	</object>
	<object class="klimaFirmaRegion" table="klima_firma_region" extends="xPDOSimpleObject">
	  <aggregate alias="Firma" class="klimaFirma" local="firma" foreign="id" cardinality="one" owner="foreign" />
	  <aggregate alias="Region" class="klimaRegion" local="region" foreign="id" cardinality="one" owner="foreign" />
		<fields key="firma" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="index" />
		<fields key="region" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="index" />		
	</object>
</model>

I’d like to print firm name and all regions that are connected with it. Normally after JOIN I would have $row array with fields from one and the other table.
For now I don’t want to set up any criteria (besides firm name) I just want to get all firms with %name% and all regions connected to it.
How one should do it in xPDO?

My snippet looks like that:

$search = $_POST['search'];

$query = $modx->newQuery('klimaFirma');
$query->leftJoin('klimaFirmaRegion','FirmaRegion');

$query->where(array(
    'klimaFirma.nazwa:LIKE' => '%'.$search.'%'
));

$query->sortby('klimaFirma.nazwa','ASC');
//$query->limit(4);

$firmy = $modx->getCollection('klimaFirma', $query);
echo 'Wszystkie firmy: '.count($firmy);
foreach ($firmy as $firma) {
	print('<br/>'.$firma->nazwa.' '.$firma->region);
}

--
Regards
MM

4,149 Posts

splittingred Reply #2, 14 years, 6 months ago

Well, for one, in your klimaFirmaRegion xml schema definition, you have ’fields’ instead of ’field’.

shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com

4,149 Posts

splittingred Reply #3, 14 years, 6 months ago

Your query can look like this:

$query = $modx->newQuery('klimaFirma');
$query->select('klimaFirma.*, Region.region AS region');
$query->leftJoin('klimaFirmaRegion','FirmaRegion');
$query->leftJoin('klimaRegion','Region','FirmaRegion.region = Region.id');

$firmy = $modx->getCollection('klimaFirma', $query);
foreach ($firmy as $firma) {
	echo '<br />'.$firma->get('nazwa').' '.$firma->get('region');
}

shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com

82 Posts

maciej.m Reply #4, 14 years, 6 months ago

Though I still don’t understand this line:
$query->leftJoin(’klimaRegion’,’Region’,’FirmaRegion.region = Region.id’);

You’re just specifying the criteria manually for the JOIN. You’re saying, only JOIN if FirmaRegion’s region field is equal to the JOINed table’s ID field.

In return: Id like to report a mistake in xPDO documentation:
http://svn.modxcms.com/docs/display/XPDO10/xPDOQuery
In example -> $c->sortby - should be $query->sortby (and following lines)
Am I right?

You are; it’s been corrected. Thanks!

82 Posts

maciej.m Reply #5, 14 years, 6 months ago

Now I have such ’complex’ (?) query as:

SELECT GROUP_CONCAT( modx_klima_regiony.region
ORDER BY modx_klima_regiony.region ASC 
SEPARATOR  ';;;' ) regiony, modx_klima_firmy. * 
FROM modx_klima_firmy
LEFT JOIN modx_klima_firma_region ON ( modx_klima_firmy.id = modx_klima_firma_region.firma ) 
LEFT JOIN modx_klima_regiony ON ( modx_klima_regiony.id = modx_klima_firma_region.region ) 
WHERE modx_klima_firmy.nazwa LIKE '%ma%'
GROUP BY modx_klima_firmy.id
ORDER BY modx_klima_firmy.nazwa ASC

I’m not sure how to translate it into xPDO

--
Regards
MM

4,149 Posts

splittingred Reply #6, 14 years, 6 months ago

Quote from: maciej.m at Oct 08, 2009, 11:55 AM

Now I have such ’complex’ (?) query as:

SELECT GROUP_CONCAT( modx_klima_regiony.region
ORDER BY modx_klima_regiony.region ASC 
SEPARATOR  ';;;' ) regiony, modx_klima_firmy. * 
FROM modx_klima_firmy
LEFT JOIN modx_klima_firma_region ON ( modx_klima_firmy.id = modx_klima_firma_region.firma ) 
LEFT JOIN modx_klima_regiony ON ( modx_klima_regiony.id = modx_klima_firma_region.region ) 
WHERE modx_klima_firmy.nazwa LIKE '%ma%'
GROUP BY modx_klima_firmy.id
ORDER BY modx_klima_firmy.nazwa ASC

$c = $modx->newQuery('klimaFirmy');
$c->select('
     klimaFirmy.*,
     (SELECT GROUP_CONCAT(region) FROM '.$modx->getTableName('klimaRegion').' ORDER BY region ASC SEPARATOR  ";;;" ) AS regiony
');
$c->leftJoin('klimaFirmaRegion','FirmaRegion');
$c->leftJoin('klimaRegion','Region','Region.id = FirmaRegion.region');
$c->where(array(
    'klimaFirma.nazwa:LIKE' => '%ma%',
));
$c->sortby('klimaFirmy.id','ASC');
$c->groupby('klimaFirmy.id');
$collection = $modx->getCollection('klimaFirmy',$c);

or something like that.

I strongly recommend reading thoroughly the xPDO docs and the API docs.

shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com

82 Posts

maciej.m Reply #7, 14 years, 6 months ago

There was a problem with SEPARATOR ’;;;’. After I removed it returned but with all records from modx_klima_regiony table in new ’regiony’ field.

DEBUG:

SELECT klimaFirma.*, (SELECT GROUP_CONCAT(region) FROM `modx_klima_regiony` ORDER BY region ASC SEPARATOR ";;;" ) AS regiony FROM `modx_klima_firmy` AS `klimaFirma` LEFT JOIN `modx_klima_firma_region` `FirmaRegion` ON `klimaFirma`.`id` = `FirmaRegion`.`firma` LEFT JOIN `modx_klima_regiony` `Region` ON Region.id = FirmaRegion.region WHERE `klimaFirma`.`nazwa` LIKE '%ma%' GROUP BY klimaFirma.id ORDER BY klimaFirma.id ASC

I’ve also noticed that two of the following are not equivalent (at least they don’t return similar results)

SELECT * FROM `modx_klima_firmy` 
LEFT JOIN `modx_klima_firma_region` 
ON (modx_klima_firmy.id = modx_klima_firma_region.firma) 
LEFT JOIN `modx_klima_regiony` 
ON (modx_klima_regiony.id = modx_klima_firma_region.region);

AND

$query = $modx->newQuery('klimaFirma');
$query->select('klimaFirma.*, Region.region AS region, Region.id AS reg_id');
$query->leftJoin('klimaFirmaRegion','FirmaRegion', 'klimaFirma.id = FirmaRegion.firma');
$query->leftJoin('klimaRegion','Region','Region.id = FirmaRegion.region');

The first returns duplicated data (for each Region Firma has)
The second doesn’t.

82 Posts

maciej.m Reply #8, 14 years, 6 months ago

Ok, finally I got query which should work, but it doesn’t (code below).
I use toSQL() function to debug. Then I cut-n-paste SQL query to PhpMyAdmin.
This returns results in PhpMyAdmin but not on a webpage.

$c = $modx->newQuery('klimaFirma');
$c->select('
	 GROUP_CONCAT 
	 ( Region.region ORDER BY Region.region ASC )
	 regiony, klimaFirma.*
');
$c->leftJoin('klimaFirmaRegion','FirmaRegion');
$c->leftJoin('klimaRegion','Region','Region.id = FirmaRegion.region');
$c->where(array(
	'klimaFirma.nazwa:LIKE' => '%ma%',
));
$c->sortby('klimaFirma.id','ASC');
$c->groupby('klimaFirma.id');


$c->prepare();
$sql = $c->toSQL();

echo $sql; // Printing SQL statement which I then try in PhpMyAdmin

$firmy = $modx->getCollection('klimaFirma', $c);

foreach ($firmy as $firma) {
	print('<br/>'.$firma->get('nazwa').' '.$firma->get('regiony'));
}

echo 'Wszystkie firmy: '.count($firmy)

;

MODX Staff
10,725 Posts

opengeek Reply #9, 14 years, 6 months ago

You can’t return data like that as a collection of objects with getCollection. Just use the PDO statement you are creating, execute it, and fetch the results as you would with any PDO statement. If you need to create objects out of the fetched rows, just use newObject() and fromArray() to manually hydrate the objects.

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

82 Posts

maciej.m Reply #10, 14 years, 6 months ago

Quote from: OpenGeek at Oct 08, 2009, 04:57 PM

You can’t return data like that as a collection of objects with getCollection.

I don’t actually understand what does it mean ’data like that’. What is the difference between code Shaun’s given and which returned results:

$c = $modx->newQuery('klimaFirmy');
$c->select('
     klimaFirmy.*,
     (SELECT GROUP_CONCAT(region) FROM '.$modx->getTableName('klimaRegion').' ORDER BY region ASC ) AS regiony
');
$c->leftJoin('klimaFirmaRegion','FirmaRegion');
$c->leftJoin('klimaRegion','Region','Region.id = FirmaRegion.region');
$c->where(array(
    'klimaFirma.nazwa:LIKE' => '%ma%',
));
$c->sortby('klimaFirmy.id','ASC');
$c->groupby('klimaFirmy.id');
$collection = $modx->getCollection('klimaFirmy',$c);

and my code:

$c = $modx->newQuery('klimaFirma');
$c->select('
	 GROUP_CONCAT 
	 ( Region.region ORDER BY Region.region ASC )
	 regiony, klimaFirma.*
');
$c->leftJoin('klimaFirmaRegion','FirmaRegion');
$c->leftJoin('klimaRegion','Region','Region.id = FirmaRegion.region');
$c->where(array(
	'klimaFirma.nazwa:LIKE' => '%ma%',
));
$c->sortby('klimaFirma.id','ASC');
$c->groupby('klimaFirma.id');


$firmy = $modx->getCollection('klimaFirma', $c);

foreach ($firmy as $firma) {
	print('<br/>'.$firma->get('nazwa').' '.$firma->get('regiony'));
}

echo 'Wszystkie firmy: '.count($firmy);