Announcement

Collapse
No announcement yet.

Adding columns to middle tables in many-to-many relationships

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Adding columns to middle tables in many-to-many relationships

    This tutorial explains how to:
    • Add a middle column (in Espo language, it is called a mid key) to a many-to-many relationship.
    • Add the mid key to the UI to allow it to be seen and modified by users.
    • Update the mid key using the query builder via PHP. The situation: I want to relate Contacts (existing entity) to Vendors (custom entity). The Contact should have a Role which is different for each Vendor. Note: This is the same situation as what exists between Contacts and Accounts.

    An example of the requirement is as follows:
    • Michael Scott is a Contact.
    • Dunder Mifflin and Michael Scott Paper Company are Vendors.
    • At Dunder Mifflin, Michael Scott is the Regional Manager. At Michael Scott Paper Company, Michael Scott is the President. In each Vendor detail view, I want to see Michael Scott listed with the correct title.

    For this tutorial, I have created the Vendor entity in a module called MyModule. All files related to Vendor live in custom/Espo/Modules/MyModule/. Because Contact is an existing entity, the customizations will be placed in custom/Espo/Custom/. Modifying existing entities works because the associated json files are merged into the files that come with Espo. If you choose to not use a module, place the files for Vendor in custom/Espo/Custom/ as well. Everything else is the same.

    Part 1: Creating the relationship and adding a column to the middle table
    • Create the custom Vendor entity using the normal tools and methods.
    • Add a many-to-many relationship between Contact and Vendor. In the following image, I created the relationship from within the Relationship area of the Vendor entity.
    Click image for larger version  Name:	definition.png Views:	4 Size:	53.9 KB ID:	98598
    • The important settings are as follows:
      • Relationship names: "vendors" and "contacts"
      • Middle table name: "contactVendor"
      • Labels: These are not important, but use "Vendors" and "Contacts" to make things easier, at least at first.
      • IMPORTANT: Add a Link Multiple Field on the right side for Contacts. The additional column in the middle table can also be used in list views, but this tutorial requires a Link Multiple Field.
    • At this point, you should see the following sections of code in your files:

    In custom/Espo/Modules/MyModule/Resources/metadata/entityDefs/Vendor.json:
    Code:
    {
        ...
        "fields": {​
            ...
            "contacts": {
                "type": "linkMultiple",
                "layoutDetailDisabled": true,
                "layoutMassUpdateDisabled": true,
                "layoutListDisabled": true,
                "noLoad": true,
                "importDisabled": true,
                "exportDisabled": true,
                "customizationDisabled": true,
                "isCustom": true
            },
        },
        "links": {​
            ...
            "contacts": {
                "type": "hasMany",
                "relationName": "contactVendor",
                "foreign": "vendors",
                "entity": "Contact",
                "audited": false,
                "isCustom": true
            }
        }
    }​
    In custom/Espo/Custom/Resources/metadata/entityDefs/Contact.json:​
    Code:
    {
        ...
        "links": {
            ...
            "vendors": {
                "type": "hasMany",
                "relationName": "contactVendor",
                "foreign": "contacts",
                "entity": "Vendor",
                "audited": false,
                "isCustom": true
            }
        },
        "fields": {
           ...
            "vendors": {
                "type": "linkMultiple",
                "layoutDetailDisabled": true,
                "layoutMassUpdateDisabled": true,
                "layoutListDisabled": true,
                "noLoad": true,
                "importDisabled": true,
                "exportDisabled": true,
                "customizationDisabled": true,
                "isCustom": true
            }
        }
    }​
    • At this point, the relationships are functional. You can relate entities to each other using the regular methods.
    • The middle table is currently defined as follows:
      • id - The ID of the row in the middle table
      • contactId - The ID of the associated Contact record
      • vendorId - The ID of the associated Vendor record
      • deleted - The flag to set the row as deleted
    • Add the following code to custom/Espo/Modules/MyModule/Resources/metadata/entityDefs/Vendor.json:​
    Code:
    {
        ...
        "links": {​
            ...
            "contacts": {
                ...
                "additionalColumns": {
                  "role": {
                      "type": "varchar",
                      "len": 40
                  }
                },
            }
        }
    }​
    • The middle table is now defined as follows:
      • id - The ID of the row in the middle table
      • contactId - The ID of the associated Contact record
      • vendorId - The ID of the associated Vendor record
      • deleted - The flag to set the row as deleted
      • role - The additional column for the many-to-many relationship.


    Stopping Point
    • ​​​​​​If you don't need to show the value of the additional column to the user, you can stop here.
    • If you need to show the value of the additional column to the user, continue with the tutorial.


    Part 2 - Showing the additional field in the UI

    In summary, the relationship and additional column in the middle table have been established. What follows is the process for showing the value of additional column to the user. To accomplish that, each entity (Contact and Vendor) must be modified. Some of the modifications involve telling Espo to use non-storable fields. In other words, the fields are populated by relationships instead of directly from the database, which is the more typical method.

    The next steps involve modifying the Vendor entity, which happen in custom/Espo/Modules/MyModule/Resources/metadata/entityDefs/Vendor.json:
    • Add a new field called contactRole to store the value in the role field of the middle table. The new field is not storable, which means it is indirectly populated by Espo instead of directly through a call to the database. You can name this field whatever you want as long as it is unique in terms of the fields for the Vendor entity.​​
    • Add a property called columnAttributeMap to the link for the Contact relationship. It must contain a key-value pair.
      • The key of the property, role, must match the name of the new field in the middle table.
      • The value of the property, contactRole, must match the name of the new field of the Vendor entity.
    • Add several properties to the contacts field and modify some of the existing properties. I don't know what several of the properties do, so I won't try to explain. Copy, paste, and modify to fit your situation. Note: The value of the role property is vendorRole; this value must match the name of the non-storable field in the Contact entity.
    • For now, we are using a default view called views/fields/link-multiple-with-role, which is in client/src/fields/views/link-multiple-with-columns.js.

    Code:
    {
        ...
        "fields": {​
            ...
            "contacts": {
                "type": "linkMultiple",
                "layoutDetailDisabled": false,
                "layoutMassUpdateDisabled": false,
                "layoutListDisabled": false,
                "noLoad": false,
                "importDisabled": false,
                "exportDisabled": false,
                "customizationDisabled": false,
                "columns": {
                    "role": "vendorRole"
                },
                "additionalAttributeList": [
                    "columns"
                ],
                "view": "views/fields/link-multiple-with-columns",
                "default": "javascript: return {contactsIds: []}",
                "isCustom": true
            },​
            "contactRole": {
                "type": "varchar",
                "notStorable": true,
                "utility": true,
            }
        },
        "links": {​
            ...
            "contacts": {
                ...
                "columnAttributeMap": {
                  "role": "contactRole"
                }​
            }
        }
    }​
    ​​

    A similar list of steps must be implemented for the Contact entity in custom/Espo/Custom/Resources/metadata/entityDefs/Contact.json:
    • Add a new field to store the text in the UI and an additional property to the link for the Vendor relationship.
    • The new property for the link is called columnAttributeMap. It must contain a key-value pair.
      • The key of the property, role, must match the name of the field in the middle table.
      • The value of the property, vendorRole, must match the name of the new field of the Vendor entity (see below).
    • The new field, vendorRole, is not storable, which means it is indirectly populated by Espo instead of directly through a call to the database. You can name this field whatever you want as long as it is unique in terms of the fields for the Contact entity.

    Code:
    {
        ...
        "fields": {​
            ...
            "vendorRole": {
                "type": "varchar",
                "notStorable": true,
                "utility": true
            }
        },
        "links": {​
            ...
            "contacts": {
                ...
                "columnAttributeMap": {
                  "role": "vendorRole"
                }​
            }
        }
    }​
    ​​​

    Stopping Point
    • ​​​​​​If you need role to be a free-from text field, you can stop here.
    • ​​​​​​If you need to create a list of options for the role field continue with this tutorial.​
    The role field is now visible, editable, and functional in the Vendor detail view as a free-from textbox. Users can enter any value in the role field and save the record, which will insert the value into the middle table.

    Remember to set the other files as necessary:
    • Layouts - Detail, List, Search, and other views
    • Languages and labels
    It is possible to create a bottom panel that shows the value of the role field. You would need to use the vendorRole field for the view of the contact record. In this example, I added the field to the Vendor detail view, which looks like this:

    Click image for larger version

