Create one-to-many relationship in code, no middle table required

sugarcrmdevelopers —  August 15, 2012 — 26 Comments

I came across this post in the forums the other day…

I have two modules created in Module Builder
Subscribers and SubscribersInfo
SubscriberInfo module has “subscriber_id” field.
I need to show all entries with matching subscriber_id from SubscriberInfo table as subpanel of Subscribers.
When I try creating relationship through Module Builder it creates additional table – “subscribers_subscribersinfo_c”.
Is there any way to create one-to-many relationship without additional table?

While we always recommend to use Module Builder for creating relationships, there’s definitely many areas in the product that we don’t and thus build these relationships without the intermediate table in between. There can even be a bit of performance boost without using the middle table, as it’s one less table join needing done when querying records.

Fortunately, this is pretty easy to do, and very similar to the technique used for creating flex relate fields. Let’s look at the code you need to add in a new file custom/Extension/modules/SubscribersInfo/Ext/Vardefs/SubscriberRelate.php…

There are three parts to this file. The first part defines the two fields for the relationship: subscriber_name which is the name field we are referencing from the Subscribers module, and subscriber_id which is the ID of the related subscriber. We next the link field for the relationship between the Subscriber and SubscriberInfo module. Finally, we define the actual relationship itself.

For the sake of completeness, you should also add the link field that references this relationship on the Subscribers module as well in new filec ustom/Extension/modules/Subscribers/Ext/Vardefs/SubscriberInfoRelate.php…

This will enable having a subpanel on the Subscribers DetailView to show all of the SubscriberInfo records.

Finally, do a quick repair and rebuild and you can add this record on any Detail, Edit, or ListView with ease.

26 responses to Create one-to-many relationship in code, no middle table required

  1. 

    There is a caveat with this as the ‘after_relationship_add’ and ‘after_relationship_delete’ logic hooks will not fire with this type of relationship

  2. 

    Great post!

    For PRO/ENT, if you create a relate field, this “relationship”
    will not create a subpanel in the relate module and in the Report module, you
    wil not be able to explore the linked bean for advanded criteria

    • 

      Yes, you need to define the subpanel in the other module’s subpaneldefs.php file in order to show the subpanel.

      Can you explain more the problem with reports?

      • 

        If you make a relate, you can’t browse into the linked module for advanced criteria in the report module
        For example, you make a custom relationship between contacts and an opportunity for define in a opportunity the main Contact.
        In the report module, try to make a report about the opportunities who have a Contact in the Country “France”

      • 

        Can you explain where exactly this subpaneldefs.php file goes and what its contents would be for the example at the top of this page? I’m struggling with this. Nothing seems to work. Thank you.

  3. 

    What about a way to create many-to-many relationship and putting additional fields in the middle table?
    Can you help pointing this out?

  4. 

    Would this also work if the new module needs to relate to an existing custom module? Could it be related to two existing modules?

  5. 

    Would this also work for Sugar 7?
    How do you do this on Sugar 7?

  6. 

    Hi I’m having trouble creating one to many relationship from a custom module to calls. I followed your tutorial and i cant get it done. Thanks

  7. 

    How to fetch such a data which is joined and shown as Panel through REST API?

Trackbacks and Pingbacks:

  1. Creating Subpanel for Campaigns module - December 18, 2012

    […] […]

  2. SugarCRM 1-2-many relationship with no middle table | critical_log - June 19, 2013

    […] Create one-to-many relationship in code, no middle table required […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s