I would like the join highlighted below to be a left join so the TCX_ProductMappings record is always returned regardless of whether or not there is a linked TCX_Product record.
$Query = new SugarQuery();
$Query->from(BeanFactory::getBean('TCX_ProductMappings'));
$ProductJoinName = $Query->join('tcx_productmappings_tcx_products')->joinName();
$Query->select(array('id','name',array("$ProductJoinName.id", 'product_id')));
$Query->where()->equals('state',$this->Order['State']);
$Query->where()->equals('distributor_product_id',$this->Order['ProductID']);
$Results = $Query->execute();
My current work around is if the above returns no results then to issue the same query sans the join and see if it returns anything. Example below. Not too big of a deal with a single record but when processing many records it's the twice the number of db reads as should be necessary.
if(!isset($Results[0])){
$Query = new SugarQuery();
$Query->from(BeanFactory::getBean('TCX_ProductMappings'));
$Query->select(array('id','name'));
$Query->where()->equals('state',$this->Order['State']);
$Query->where()->equals('distributor_product_id',$this->Order['ProductID']);
$Results = $Query->execute();
}
Thanks for any help, -pat
