Forums - Thread Details
  • State Verified Answer
  • Replies 6 replies
  • Subscribers 318 subscribers
  • Views 2182 views
  • Users 0 members are here
Forum Actions
Related

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

Pat Pawlowski

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

Parents Reply
  • 0 in reply to Angel Magana

    Hello,

    If anyone is still interested on this, you will realize that

    $sugarQuery->joinRaw()

    does not exist anymore.

    Instead, you could make a left or right join like this:

    $sq = new SugarQuery();
    $sq->from( BeanFactory::newBean('my_module') );
    $sq->join( 'accounts_my_module_1', array( 'alias' => 'accs', 'joinType' => 'left' ) );
    $sq->select( array('id', 'name', 'my_custom_field_c', array( 'accs.name', 'acc_name' )) );
    $res = $sq->execute();

    Note the option: 'joinType' => 'left' in the array of options in the join clause.

    Hope this helps.

    Regards,

    David.

Children
No Data