Vardefs join_name

SuiteCRM Forum - English Language Developer Collaboration
1

Hello. Can anyone enlighten me as to what I should set “join_name” to in a field of “relate” type? It normally seems to be the same as the table name. I am adding a new link to a child table, where there is already a link to the same parent table. For the sake of argument, I have a link from Pupil (child) to School (parent), and I want the ability to link the same Pupil to a second School. So in my “relationships” array I have copied my existing “school_pupils” section to a “school_pupils_2” section with a different rh key, then copied and amended the original “link” and “relate” sections to add the new link. Most of the properties are obvious, e.g. the new “link” section has a “relationship” property which points to “school_pupils_2” instead of “school_pupils”, and the associated “relate” section has a “link” property which points to the new link etc. But I cannot work out what I should set “join_name” to. Should it be the same as the table name , or the same as the link, or something else altogether?

2

I don’t the answer, but I know a trick that might help.

Have a look in the cached vardefs for that module. It contains a compiled list of all the relationship names and should give you ideas on which things you can try.

Under cache/modules, for example

cache/modules/Contacts/Contactvardefs.php

3

Hi @tomglare,

Unless if you want to limit the number of relations between pupil and schools, it would be preferable to create a many-to-many relationship between Pupil and School.

Considering “join_name”: this is an alias for the related table and simplifies the query that SuiteCRM generates. It is not obligatory, if you don’t define it, then SuiteCRM will create an alias itself.

The effect can be demonstrated best by adding two relate fields from the same module.

Setup

Suppose you want to expose the account fields “billing address city” and “billing address postalcode” to the contact module so you can list them and search with those fields, you could do that by adding following file:

custom/Extension/modules/Contacts/Ext/Vardefs/account_fields.php

<?php
$dictionary['Contact']['fields']['account_billing_address_postalcode'] = array (
        'name' => 'account_billing_address_postalcode',
        'id_name' => 'account_id',
        'type' => 'relate',
        'rname' => 'billing_address_postalcode',
        'vname' => 'LBL_ACCOUNT_BILLING_ADDRESS_POSTALCODE',
        'link' => 'accounts',
        'module' => 'Accounts',
        'source' => 'non-db',
        'studio' => array('editview'=>false, 'quickcreate'=>false),
        'inline_edit' => false,
        'massupdate' => false,
//      'join_name'=>'accounts',
);

$dictionary['Contact']['fields']['account_billing_address_city'] = array (
        'name' => 'account_billing_address_city',
        'id_name' => 'account_id',
        'type' => 'relate',
        'rname' => 'billing_address_city',
        'vname' => 'LBL_ACCOUNT_BILLING_ADDRESS_CITY',
        'link' => 'accounts',
        'module' => 'Accounts',
        'source' => 'non-db',
        'studio' => array('editview'=>false, 'quickcreate'=>false),
        'inline_edit' => false,
        'massupdate' => false,
//      'join_name'=>'accounts',
);

and the following file:
custom/Extension/modules/Contacts/Ext/Language/en_us.account_fields.php

<?php
$mod_strings['LBL_ACCOUNT_BILLING_ADDRESS_POSTALCODE'] = 'Account Billing Postal Code';
$mod_strings['LBL_ACCOUNT_BILLING_ADDRESS_CITY'] = 'Account Billing City';

  • Do an Admin / Repair / Quick Repair and Rebuild

  • Set Admin / System Settings / Log Level to “Info”

  • In Admin / Studio / Contacts add the fields “Account Billing Postal Code” and “Account Billing City” to the List View and to Filter / Advanced Filter.

  • Create Account1: postal code “11111” and city “london”

  • Create Account2: postal code “22222” and city “manchester”

  • Create Account3: postalcode “11111” and city “manchester”

  • Create Contact1, link it to Account3

  • Create Contact2, link it to Account1 and to Account2 (you can link a contact to multiple accounts by going into each account and add the contact in the subpanel Contacts)

Open the Contacts list view

You will see Contact2 eight times. This is due to the combinations of the three fields: “Account Name” (Account1, Account2), “Account Billing Postal Code” (11111, 22222) and “Account Billing City” (london, manchester)

If you look in the file
suitecrm.log
Among other queries you will find the query:

