Forums - Thread Details
  • State Not Answered
  • Replies 3 replies
  • Subscribers 322 subscribers
  • Views 1044 views
  • Users 0 members are here
Forum Actions
Related

How to join result set of custom query using joinTable or Join method

Brijesh Patil

We have made customizations to add custom subpanels under Accounts Module. Earlier in 7.8.2, we have used joinRaw method to join the result set of custom query. But while upgrading the instance from 7.8.2 to 7.9.5, we got healthcheck error for joinRaw. So we replaced joinRaw with joinTable method.

But joinTable does not allow to join custom query as below:

public function buildJoinSugarQuery($sugar_query, $options = array()) {

    $joinParams = array('joinType' => isset($options['joinType']) ? $options['joinType'] : 'INNER');

    $jta = 'accounts_commercialnew_1';

     if (!empty($options['joinTableAlias'])) {

           $jta = $joinParams['alias'] = $options['joinTableAlias'];

     }

      $sugar_query->joinTable($this->getCustomJoin($options), $joinParams);

      $sugar_query->where()->queryOr()

                                                                   ->between("shipmentdate",date("Y-m-d"),date("Y-m-d",strtotime("+30 day")))

                                                                   ->between("excinvdate_c",date("Y-m-d",strtotime("-365 day")),date("Y-m-d"))

                                                                   ->between("customerrequesteddate_c",date("Y-m-d"),date("Y-m-d",strtotime("+30 day")));

       return $sugar_query->join[$jta];

}

 

protected function getCustomJoin($params = array())

{

       $sql = " (";

       // Give me every opportunity

      $sql .= <<<MYCUSTOMQUERY

       SELECT

       commercialnew.id AS commer_id

        FROM

        commercialnew JOIN accounts_divis_commercialnew_1_c ON commercialnew.id =     accounts_commercialnew_1_c.accounts_commercialnew_1commercialnew_idb

WHERE accounts_commercialnew_1_c.deleted=0 AND accounts_commercialnew_1_c.accounts_commercialnew_1accounts_ida = '{$this->focus->id}'

MYCUSTOMQUERY;

 

$sql .= ") commercialnew_to_show ON commercialnew_to_show.commer_id = commercialnew.id";

return $sql;

}

 

How do I arrange this so I can get the join right ? Any help will be greatly appreciated

  • 0

    Can you post the entire query you are trying to achieve?

  • 0 in reply to Jeff Bickart

    We are trying to achieve below query:

    SELECT commercialnew.id id, commercialnew.date_modified date_modified, commercialnew.assigned_user_id assigned_user_id, commercialnew.created_by created_by FROM commercialnew
    JOIN (SELECT commercialnew.id AS commer_id FROM commercialnew JOIN accounts_commercialnew_1_c ON commercialnew.id = accounts_commercialnew_1_c.accounts_commercialnew_1commercialnew_idb WHERE accounts_commercialnew_1_c.deleted=0 AND accounts_commercialnew_1_c.accounts_commercialnew_1accounts_ida = '61395dc8-ecd4-11ea-97ed-0050568537b5') commercialnew_to_show ON commercialnew_to_show.commer_id = commercialnew.id
    LEFT JOIN commercialnew_cstm ON commercialnew_cstm.id_c = commercialnew.id
    WHERE commercialnew.deleted = 0 AND (commercialnew.shipmentdate BETWEEN '2020-09-15' AND '2020-10-15' OR commercialnew_cstm.excinvdate_c BETWEEN '2020-09-05' AND '2020-09-15' OR commercialnew_cstm.customerrequesteddate_c BETWEEN '2020-09-15' AND '2020-10-15') ORDER BY commercialnew.date_modified DESC,commercialnew.id DESC LIMIT 0,6

    I have tried to achive it as below:

    $q = new SugarQuery();
    $q->from(BeanFactory::newBean('CommercialNew'));
    $q->select(array('id', 'date_modified', 'assigned_user_id','created_by'));
    $q->joinTable($this->getCustomJoin($options), array('alias'=>$jta, 'joinType'=>"INNER", "linkingTable" => true))
    ->on()->equalsField($jta.'.commer_id', 'commercialnew.id', BeanFactory::newBean('CommercialNew'));
    $q->where()->queryOr()
    ->between("shipmentdate",date("Y-m-d"),date("Y-m-d",strtotime("+30 day")))
    ->between("excinvdate_c",date("Y-m-d",strtotime("-365 day")),date("Y-m-d"))
    ->between("customerrequesteddate_c",date("Y-m-d"),date("Y-m-d",strtotime("+30 day")));

    but we get below error:

  • 0 in reply to Brijesh Patil

    I took a quick peek at your query and I don't think you need a subquery - since you're doing a join back to the same table?

    You can just directly use joinTable with an alias, something like:

    $q->joinTable('accounts_commercialnew_1_c', array('alias' => 'acc_com'))->on()
        ->equalsField('commercialnew.id','acc_com.accounts_commercialnew_1commercialnew_idb')
        ->equals('acc_com.deleted',0)
        ->equals('acc_com.accounts_commercialnew_1accounts_ida','61395dc8-ecd4-11ea-97ed-0050568537b5');