Iv'e got a bit of code:
public function getZoneFromUserLocation(){
$user = $this->modx->getUser();
$profile = $user->getOne('Profile');
$usercountry = $profile->get('country');
$criteria = $this->modx->newQuery('Countries');
$criteria->where(array('abbreviation' => $usercountry));
$zones = $this->modx->getCollectionGraph('Countries', '{"MarketZone": {}, }', $criteria);
$criteria->prepare();
echo '<pre>'.$criteria->toSQL().'</pre>';
foreach($zones as $zone){
$x = $zone->toArray();
$mz = $zone->getOne('MarketZone');
$y = $mz->toArray();
print_r($x);
print_r($y);
}
if(!$zone){
return 'Could not determine user\'s zone';
}
return $this->output;
}
Based on the schema:
<?xml version="1.0" encoding="UTF-8"?>
<model package="marketzones" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
<object class="Countries" table="countries" extends="xPDOSimpleObject">
<field key="Country" dbtype="varchar" precision="50" phptype="string" null="false" />
<field key="abbreviation" dbtype="varchar" precision="10" phptype="string" null="false" />
<field key="zone" dbtype="int" precision="4" phptype="integer" null="false" default="0" />
<aggregate alias="MarketZone" class="MarketZone" local="zone" foreign="id" cardinality="one" owner="foreign" />
</object>
<object class="MarketZone" table="market_zone" extends="xPDOSimpleObject">
<field key="zone" dbtype="varchar" precision="50" phptype="string" null="false" />
<field key="abbreviation" dbtype="varchar" precision="10" phptype="string" null="false" />
</object>
</model>
This thing will never get the data from the market_zones table, [tried getOne, getMany, getObject etc] & I've had this kind of problem before. I thought getCollectionGraph was supposed to automatically join the related tables.
What is wrong here?
[ed. note: sean69 last edited this post 12 years, 3 months ago.]
*** Not just websites, we also create signage, banners, print, trade show displays and more! ***
Sean Kimball CLP, CLS.
Technical Director / Sr. Developer | BigBlock Studios
._______________________________________________.
Bigblock Studios
http://www.bigblockstudios.ca Web site design & development.
27-1300 King Street East. Box 167 Oshawa, Ontario L1H8J4 Canada.
phone/fax: 905-426-5525
You need to qualify your where() statement with a prefix (not sure about the capitalization).
$criteria->where(array('MarketZones.abbreviation' => $usercountry));
Actually I'm trying to get the abbreviation from the Countries table [which does work ~ it just does not give me the data from the marketzones table]. even the criteria dump looks like this:
SELECT `Countries`.`id` AS `Countries_id`, `Countries`.`Country` AS `Countries_Country`,
`Countries`.`abbreviation` AS `Countries_abbreviation`, `Countries`.`zone` AS `Countries_zone`
FROM `modx_countries` AS `Countries` WHERE `Countries`.`abbreviation` = 'CA'
no marketZone info unless I add the $criteria->leftJoin('MarketZones'); though I shouldn't have to do this with getCollectionGraph!
Quote from: BobRay at Jul 02, 2014, 11:20 PMYou need to qualify your where() statement with a prefix (not sure about the capitalization).
$criteria->where(array('MarketZones.abbreviation' => $usercountry));
*** Not just websites, we also create signage, banners, print, trade show displays and more! ***
Sean Kimball CLP, CLS.
Technical Director / Sr. Developer | BigBlock Studios
._______________________________________________.
Bigblock Studios
http://www.bigblockstudios.ca Web site design & development.
27-1300 King Street East. Box 167 Oshawa, Ontario L1H8J4 Canada.
phone/fax: 905-426-5525
-
MODX Staff
- 10,725 Posts
You are using getMany() but you have defined the relationship using cardinality="one" — this is a one-to-one relationship.
Ideally, you would define the relationship using cardinality="many" if you are expecting multiple related instances from MarketZones.
Also, abbreviation is misspelled in your MarketZones definition.
I did note that I was trying anything [getone/many/object] but I updated the schema, code snippet and regenerated the package so that the code made a little more sense.. i.e. $zone->getOne('MarketZone')
but still no dice. I must be missing something really silly. [do I have to declare the relationship in the MarketZone schema object as well as in the Countries object?]
Quote from: opengeek at Jul 03, 2014, 10:10 AMYou are using getMany() but you have defined the relationship using cardinality="one" — this is a one-to-one relationship.
Ideally, you would define the relationship using cardinality="many" if you are expecting multiple related instances from MarketZones.
Also, abbreviation is misspelled in your MarketZones definition.
*** Not just websites, we also create signage, banners, print, trade show displays and more! ***
Sean Kimball CLP, CLS.
Technical Director / Sr. Developer | BigBlock Studios
._______________________________________________.
Bigblock Studios
http://www.bigblockstudios.ca Web site design & development.
27-1300 King Street East. Box 167 Oshawa, Ontario L1H8J4 Canada.
phone/fax: 905-426-5525
Didn't work, though the syntax does not look quite right.
From another forum posting: $criteria->bindGraph('{"Countries":{"MarketZone":{}}}');
Though I hear you, but I keep coming back to the docs on get collectiongraph [
http://rtfm.modx.com/xpdo/2.x/getting-started/using-your-xpdo-model/retrieving-objects/getcollectiongraph ]
getCollectionGraph allows you to automatically load up related objects by specifying a JSON style hash to its second argument (in other words, it automatically joins a table on its related tables).
that's pretty clearly stating that I shouldn't have to join the tables manually so long as the schema is correct - I feel like I'm missing something pretty key, because I have seen ot work with and without manually joining tables!
~still struggling~
Quote from: garryn at Jul 03, 2014, 09:32 PMIt's been a long while, and I mean a long while, since I used Collection Graphs ... but I think you need to bind the graph to your $criteria object as well, not just in modX::getCollectionGraph():
$criteria->bindGraph('{"MarketZone": {}}');
When you do that, you may need to qualify your where() statement as BobRay mentioned but like this:
$criteria->where(array('Countries.abbreviation' => $usercountry));
*** Not just websites, we also create signage, banners, print, trade show displays and more! ***
Sean Kimball CLP, CLS.
Technical Director / Sr. Developer | BigBlock Studios
._______________________________________________.
Bigblock Studios
http://www.bigblockstudios.ca Web site design & development.
27-1300 King Street East. Box 167 Oshawa, Ontario L1H8J4 Canada.
phone/fax: 905-426-5525
Maybe another example will help. This works for me:
$c = $modx->newQuery('modResource');
$c->sortby('pagetitle', 'ASC');
$c->where(
array('resourceData.color' => 'red'),
);
$resources = $modx->getCollectionGraph('modResource', '{"resourceData":{}}', $c);
Here's the schema:
<?xml version="1.0" encoding="UTF-8"?>
<model package="extendedresource" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" tablePrefix="ext_" version="1.0.0">
<!-- extend the modUser class -->
<object class="extResource" extends="modResource">
<composite alias="Data" local="id" class="resourceData" foreign="resourcedata_id" cardinality="one" owner="local"/>
</object>
<object class="resourceData" table="resource_data" extends="xPDOSimpleObject">
<field key="resourcedata_id" dbtype="int" precision="11" phptype="integer" null="false" attributes="unsigned"/>
<field key="name" dbtype="varchar" precision="50" phptype="string" null="true"/>
<field key="color" dbtype="varchar" precision="50" phptype="string" null="true"/>
<field key="breed" dbtype="varchar" precision="50" phptype="string" null="true"/>
<field key="age" dbtype="int" precision="10" phptype="integer" null="false" default="0"/>
<index alias="resourcedata_id" name="resourcedata_id" primary="false" unique="true" type="BTREE">
<column key="resourcedata_id" length="" collation="A" null="false"/>
</index>
<aggregate alias="Resource" class="modResource" local="resourcedata_id" foreign="id" cardinality="one" owner="foreign"/>
</object>
</model>