SELECT  contacts.id , … 
accounts.name account_name, …
jt2.billing_address_postalcode account_billing_address_postalcode , 
jt3.billing_address_city account_billing_address_city, …
FROM contacts 
…
LEFT JOIN  accounts_contacts jtl0 ON contacts.id=jtl0.contact_id AND jtl0.deleted=0
LEFT JOIN  accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0 AND accounts.deleted=0
…
LEFT JOIN  accounts_contacts jtl2 ON contacts.id=jtl2.contact_id AND jtl2.deleted=0
LEFT JOIN  accounts jt2 ON jt2.id=jtl2.account_id AND jt2.deleted=0 AND jt2.deleted=0  

LEFT JOIN  accounts_contacts jtl3 ON contacts.id=jtl3.contact_id AND jtl3.deleted=0
LEFT JOIN  accounts jt3 ON jt3.id=jtl3.account_id AND jt3.deleted=0 AND jt3.deleted=0
…

Because no “join_name” was defined for account billling postal code and account billing city, SuiteCRM generated the aliases “jt2” and “jt3” for them. Because it now searches through three links (“accounts” for account name and “jt2” and “jt3”), it finds 2 x 2 x 2 accounts, leading to eight entries for Contact2 in the list view.

Open the Advanced Filter

  • For Account Billing Postal Code set “11111”
  • For Account Billing City set “manchester”
  • Click “Search”

In the file suitecrm.log you will now see a similar query ending with:

where ((jt2.billing_address_postalcode like '11111%' ) 
 AND ( jt3.billing_address_city like 'manchester%'
…

Accordingly you will see Contact1 and Contact2 in the search results, although Contact2 is not linked to Account3, the only account having Postal Code “11111” and City “manchester”.
However, because Contact2 is linked to Account1 having Postal Code “11111” and linked to Account2 having city “manchester”, it will show up in the results, as it searches through different account aliases.

Add a join name

  • In custom/Extension/modules/Contacts/Ext/Vardefs/account_fields.php
    uncomment the line with “join_name” for both “account_billing_address_postalcode” and “account_billing_address_city”.
    This sets the join name for both relate fields to “accounts” which is the same join name used for the field “account name” in the file modules/Contacts/vardefs.php
  • Do an Admin / Repair / Quick Repair and Rebuild
  • Open the Contacts list view

In the file suitecrm.log you will now see a query:

SELECT  contacts.id , …
accounts.name account_name, …
accounts.billing_address_postalcode account_billing_address_postalcode , 
accounts.billing_address_city account_billing_address_city, …
FROM contacts
…
LEFT JOIN  accounts_contacts jtl0 ON contacts.id=jtl0.contact_id AND jtl0.deleted=0
LEFT JOIN  accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0 AND accounts.deleted=0
…
where ((accounts.billing_address_postalcode like '11111%' )
 AND ( accounts.billing_address_city like 'manchester%'))
…

You will now no longer see Contact2 in the search results, because the requirement is that it needs to be linked to an account having both postalcode “11111” and city “manchester”

  • Clear the filters

You will see Contact2 now two times instead of eight:
one time with “Account1”, account billing postal code “11111”, account city “london” and
one time with “Account2”, account billing postal code “22222”, account city “manchester”

To summarize

  • If you add multiple relate fields from the same relation, then it is advisable to define a “join_name”. You can choose the name yourself, as long as it is the same for each relate field.
  • If you add relate fields from different relations (your case), then there is no need to set a “join_name”, as SuiteCRM will generate one itself.

By the way, the generated aliases jtl0, jtl1 etc. refer to the intermediate table between the modules, accounts_contacts. This alias can be set by the vardef field “join_link_name” and would be relevant if you define multiple “relationship fields”. These are defined in the table accounts_contacts.

2 Likes
4

Thankyou for that, pgr. Probably worth mentioning in passing that application in question is on a very old (7.6.10) version of SuiteCRM, so it might not be relevant to later versions. The only instances of join_name I could find being used were in connection with many-to-many relationships, but even here I can not see what it is doing, as their is already a separate property, “join_table”, which relates to that sort of link. In any case, I just made it the same as the link name and ran the “Quick Repair” procedure, and it has made the desired change to the database.

5

Many thanks for your trouble, jansiero - most appreciated. In this case, yes, the client specifically said that in most cases there would only be one school link, but occasionally there would be a second, and that was exactly what they wanted, so that is why I did it that way. Thanks again for the useful help. Regards, Tom

6

This is a Master Class and I’ve bookmarked it for future reference
:clap:

1 Like