Name:	Screenshot 2023-10-14 at 13.04.41.png
Views:	260
Size:	35.4 KB
ID:	98599
    Last edited by bandtank; 10-14-2023, 07:18 PM.

  • #2
    Part 3: Change the role field to be an enum instead of a varchar to give the user a list of options

    Changing role from varchar to enum allows you to present the user with a list of options. You can accomplish this in the following ways:
    • Store a list of generic options that every Contact to Vendor relationship will have to use for the value of role
      • The list of options could be Pizza, Cheese, and Paper.
      • Every Contact that relates to a Vendor will have to choose between Pizza, Cheese, and Paper as the value of role.
    • Store a list of custom options in each Vendor record, which allows the user to set a value for role that is stored specifically in the related Vendor record.
      • The list of options is stored in the Vendor record. Vendor A could store Desk, Chair, and Table as the options while Vendor B could store Grass, Tree, and Flower as the options.
      • Contacts related to Vendor A would be able to choose from Desk, Chair, and Table as the value of role. Contacts related to Vendor B would be able to choose from Grass, Tree, and Flower as the value of role.
      • This method is what you see when you relate a User to a Team.
    I will update this tutorial in the future to explain the steps involved with each process. The newer view called link-multiple-with-columns implements enums in a different way than the older view called link-multiple-with-role. I do not yet know how to use the newer view to accomplish both methods. For now, you can see an example of each method by searching for the aforementioned views in client/src/.​
    Last edited by bandtank; 10-15-2023, 03:22 PM.

    Comment


    • #3
      To update the value of role using the ORM, there are multiple methods.

      Method 1 - relate()
      PHP Code:
      $vendor $this->em->getRDBRepository(Vendor::ENTITY_TYPE)->where(...)->findOne();
      $contact $this->em->getRDBRepository(Contact::ENTITY_TYPE)->where(...)->findOne();

      $this->em->getRDBRepository(Vendor::ENTITY_TYPE)->getRelation($vendor'contacts')->relate($contact, array('role' => "New Role Here"));​ 

      Method 2 - queryBuilder()
      PHP Code:
      $vendor $this->em->getRDBRepository(Vendor::ENTITY_TYPE)->where(...)->findOne();
      $contact $this->em->getRDBRepository(Contact::ENTITY_TYPE)->where(...)->findOne();

      $updateQuery 
      $this->em->getQueryBuilder()->update()->in('ContactVendor')
      ->
      set(array('role' => "New Role Here"))
      ->
      where(array(
        
      'contactId' => $contact->getId(),
        
      'vendorId' => $vendor->getId(),
      ))
      ->
      build();

      $this->em->getQueryExecutor()->execute($updateQuery);​ 

      To find records based on the value of role using the ORM, there are multiple methods.​

      For example, this is a filter in custom/Espo/Modules/MyModule/Classes/Select/Vendor/BoolFilters/OnlyOwners.php that finds Vendors with Contacts who have a role of Owner.
      Code:
      <?php
      
      namespace Espo\Modules\MyModule\Classes\Select\Vendor\BoolFilters;
      
      use Espo\Core\Select\Bool\Filter;
      
      use Espo\ORM\Query\{
        SelectBuilder,
        Part\Where\OrGroupBuilder,
        Part\WhereClause,
      };
      
      class OnlyOwners implements Filter
      {
        public function __construct() {
        }
      
        public function apply(SelectBuilder $queryBuilder, OrGroupBuilder $orGroupBuilder): void
        {
          $queryBuilder
            ->leftJoin('contactVendor', 'cv', ['cv.vendorId:' => 'vendor.id'])
            ->leftJoin('contact', 'c', ['cv.contactId:' => 'c.id']);
      
          $orGroupBuilder->add(
            WhereClause::fromRaw(array(
              'cv.role' => "Owner",
            ))
          );
        }
      }​
      Here is a query builder statement on its own:
      Code:
      $selectQuery = $this->em->getQueryBuilder()
        ->select(['id'])
        ->from(Vendor::ENTITY_TYPE)
        ->leftJoin('contactVendor', 'cv', ['cv.vendorId:' => 'vendor.id', 'cv.deleted' => false, ])
        ->leftJoin('contact', 'c', ['cv.contactId:' => 'c.id', 'c.deleted' => false, ])
        ->where(['cv.role' => "Owner"])
        ->build();
      
      $pdoStatement = $this->em->getQueryExecutor()->execute($selectQuery);
      $rows = $pdoStatement->fetchAll(\PDO::FETCH_ASSOC);​
      Last edited by bandtank; 10-14-2023, 07:55 PM.

      Comment


      • #4
        Part 4: Add custom list views in related entities that show the role

        To create a custom list layout for an entity in another entity, you must first create the new layout. In this example, I will create a new list layout for the Account entity, which will be viewed in the Contact entity. I'm using Account and Contact because they have a more difficult relationship to manage than any of the other entities.

        Goal: Display the list of Accounts related to a contact in the relationship section of a Contact record with the associated title/role.

        Step 1 - Create the new layout
        Create the following file: custom/Espo/Custom/Resources/layouts/Account/listForContact.json. The file defines a layout that will be used to display Accounts in the relationship panel of a Contact record. Add this code to the file:
        Code:
        [
            {
                "name": "name",
                "link": "true"
            },
            {
                "name": "contactRole"
            },
            {
                "name":"emailAddress"
            },
            {
                "name":"phoneNumber"
            }
        ]​
        Note the name of the second field. contactRole is defined in application/Espo/Modules/Crm/Resources/metadata/entityDefs/Account.json. It is populated by the string from the middle table column called role in the accountContact table, which is the same concept as the role field we defined earlier for the Vendor to Contact relationship in the vendorContact table.

        Step 2 - Define the layout
        Now that the layout file has been created, it must be made available as one of the layouts for the Account entity. Create or edit the following file: custom/Espo/Custom/Resources/metadata/clientDefs/Account.json. Add the following code:
        Code:
        {
            ...
        
            "additionalLayouts": {
                ...
                "listForContact": {
                    "type": "listSmall"
                }
            }​
        }​
        You should now see the layout in the UI:

        Click image for larger version  Name:	Screenshot 2023-10-17 at 10.22.42.png Views:	0 Size:	130.2 KB ID:	98709

        Because the contactRole (shown above as Title) field only makes sense in the context of a contact, we must limit the availability of the field to only be shown in the list layout called listForContact. The field is defined by default here: application/Espo/Modules/Crm/Resources/metadata/entityDefs/Account.json. Create or edit a custom entity definition file here: custom/Espo/Custom/Resources/metadata/entityDefs/Account.json. Add the following code:
        Code:
        {
            ...
            "fields": {
                ...
                "contactRole": {
                    "layoutAvailabilityList": ["listForContact"]
                }
            },
            ...
        }​
        Step 3 - Use the layout in the relationship panel for Contacts
        Create or edit the following file: custom/Espo/Custom/Resources/metadata/clientDefs/Contact.json. Add the following code:

        Code:
            "relationshipPanels": {
                "contacts": {
                    "layout": "listForContact",
                    "orderBy": "name"
                },
                ...
            },​
        There is one more critical step before the list will work correctly. The accounts field in the Contact entity is not allowed to be added to a relationship panel due to the default entity definition of the Contact entity. You must reverse that setting by creating or editing the following file: custom/Espo/Custom/Resources/metadata/entityDefs/Contact.json. Add the following code:

        Code:
        {
            ...​
            "links": {
                ...
                "accounts": {
                    "layoutRelationshipsDisabled": false
                }​,
                ...
        }​
        Changing the setting for layoutRelationshipDisabled to false is a step you do not need to take for the Vendor to Contact relationship. At this point, you should see the roles in the relationship panel of the Contact entity for the related Accounts. Notice the roles are the same as the roles for the contacts in the Accounts field of the Overview panel:

        Click image for larger version  Name:	Screenshot 2023-10-17 at 10.49.40.png Views:	0 Size:	285.2 KB ID:	98710

        Step 4 - Change the label
        Create or edit the file here: src/files/custom/Espo/Custom/Resources/i18n/en_US/Account.json. Add the following code:

        Code:
        {
            "fields": {
                ...
                "contactRole": "Role"
            },
            ...
        }​
        Set the value for contactRole. You should now see the new label in the UI:

        Click image for larger version  Name:	Screenshot 2023-10-17 at 10.53.19.png Views:	0 Size:	71.6 KB ID:	98711

        I would suggest removing the Accounts field from the Overview panel to prevent the same information from being displayed twice.
        Last edited by bandtank; 10-17-2023, 04:54 PM.

        Comment


        • espcrm
          espcrm commented
          Editing a comment
          Look good banktank! Don't mean to be ungrateful but would you also post the end result screenshot?

        • bandtank
          bandtank commented
          Editing a comment
          espcrm I will post screenshots to each section as I finish the tutorial.

        • espcrm
          espcrm commented
          Editing a comment
          *subscribe*

      • #5
        this one is helpful. thank you so much

        Comment


        • #6
          First of all, thank very much bandtank. Nonetheless, after having completed Part. 2, I still don't have any value in the new column "role" in the middle table. I am using EspoCRM version 8.0.5. While EspoCRM is a fantastic tool, still there are parts where one does things without having a clear knowledge of what he is doing. I also read the EspoCRM documentation regarding entityDefs but still it is difficult to understand how to use a feature like AdditionalColumns without a complete example. I just know that to configure the custom entity you can add something like:

          Code:
          "additionalColumns": {
              "role": {
                  "type": "varchar",
                  "maxLength": 64 
              }
          }
          The only difference with the bandtank guide is that at Part. 2 I don't have a "contacts" object in the "links" part of Contact.json configuration file

          Code:
          "contacts": {
          ...
              "columnAttributeMap": {
                  "role": "vendorRole"
              }​
          }
          Does anyone have any suggestion to have values in the column "role" of the vendor_contact table whenever I add a new vendor?
          Last edited by Letsgetlost; 11-28-2023, 03:43 PM.

          Comment


          • #7
            This is a complete example. The relationship in the links area is required for the additional column to populate. You can add the relationship in the Entity Manager through the GUI if you are not familiar with the structure of the json file(s).

            Regarding your second question, default values can be populated by adding a default to the additionalColumns.role field. You can also use javascript. It depends how you want to solve the problem. More context and code would be required for a more complete answer to be provided.

            When posting on the forum, it is helpful if you use CODE tags to make the code easier to read.
            Last edited by bandtank; 11-28-2023, 03:24 PM.

            Comment


            • #8
              When I wrote about complete example I was speaking in general regarding the official documentation (you also noticed that there for several properties it is not clear what they do) and I appreciate very much your guides because they are an important reference when one dare to customize EspoCRM.

              My main problem is that I don't know why I don' have values in the "role" column of the middle table when I add a new vendor record.
              What I have understood (please correct me if I am wrong) so far is:
              1. At the end of part.1 a new column (named role) has been added to the middle table. I can confirm that I have the new column.
              2. At the end of part. 2 every time a new vendor record is added, the middle table is also updated with a new record which contains a value for all the columns. In my case the "role" column has always a null value
              The only difference from my setup and the instruction in part. 2 of your guide is on the "Contact.json" file because I don't understand why, in the code you provided on the guide, in the "links" part of the Contact entity there is a reference to "contacts":

              Code:
              "contacts": {
              ...
                  "columnAttributeMap": {
                      "role": "vendorRole"
                  }​
              }​
              In my Contact.json file there is only a field of type "linkMultiple" i.e. "vendors" and consequently there is only the object "vendors" in the "links" section.

              Can you confirm that I didn't miss any important configuration step and that at the end of Part. 2 the "role" column should have values?

              Thank you very much.



              Originally posted by bandtank View Post
              This is a complete example. The relationship in the links area is required for the additional column to populate. You can add the relationship in the Entity Manager through the GUI if you are not familiar with the structure of the json file(s).

              Regarding your second question, default values can be populated by adding a default to the additionalColumns.role field. You can also use javascript. It depends how you want to solve the problem. More context and code would be required for a more complete answer to be provided.

              When posting on the forum, it is helpful if you use CODE tags to make the code easier to read.

              Comment


              • #9
                The link to vendorRole is required because that is how the system knows which field to use when storing the value in the middle table. Earlier in contact.json, you had to define a field called vendorRole as a varchar. WIthout the last section, the value you type in the GUI to the vendorRole field will not be stored in the database.

                Comment

                Working...
                X