AnsweredAssumed Answered

Is there a way to do a left join with SugarQuery()?

Question asked by Pat Pawlowski on Dec 10, 2015
Latest reply on Jan 17, 2018 by David López

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

Outcomes