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.
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.
- Change the ‘true_relationship_type’ value to ‘many-to-many’
- Adjust the ['indicies'] an ['indicies'] values ( 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', ), ),
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!