Problems with sub-sub-collection in getCollectionGraph

183 Posts

mikrobi Reply #1, 15 years, 8 months ago

Hi again,

I’m having another problem with getCollectionGraph: I have categories, which contain several products which have several prices for different materials (see schema definition below).

I’d like to fetch a collection graph containing all categories, products and their prices ({ "Products":{ "Prices":{} } }):

<?php
$categories = $modx->getCollectionGraph('Category', '{ "Products":{ "Prices":{} } }');

I can loop through all categories and products but there is always only one price fetched for a product:

<?php
foreach($categories as $category) {
	foreach($category->Products as $product) {
		echo count($product->Prices); // returns always 0 or 1 even if there are more than 1 prices defined in the DB table
	}
}

Am I doing something wrong?

Here my (simplified) schema definition:

<?xml version="1.0" encoding="UTF-8"?>
<model package="products" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
	<object class="Product" table="product" extends="xPDOSimpleObject">
		<field key="id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" index="pk" generated="native" />
		<field key="name" dbtype="varchar" precision="255" phptype="string" null="false" default="" index="unique" />
		<field key="category" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" />
		
		<composite alias="Prices" class="Price" local="id" foreign="product" cardinality="many" owner="local" />
		<aggregate alias="Category" class="Category" local="category" foreign="id" cardinality="one" owner="foreign" />
	</object>
	<object class="Category" table="category" extends="xPDOSimpleObject">
		<field key="id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" index="pk" generated="native" />
		<field key="name" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
		
		<composite alias="Products" class="Product" local="id" foreign="category" cardinality="many" owner="local" />
	</object>
	<object class="Material" table="material" extends="xPDOSimpleObject">
		<field key="id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" index="pk" generated="native" />
		<field key="name" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
		
	</object>
	<object class="Price" table="price" extends="xPDOObject">
		<field key="product" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" index="pk" />
		<field key="material" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" index="pk" />
		<field key="price" dbtype="float" attributes="unsigned" phptype="float" null="false" default="0" />
		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true">
			<column key="product" collation="A" null="false" />
			<column key="material" collation="A" null="false" />
		</index>
		
		<aggregate alias="Product" class="Product" local="product" foreign="id" cardinality="one" owner="foreign" />
		<aggregate alias="Material" class="Material" local="material" foreign="id" cardinality="one" owner="foreign" />
	</object>
</model>

Thanks a lot!

Add-On to easily manage your multilingual sites: Babel

MODX Staff
730 Posts

netProphET Reply #2, 15 years, 8 months ago

If I remember correctly, you can’t specify a relationship to an object that has a compound key.
You can always create a custom method in your Product class to handle loading prices, effectively making your own relationships in code. You just won’t have the simplicity of one simple object graph.

Mike Schell
Lead Developer, MODX Cloud
Email: [email protected]
GitHub: https://github.com/netProphET/
Twitter: @mkschell

183 Posts

mikrobi Reply #3, 15 years, 8 months ago

Thanks for your reply!

Hmm, I tried with a modified definition of Material using one field as key:

<object class="Price" table="price" extends="xPDOSimpleObject">
	<field key="id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" index="pk" generated="native" />
	<field key="product" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" />
	<field key="material" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" />
	<field key="price" dbtype="float" attributes="unsigned" phptype="float" null="false" default="0" />
	
	<aggregate alias="Product" class="Product" local="product" foreign="id" cardinality="one" owner="foreign" />
	<aggregate alias="Material" class="Material" local="material" foreign="id" cardinality="one" owner="foreign" />
</object>

But the problem still remains: I’ve inserted two prices for one product but in the collection only one of them is being fetched.

I want to avoid loading the prices with a custom method: Performance will decrease because for each product a query for fetching its prices has to be performed. I’d like to fetch all data with one query...

Add-On to easily manage your multilingual sites: Babel

183 Posts

mikrobi Reply #4, 15 years, 8 months ago

Very strange: the SQL generated by xPDO (see http://modxcms.com/forums/index.php/topic,61212.0.html) is the following:

SELECT `Category`.`id` AS `Category_id`,
	`Category`.`name` AS `Category_name`,
	 `Products`.`id` AS `Products_id`,
	 `Products`.`name` AS `Products_name`,
	 `Products`.`category` AS `Products_category`,
	 `Prices`.`product` AS `Prices_product`,
	 `Prices`.`material` AS `Prices_material`,
	 `Prices`.`price` AS `Prices_price` 
 FROM `modx_category` AS `Category`
 LEFT JOIN `modx_product` `Products` ON `Category`.`id` = `Products`.`category`
 LEFT JOIN `modx_price` `Prices` ON `Products`.`id` = `Prices`.`product`
 ORDER BY `Category`.`id`

When I execute this statement directly in the database (using phpmyadmin). All defined prices for each product are listed. But running through the foreach loop from my previous post returns always only one price per product. What am I doing wrong?!?

Thanks!

Add-On to easily manage your multilingual sites: Babel

MODX Staff
730 Posts

netProphET Reply #5, 15 years, 8 months ago

It seems like it should work. Did you you regenerate your map files after updating your schema?

Mike Schell
Lead Developer, MODX Cloud
Email: [email protected]
GitHub: https://github.com/netProphET/
Twitter: @mkschell

183 Posts

mikrobi Reply #6, 15 years, 8 months ago

Yes, I did. I even deleted all the tables and re-created them via modx and the new xml schema...

Add-On to easily manage your multilingual sites: Babel

183 Posts

mikrobi Reply #7, 15 years, 7 months ago

I#ve encountred this problem in another project, too. I’ll post a simple example code for this issue as soon as possible...

Add-On to easily manage your multilingual sites: Babel

249 Posts

cyclissmo Reply #8, 15 years, 6 months ago

I can confirm the same difficulty in a current project. Very similar to your schema:

$graph = '{"Division":{"Belts":{},"Weights":{}}}';
$q = $this->modx->newQuery('EventDivisions');
$q->bindGraph($graph);

Belts and Weight collections always yield only one object (even though they should be a collection of objects). But taking an SQL dump and executing it yields the correct relationships and items.

Did you ever solve your issue? Is this a bug?

lo9on.com

MODx Evolution/Revolution | Remote Desktop Training | Development

183 Posts

mikrobi Reply #9, 15 years, 6 months ago

I’m quite busy right now and there was no time for posting the simple example. I think this is a XPDO bug. I was not able to get it to work. The only way to get all objects is to make a new query foreach item in your collection (what is of course very bad regarding the performance)...

Add-On to easily manage your multilingual sites: Babel

☆ A M B ☆
872 Posts

lossendae Reply #10, 15 years, 6 months ago

You can also use a native PDO iteration to retreive the desired columns. (based on the working dump).
But you’ll lose the object facilities provided by xPDO.