Converting a Relationship from one:many to many:many in SugarCRM

Editor’s Note: This post comes from the blog of Sugar Partner and Open+ Developer Program member Profiling Solutions. In this post, developer Matt Poer details changing a relationship from a one-to-many to a many-to-many

Requirements change. And sometimes you just click the wrong button in Studio. Now you have this wonderful one:many relationship between two modules, but what you really need now is many:many relationship. How can we convert the relationship without losing data?

It’s a multi-step process that will involve some superior SQL shogun-ism. If you’re not a code-level developer and part-time DBA, it may be a good idea to read through this one before attempting.

Terms:

For the sake of the discussion, let’s assume we’re talking about a one:many relationship that exists between Accounts and Contacts, where an Account can have several Contacts, but a single Contact many only have one Account.

Step One: Create the subpanel definition.

The one:many relationship only created one subpanel definition, because that’s all that’s required. In our case, the Account has the subpanel, and the Contact only has a field.

Remove the field from the detail and edit views (and anywhere else you might have put it) on the Contact module.

Then add the subpanel definition. This is as streight-forward as copying the existing one and renaming some things. Copy the file from custom/Extension/modules/Accounts/Ext/Layoutdefs/something.php to custom/Extension/modules/Contacts/Ext/Layoutdefs/something.php.

Now, in your new copy, switch “Accounts” with “Contacts” and “Contacts” with “Accounts” to reverse the references. You’ll see this in the $layoutdefs[“Accounts”], and the [‘module’] key. You’ll also want to change the [‘title_key’] key value to something relevant so that it outputs Accounts instead of Contacts.

Step Two: Modify the Relationship Metadata

When relationships are created, they’re housed in custom/metadata and the files are named something like accounts_contactsMetaData.php. Open up your Metadata file and make a few quick changes.

1. Change the ‘true_relationship_type’ value to ‘many-to-many’
2. Adjust the [‘indicies’][1] an [‘indicies’][2] values ([0] is the primary key, which is fine). Currently there are two keys created, but a many:many relationship only requires one.

1 => array (
'name' => 'accounts_contacts_ida1',
'type' => 'index',
'fields' => array (
0 => 'accounts_ida',
),
),
2 => array (
'name' => 'accounts_contacts_alt',
'type' => 'alternate_key',
'fields' => array (
0 => 'contacts_idb',
),
),

Will become

1 => array (
'name' => 'accounts_contacts_alt',
'type' => 'alternate_key',
'fields' => array (
0 => 'accounts_ida',
1 => 'contacts_idb',
),
),

Step Three: Modify the Relationship Table in the Database
Assuming the relationship is housed in a table called accounts_contacts, you’ll run a query similar to this:

alter table `accounts_contacts`
drop INDEX accounts_contacts_alt,
drop INDEX accounts_contacts_ida1,
add KEY `accounts_contacts_alt` (`accounts_ida`,`accounts_idb`);

You’ll likely have to adjust that heavily to suite your field names. You can run a ‘describe’ query on your table to get field names, and I found that the MySQL Workbench tool was helpful in diagnosing the need for this.

It’s worth noting that the next step (the repair/rebuild) may take care of this, but I’ve had awkward experiences with the SQL that this tool generated in the past. I wanted to handle this piece directly so that I a) fully understood what was going on and b) didn’t let a tool misguide me.

Step Four: Repair and Rebuild

The last step is the easiest. Cross your fingers and run a repair-rebuild. Test your new subpanels and see if you don’t have a many:many relationship functioning as expected.

If this helped you out, you owe me a high-five. Either way, feel free to leave questions or comments below!