We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 40092
    • 265 Posts
    Hi guys,

    As i wrestle myself through the docs and forums, i'm now stuck on this one.
    I have this function:

    function getOffers(){
    	global $modx;
    	$select = $modx->newQuery('Offers');
    		$select->select($modx->getSelectColumns('Accomodations', 'Countries'));
    			$select->select(array(
    			'Countries.naam as countryName',
    			'Countries.alias as countryAlias',
    			'Accomodations.naam as accomodationName',
    			'Accomodations.plaats',
    			'Accomodations.intro',
    			'Accomodations.alias',
    			'Accomodations.klm',
    			'Accomodations.soort',
    			'Accomodations.vanafPrijs',
    			'Accomodations.offer_price',
    			'Accomodations.numberDays',
    			'Accomodations.klasse',
    			'Accomodations.thumbnail',
    			'Accomodations.code',
    			));
    			
    			$select->leftJoin('Offers','Accomodations', 'Offers.idAccomodation = Accomodations.id');
    			$select->leftJoin('Countries', 'Accomodations', 'countries.id = accomodations.land_id');
    			
    			$select->where(array(
    			'active' => 1,
    			));
    			
    			$select->groupby('Offers.sorteer');
    			
    			$result = $modx->getIterator('Offers', $select);
    			if(empty($result)){
    				$result = "leeg!";
    			}
    			else{}
    			
    				return $result;
    	}	
    


    And this call on frontend:

    $results = $moreReises->getOffers();
    foreach($results as $resultaten){
    
    		$result = $resultaten->toArray();
    
    //iterating values like this
    $result['value'];
    


    And i get absolutely nothing, also no errors in the log or manager nor php (set to ALL).
    Anyone with a clue what is going wrong here?

    Regards
    Robin [ed. note: thapriest86 last edited this post 13 years, 1 month ago.]
      • 40092
      • 265 Posts
      Hmmm, found that with:

      $result = $modx->getCollectionGraph('Offers', $select);


      I do get something, but only the contents of the Offers Table, so it seems the $select criteria is skipped?
        • 3749
        • 24,544 Posts
        getCollectionGraph() takes three arguments and you're only sending two.

        http://rtfm.modx.com/xpdo/2.x/getting-started/using-your-xpdo-model/retrieving-objects/getcollectiongraph
          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
          • 40092
          • 265 Posts
          If i understand this correctly, this is used when there is an actual relation in the schema?
            • 3749
            • 24,544 Posts
            Yes, xPDO has to know they are related objects in order for getCollectionGraph() to work.

            BTW, you probably know this, but fixing the schema is not enough because it's ignored. You need to generate new class and map files from the corrected schema.
              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
              • 40092
              • 265 Posts
              Actually i didn't smiley

              So basically, for every table join i currently have i should generate new class and maps for?
              That seems like an odd way of doing a relatively simple query.

              Example given:

              I have this wich i wanted to "convert" (see my attempt in the first post):
              function getMore(){
              		$select = "SELECT 
              						countries.naam as countryName, 
              						countries.alias as countryAlias,
              						accomodations.naam as accomodationName, 
              						accomodations.plaats, 
              						accomodations.intro, 
              						accomodations.alias,
              						accomodations.klm,
              						accomodations.soort, 
                                                              accomodations.vanafPrijs,                         
              						accomodations.offer_price, 						
              						accomodations.numberDays, 
              						accomodations.klasse, 
              						accomodations.thumbnail,
                                                              accomodations.code 
              						FROM offers 
              						LEFT JOIN accomodations ON offers.idAccomodation = accomodations.id
              						LEFT JOIN countries ON countries.id = accomodations.land_id 
              						WHERE accomodations.active = 1
              						ORDER BY offers.sorteer 
              						  ";
              		$query = mysql_query($select);
              		$result = array();
              		if (mysql_numrows($query) == 0) { 
              			//Geen accomodations
              		} else {
              			while ($row = mysql_fetch_object($query)) {
              			
              				foreach ($row as $key => $value) {
              					$row->$key = $this->cleanfromdatabase($row->$key);
              				}
              			
              			$result[] = $row;
              			}
              		}
              		return $result;
              	}	


              I believe i am misunderstanding something, here smiley
                • 40706
                • 128 Posts
                I Think your first Post was nearly right, but Ad Hoc Fields, which are not explained in the XML are not mapped to the objects by default.

                You could just add
                $modx->setOption(xPDO::OPT_HYDRATE_ADHOC_FIELDS, true);


                so all Fields which are not part of "Offers" will be in the object

                But the fault was when you are using getCollection() getIterator() or getObject() you have to select the primary key of the object that you are generating.
                So in your case Offers.id (?) has to be in the resultset with the correct key, otherwise it will fail.


                getCollectionGraph() is an shotcut so you would not need to do this
                $select->leftJoin('Offers','Accomodations', 'Offers.idAccomodation = Accomodations.id');
                $select->leftJoin('Countries', 'Accomodations', 'countries.id = accomodations.land_id');
                

                  • 40092
                  • 265 Posts
                  Thanks Michael,

                  I did not know about the Ad Hoc fields.

                  However, i now have my function like so:
                  Printing the result array, i can see that it fills only the columns from the Offers column.

                  function getOffers(){
                      global $modx;
                  	$modx->setOption(xPDO::OPT_HYDRATE_ADHOC_FIELDS, true);
                      $select = $modx->newQuery('Offers');
                          $select->select($modx->getSelectColumns('Accomodations', 'Countries'));
                              $select->select(array(
                  			'Offers.id',
                              'Countries.naam as countryName',
                              'Countries.alias as countryAlias',
                              'Accomodations.naam as accomodationName',
                              'Accomodations.plaats',
                              'Accomodations.intro',
                              'Accomodations.alias',
                              'Accomodations.klm',
                              'Accomodations.soort',
                              'Accomodations.vanafPrijs',
                              'Accomodations.offer_price',
                              'Accomodations.numberDays',
                              'Accomodations.klasse',
                              'Accomodations.thumbnail',
                              'Accomodations.code',
                              ));
                               
                             $select->leftJoin('Offers','Accomodations', 'Offers.idAccomodation = Accomodations.id');
                             $select->leftJoin('Countries', 'Accomodations', 'countries.id = accomodations.land_id');
                               
                              $select->where(array(
                              'active' => 1,
                              ));
                               
                              $select->groupby('Offers.sorteer');
                               
                              $result = $modx->getCollection('Offers', $select);
                          
                               
                                  return $result;
                      }   
                  
                    • 40092
                    • 265 Posts
                    $select->toSql(); is resulting in this:

                    SELECT `Countries`.`id`, `Countries`.`code`, `Countries`.`land_id`, `Countries`.`alias`, `Countries`.`naam`, `Countries`.`plaats`, `Countries`.`intro`, `Countries`.`vanafPrijs`, `Countries`.`numberDays`, `Countries`.`infoAlgemeen`, `Countries`.`infoFaciliteiten`, `Countries`.`infoAccomodatie`, `Countries`.`klasse`, `Countries`.`soort`, `Countries`.`type`, `Countries`.`details`, `Countries`.`thumbnail`, `Countries`.`active`, `Countries`.`lastminute`, `Countries`.`aangemaakt`, `Countries`.`gewijzigd`, `Countries`.`reisSoorten`, `Countries`.`accomodatieTypes`, `Countries`.`offer_price`, `Countries`.`video`, `Countries`.`losse_prijs`, `Countries`.`meta_beschrijving`, `Countries`.`meta_zoekwoorden`, `Countries`.`zoover`, `Countries`.`dag_prijs`, `Countries`.`klm`, Offers.id, Countries.naam as countryName, Countries.alias as countryAlias, Accomodations.naam as accomodationName, Accomodations.plaats, Accomodations.intro, Accomodations.alias, Accomodations.klm, Accomodations.soort, Accomodations.vanafPrijs, Accomodations.offer_price, Accomodations.numberDays, Accomodations.klasse, Accomodations.thumbnail, Accomodations.code FROM `custom_offers` AS `Offers` LEFT JOIN `custom_offers` `Accomodations` ON Offers.idAccomodation = Accomodations.id LEFT JOIN `custom_countries` `Accomodations` ON countries.id = accomodations.land_id WHERE `Offers`.`active` = '1' GROUP BY Offers.sorteer
                      • 3749
                      • 24,544 Posts
                      Quote from: thapriest86 at Sep 06, 2013, 06:58 AM
                      Actually i didn't smiley

                      So basically, for every table join i currently have i should generate new class and maps for?
                      That seems like an odd way of doing a relatively simple query.

                      To clarify -- you only need to regenerate the class and map files once, whenever you change the schema. If there are related objects in your table structure and you want to be able to use thing like getOne(), getMany(), getObjectGraph(), and getCollectionGraph(), you need to specify the relationships as Aliases in your schema so xPDO knows where to get the related objects.
                        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