AnsweredAssumed Answered

How can I use joinTable properly?

Question asked by Duncan Turnbull on Jun 18, 2019

Hi there.

I am making a custom sub panel to display accounts that are related to an opportunity but are't the paying account

There is a custom 1 to many relationship from Accounts to Opportunities

In opportunities there is a custom field which is a related link to the opportunities and shows up in opportunities_cstm as account_id1_c


What I want is for an Account give me the opportunities that have opportunity_cstm.account_id1_c = the account id where = opportunity_cstm.idc


The logic I can see in the relationships is

This relationship is called from Accounts so base id is the account I am looking at

Its looking at opportunities as thats the end result I want

The link is through opportunities_cstm which has the account id link field I need account_id1_c

JoinTable keeps adding an extra condition that the joined = the master module.bean id i.e. the account id. I don't know why or how to get rid of it


Perhaps this is no longer the way to do custom subpanels?


All help gratefully appreciated. Details below


I have made a custom relationship



$dictionary["Account"]["fields"]["accounts_opportunities_1"] = array (
  'name' => 'accounts_opportunities_1',
  'type' => 'link',
  'relationship' => '',
  'source' => 'non-db',
  'module' => 'Opportunities',
  'bean_name' => 'Opportunity',
  'id_name' => 'accounts_opportunities_1accounts_ida',
  'link-type' => 'many',
  'side' => 'left',
  'link_file' => "custom/modules/Accounts/OpportunitiesForAccountLink.php",
  'link_class' => "OpportunitiesForAccountLink",


Modified the label



$mod_strings['LBL_OPPORTUNITIES_FOR_ACCOUNT_SUBPANEL_TITLE'] = 'All related Opportunities that are not Paying';


Edited the subpanel defintitions


// created: 2018-03-07 14:38:22
$viewdefs['Accounts']['base']['layout']['subpanels']['components'][] = array (
  'layout' => 'subpanel',
  'context' =>
  array (
    'link' => 'accounts_opportunities_1',


And I get to the relationship and its now all based on using joinTable instead of joinRaw and I am struggling with getting the syntax right


public function buildJoinSugarQuery($sugar_query, $options = array())
$joinParams = array('joinType' => isset($options['joinType']) ? $options['joinType'] : 'INNER');
$jta = 'accounts_opportunities_1';
if (!empty($options['joinTableAlias'])) {
   $jta = $joinParams['alias'] = $options['joinTableAlias'];
// $sugar_query->joinTable($this->getCustomJoin($options), $joinParams);


$sugar_query->joinTable('opportunities_cstm', $joinParams)->on()->equalsField('id',$jta.'.id_c');
$sugar_query->where()->equals($jta.'.account_id1_c', $this->focus->id);

$preparedStmt = $sugar_query->compile();
return $GLOBALS['log']->debug($preparedStmt->getParameters());



What happens is that the join is almost there but it adds a condition for the join table to equal the module id which isn't correct. 

An exception occurred while executing

'SELECT, opportunities.date_modified, opportunities.assigned_user_id, opportunities.created_by

FROM opportunities

INNER JOIN opportunities_cstm jt0_accounts_opportunities_1 ON ( = jt0_accounts_opportunities_1.id_c) AND ( = ?)

LEFT JOIN opportunities_cstm opportunities_cstm ON opportunities_cstm.id_c =

WHERE (jt0_accounts_opportunities_1.account_id1_c = ?) AND (opportunities.deleted = ?)

ORDER BY opportunities.date_modified DESC, DESC LIMIT 11'

with params ["294060bc-252d-11e9-8c21-022504f1067d", 0]:

Unknown column '' in 'on clause'\

The additional problem statement is bolded. Everything works without that statement. There is no id field in the opportunities_cstm, but even if there was it has nothing to link to and the test expects it to match the accounts module id. 

How do I arrange this so I can get the join right - is it even possible now?


Thanks again