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.id = 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 table.id = 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

 

custom/Extension/modules/Accounts/Ext/Vardefs/accounts_opportunities_1_Accounts.php

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

 

Modified the label

 

custom/Extension/modules/Accounts/Ext/Language/en_us.opportunities_for_cases_subpanel.php
<?php

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

 

Edited the subpanel defintitions

 

custom/Extension/modules/Accounts/Ext/clients/base/layouts/subpanels/accounts_opportunities_1_Accounts.php
<?php
// created: 2018-03-07 14:38:22
$viewdefs['Accounts']['base']['layout']['subpanels']['components'][] = array (
  'layout' => 'subpanel',
  'label' => 'LBL_OPPORTUNITIES_FOR_ACCOUNT_SUBPANEL_TITLE',
  '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();
$GLOBALS['log']->debug($preparedStmt->getSql());
return $GLOBALS['log']->debug($preparedStmt->getParameters());

}

 

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

An exception occurred while executing

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

FROM opportunities

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

LEFT JOIN opportunities_cstm opportunities_cstm ON opportunities_cstm.id_c = opportunities.id

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

ORDER BY opportunities.date_modified DESC, opportunities.id DESC LIMIT 11'

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

Unknown column 'jt0_accounts_opportunities_1.id' 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

Outcomes