Sugar Developer Guide Version 5.1

Relationship Metadata

If you have a many-to-many relationship, a table must exist for the relationship. If this is a new relationship then you must add the details of the relationship file (accounts_bugsMetaData.php in this example) to TableDictionary.php in the /modules directory. You must then run Repair Database to create the new relationships table (accounts_bugs in the example below). To remain upgrade-safe you must put your custom changes into /custom/application/ext/tabledictionary/tabledictionary.ext.php.

In the ./metadata directory, the relationship must exist and included in the $dictionary array. To keep consistent with the above accounts_bugs example, here is the content of the accounts_bugsMetaData.php

$dictionary['accounts_bugs'] = array(

'table' => 'accounts_bugs', //the table that is created in the database

'fields' => array (

array('name' =>'id', 'type' =>'varchar', 'len'=>'36',), // the unique id for the relationship

array('name' =>'account_id', 'type' =>'varchar', 'len'=>'36'), // the id for the account

array('name' =>'bug_id', 'type' =>'varchar', 'len'=>'36'), // the id for the bug

array('name' => 'date_modified','type' => 'datetime'), // necessary

array('name' =>'deleted', 'type' =>'bool', 'len'=>'1', 'required'=>true, 'default'=>'0') // necessary

),

// the indices are necessary for indexing and performance

'indices' => array (

array('name' =>'accounts_bugspk', 'type' =>'primary', 'fields'=>array('id')),

array('name' =>'idx_acc_bug_acc', 'type' =>'index', 'fields'=>array('account_id')),

array('name' =>'idx_acc_bug_bug', 'type' =>'index', 'fields'=>array('bug_id')),

array('name' => 'idx_account_bug', 'type'=>'alternate_key', 'fields'=>array('account_id','bug_id'))

),

'relationships' => array(

'accounts_bugs' => array(

'lhs_module'=> 'Accounts', // the left hand module - should match $beanList index

'lhs_table'=> 'accounts', // the left hand table name

'lhs_key' => 'id', // the key to use from the left table

'rhs_module'=> 'Bugs', // the right hand module - should match $beanList index

'rhs_table'=> 'bugs', // the right hand table name

'rhs_key' => 'id', // the key to use from the right table

'relationship_type'=>'many-to-many', // relationship type

'join_table'=> 'accounts_bugs', // join table - table used to join items

'join_key_lhs'=>'account_id', // left table key - should exist in table field definitions above

'join_key_rhs'=>'bug_id' // right table key - should exist in table field definitions above

)

)

)