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?