Thread Details
  • State Verified Answer
  • Replies 6 replies
  • Subscribers 368 subscribers
  • Views 2901 views
  • Users 0 members are here
Forum Actions

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

  • 0 Sugar Employee Badge

    Try using $Query->joinRaw() as that would allow more granular control over the JOIN part of the statement.

  • 0 in reply to Angel Magana

    Thanks Angel. Is that documented anywhere? I can't find any info on it.

    Is it as simple as $Query->joinRaw('left join tcx_productmappings_tcx_products_c as p on p.tcx_productmappings_tcx_productstcx_productmappings_idb = tcx_productmappings.id')

    I'll test it when I get a chance.

    Thanks again, -pat

  • 0 in reply to Pat Pawlowski

    That did work except the following didn't work:

            $Query->select(array('id','name',array('p.tcx_productmappings_tcx_productstcx_products_ida', 'product_id')));

    It did not seem to like the alias and only returned the id and name columns. Since the column name itself was ridiculously unique I just used it sans alias and was good.

            $Query->select(array('id','name','tcx_productmappings_tcx_productstcx_products_ida'));

    Thanks again, -pat

  • 0 in reply to Angel Magana

    Hello Pat Pawlowski

    Here is the good link and I think you will get the solution of your query from this link section of "N-Deep Joins".
    SugarCRM Cookbook – SugarQuery – The Basics « Sugar Developer Blog – SugarCRM

    Thanks

  • 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.