We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 4172
    • 5,888 Posts
    This is the relevant part of my schema:

    	<object class="Products" table="products" extends="xPDOObject">
    		<field key="products_id" dbtype="int" precision="11" phptype="integer" null="false" index="pk"  generated="native" />
    		<field key="products_ean" dbtype="varchar" precision="128" phptype="string" null="true" />
    		<field key="products_quantity" dbtype="int" precision="4" phptype="integer" null="false" default="0" />
    		<field key="products_shippingtime" dbtype="int" precision="4" phptype="integer" null="false" default="0" />
    		<field key="products_model" dbtype="varchar" precision="64" phptype="string" null="true" />
            <and_so_on />      
    
    		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
    			<column key="products_id" length="" collation="A" null="false" />
    		</index>
            
            <composite alias="ProductsDescription" class="ProductsDescription" local="products_id" foreign="products_id" cardinality="one" owner="local" />
            
    	</object>
    	<object class="ProductsDescription" table="products_description" extends="xPDOObject">
    		<field key="products_id" dbtype="int" precision="11" phptype="integer" null="false" index="pk"  generated="native" />
    		<field key="language_id" dbtype="int" precision="11" phptype="integer" null="false" default="1" index="pk" />
    		<field key="products_name" dbtype="varchar" precision="64" phptype="string" null="false" default="" index="index" />
    		<field key="products_description" dbtype="text" phptype="string" null="true" />
            <and_so_on />   
    
    		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
    			<column key="products_id" length="" collation="A" null="false" />
    			<column key="language_id" length="" collation="A" null="false" />
    		</index>
            
            <aggregate alias="Products" class="Products" local="products_id" foreign="products_id" cardinality="one" owner="foreign" />        
    	</object>


    this works, but I can’t filter by ProductsDescription-fields:
    <?php    
        
    $c = $xpdo->newQuery('Products');
    
    $collection = $xpdo->getCollection('Products', $c);
    $rows=array();
    foreach ($collection as $object){
    	$desc = $object->getOne('ProductsDescription');
        $row = array_merge($object->toArray(),$desc->toArray());
        $rows[]=$row;
    }
    



    this results in a mysql-error
    ’LEFT JOIN `products_description` `desc` ON ’ <- missing ON - statement :
    <?php   
    $c = $xpdo->newQuery('Products');
    $c->leftJoin('ProductsDescription','desc');
    $c->select('Products.*');
    //$c->select('`desc`.`products_id` AS products_desc_id');
    $c->select('`desc`.`products_name` AS products_name');
    
    $collection = $xpdo->getCollection('Products', $c);
    $rows=array();
    foreach ($collection as $object){
        $row =$object->toArray();
        
        $row['products_name'] = $object->get('products_name'); 
        $rows[]=$row;
    }



    the query of this is ok,
    LEFT JOIN `products_description` `desc` ON desc.products_id=Products.products_id
    , all fields of ’Pruducts’ are there
    but resulting products_name of ’ProductsDescription’ is null:
    <?php   
    $c = $xpdo->newQuery('Products');
    $c->leftJoin('ProductsDescription','desc','desc.products_id=Products.products_id');
    $c->select('Products.*');
    //$c->select('`desc`.`products_id` AS products_desc_id');
    $c->select('`desc`.`products_name` AS products_name');
    
    $collection = $xpdo->getCollection('Products', $c);
    $rows=array();
    foreach ($collection as $object){
        $row =$object->toArray();
        
        $row['products_name'] = $object->get('products_name'); 
        $rows[]=$row;
    }


    This is a Revo 2.1.0 rc4, if that matters.

    Another related question:

    Is it correct, that its not possible to do a leftJoin with objects, when there isn’t a relation-definition between this objects in the schema?



      -------------------------------

      you can buy me a beer, if you like MIGX

      http://webcmsolutions.de/migx.html

      Thanks!
      • 4172
      • 5,888 Posts
      the solution seems to be getCollectionGraph.

      This is working:

      <?php
      
      $c = $xpdo->newQuery($classname);
      
      $c->select('Products.*');
      
      //here some conditions
      
      $collection = $xpdo->getCollectionGraph($classname, '{ "ProductsDescription":{} }', $c );
      
      $rows=array();
      foreach ($collection as $object){
          
          $row = array_merge($object->toArray(),$object->ProductsDescription->toArray());
          $rows[]=$row;
      }
      
        -------------------------------

        you can buy me a beer, if you like MIGX

        http://webcmsolutions.de/migx.html

        Thanks!
        • 22303 MODX Staff
        • 10,725 Posts
        Keep in mind when using getCollectionGraph() or anytime you customize the select() of an xPDOQuery, that if you do not select all of the columns that represent all of the objects you are trying to instantiate, the fields that were not selected will be "lazy", IOW, no value is loaded for that field from the database. So if you later call $object->toArray(), an additional query will be fired to retrieve the "lazy-loaded" field values. If you call $object->get() for multiple lazy fields separately, each call will trigger an additional query to the database.

        NOTE: You must at least select the primary key columns to instantiate an xPDOObject at all. Other fields may also be required depending on many different factors (for instance, when using single-table inheritance like modResource does in MODX, you have to select the class_key field so you load it as the proper derivative class).