We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 26503
    • 620 Posts
    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
      • 3749
      • 24,544 Posts
      You need to qualify your where() statement with a prefix (not sure about the capitalization).

      $criteria->where(array('MarketZones.abbreviation' => $usercountry));


        Did I help you? Buy me a beer
        Get my Book: MODX:The Official Guide
        MODX info for everyone: http://bobsguides.com/modx.html
        My MODX Extras
        Bob's Guides is now hosted at A2 MODX Hosting
        • 26503
        • 620 Posts
        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 PM
        You 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
          • 22303 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.
            • 26503
            • 620 Posts
            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 AM
            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.
              *** 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
              • 10487 MODX Staff
              • 1,535 Posts
              It'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));
                Garry Nutting
                Senior Developer
                MODX, LLC

                Email: [email protected]
                Twitter: @garryn
                Web: modx.com
                • 10487 MODX Staff
                • 1,535 Posts
                Also, if that works, you should be able to get rid of:

                $mz = $zone->getOne('MarketZone');

                As, the MarketZone object would be available via:

                $zone->MarketZone

                That's the whole point of Collection Graphs smiley
                  Garry Nutting
                  Senior Developer
                  MODX, LLC

                  Email: [email protected]
                  Twitter: @garryn
                  Web: modx.com
                  • 26503
                  • 620 Posts
                  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 PM
                  It'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
                    • 3749
                    • 24,544 Posts
                    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>
                      Did I help you? Buy me a beer
                      Get my Book: MODX:The Official Guide
                      MODX info for everyone: http://bobsguides.com/modx.html
                      My MODX Extras
                      Bob's Guides is now hosted at A2 MODX Hosting
                      • 10487 MODX Staff
                      • 1,535 Posts
                      Hmm, have you tried changing your schema so the aggregate relationship has:

                      owner="local"


                      I'm looking at some code I wrote a while back where I was doing Collection Graphs (and it worked at the time):

                      		$c = $this->modx->newQuery('hubCustomer');
                      		$c->bindGraph('{"Address":{}}');
                      		$c->where(array('`hubCustomer`.`id` IN('.implode(',', $results).')'));
                      		$c->sortby('`Address`.`first_name`', 'ASC');
                      
                      		$collection = $this->modx->getCollectionGraph('hubCustomer', '{"Address":{}}', $c);


                      The only difference I can spot is the owner was 'local' in my schema.
                        Garry Nutting
                        Senior Developer
                        MODX, LLC

                        Email: [email protected]
                        Twitter: @garryn
                        Web: modx.com