Circular M:M relationship adds duplicate rows to the linking table

I'm trying to add a circular (relationship with itself) M:M relationship to the Product Templates module. I was to create it and the subsequent linking table and add the subpanels for both the left and right side of the relationship.

However, when I try to add/link/unlink records to any of the two subpanels, it is duplicated to the other. I checked the linking table, and 2 rows were added.

This is the vardefs for the relationship:

Fullscreen

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?php
$dictionary["ProductTemplate"]["fields"]["producttemplates_producttemplates_1_left"] = array (
  'name' => 'producttemplates_producttemplates_1_left',
  'type' => 'link',
  'relationship' => 'producttemplates_producttemplates_1',
  'source' => 'non-db',
  'module' => 'ProductTemplates',
  'bean_name' => 'ProductTemplate',
  'vname' => 'LBL_PRODUCTTEMPLATES_PRODUCTTEMPLATES_1_FROM_PRODUCTTEMPLATES_L_TITLE',
  'id_name' => 'child_product_id',
  'link-type' => 'many',
  'side' => 'left',
);
$dictionary["ProductTemplate"]["fields"]["producttemplates_producttemplates_1_right"] = array (
  'name' => 'producttemplates_producttemplates_1_right',
  'type' => 'link',
  'relationship' => 'producttemplates_producttemplates_1',
  'source' => 'non-db',
  'module' => 'ProductTemplates',
  'bean_name' => 'ProductTemplate',
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

<?php

$dictionary["ProductTemplate"]["fields"]["producttemplates_producttemplates_1_left"] = array (
  'name' => 'producttemplates_producttemplates_1_left',
  'type' => 'link',
  'relationship' => 'producttemplates_producttemplates_1',
  'source' => 'non-db',
  'module' => 'ProductTemplates',
  'bean_name' => 'ProductTemplate',
  'vname' => 'LBL_PRODUCTTEMPLATES_PRODUCTTEMPLATES_1_FROM_PRODUCTTEMPLATES_L_TITLE',
  'id_name' => 'child_product_id',
  'link-type' => 'many',
  'side' => 'left',
);
$dictionary["ProductTemplate"]["fields"]["producttemplates_producttemplates_1_right"] = array (
  'name' => 'producttemplates_producttemplates_1_right',
  'type' => 'link',
  'relationship' => 'producttemplates_producttemplates_1',
  'source' => 'non-db',
  'module' => 'ProductTemplates',
  'bean_name' => 'ProductTemplate',
  'side' => 'right',
  'vname' => 'LBL_PRODUCTTEMPLATES_PRODUCTTEMPLATES_1_FROM_PRODUCTTEMPLATES_R_TITLE',
  'id_name' => 'parent_product_id',
  'link-type' => 'many',
);

This is the metadata for the relationship:

Fullscreen

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?php
// created: 2020-07-28 19:01:14
$dictionary["producttemplates_producttemplates_1"] = array (
  'true_relationship_type' => 'many-to-many',
  'from_studio' => true,
  'relationships' => 
  array (
    'producttemplates_producttemplates_1' => 
    array (
      'lhs_module' => 'ProductTemplates',
      'lhs_table' => 'product_templates',
      'lhs_key' => 'id',
      'rhs_module' => 'ProductTemplates',
      'rhs_table' => 'product_templates',
      'rhs_key' => 'id',
      'relationship_type' => 'many-to-many',
      'join_table' => 'product_templates_product_templates_1_c',
      'join_key_lhs' => 'parent_product_id',
      'join_key_rhs' => 'child_product_id',
    ),
  ),
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

<?php
// created: 2020-07-28 19:01:14
$dictionary["producttemplates_producttemplates_1"] = array (
  'true_relationship_type' => 'many-to-many',
  'from_studio' => true,
  'relationships' => 
  array (
    'producttemplates_producttemplates_1' => 
    array (
      'lhs_module' => 'ProductTemplates',
      'lhs_table' => 'product_templates',
      'lhs_key' => 'id',
      'rhs_module' => 'ProductTemplates',
      'rhs_table' => 'product_templates',
      'rhs_key' => 'id',
      'relationship_type' => 'many-to-many',
      'join_table' => 'product_templates_product_templates_1_c',
      'join_key_lhs' => 'parent_product_id',
      'join_key_rhs' => 'child_product_id',
    ),
  ),
  'table' => 'product_templates_product_templates_1_c',
  'fields' => 
  array (
    'id' => 
    array (
      'name' => 'id',
      'type' => 'id',
    ),
    'date_modified' => 
    array (
      'name' => 'date_modified',
      'type' => 'datetime',
    ),
    'deleted' => 
    array (
      'name' => 'deleted',
      'type' => 'bool',
      'default' => 0,
    ),
    'parent_product_id' => 
    array (
      'name' => 'parent_product_id',
      'type' => 'id',
    ),
    'child_product_id' => 
    array (
      'name' => 'child_product_id',
      'type' => 'id',
    ),
  ),
  'indices' => 
  array (
    0 => 
    array (
      'name' => 'idx_product_templates_product_templates_1_pk',
      'type' => 'primary',
      'fields' => 
      array (
        0 => 'id',
      ),
    ),
    1 => 
    array (
      'name' => 'idx_parent_product_id1_deleted',
      'type' => 'index',
      'fields' => 
      array (
        0 => 'parent_product_id',
        1 => 'deleted',
      ),
    ),
    2 => 
    array (
      'name' => 'idx_child_product_id2_deleted',
      'type' => 'index',
      'fields' => 
      array (
        0 => 'child_product_id',
        1 => 'deleted',
      ),
    ),
    3 => 
    array (
      'name' => 'product_templates_product_templates_1_alt',
      'type' => 'alternate_key',
      'fields' => 
      array (
        0 => 'child_product_id',
      ),
    ),
  ),
);

How can I make it so that, there should be no duplicates when I add/link a record to any of the subpanels?

EDIT:

After some digging in the Sugar Core files, I found out that when the you have a self referencing relationship (when LHS module = RHS module), it will do another addRow to the table. (M2MRelationship.php, line 142). This must be the one that's causing the additional row, but since this is a parent-child relationship, I would not need this.

Fullscreen

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<?php
class M2MRelationship extends SugarRelationship {
...
//Many to many has no additional logic, so just add a new row to the table and notify the beans.
$dataToInsert = $this->getRowToInsert($lhs, $rhs, $additionalFields);
/**
 * We need to do a complete check against all fields in the relationship to ensure that
 * an update occurs for any additional relationship fields
 * */
if (!$currentRow || !$this->compareRow($currentRow, $dataToInsert)) {
    $this->addRow($dataToInsert);
    if ($this->self_referencing) {
        $this->addSelfReferencing($lhs, $rhs, $additionalFields); <---- This adds the additional row
    }
    ...
}
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

<?php
class M2MRelationship extends SugarRelationship {
...
//Many to many has no additional logic, so just add a new row to the table and notify the beans.
$dataToInsert = $this->getRowToInsert($lhs, $rhs, $additionalFields);
/**
 * We need to do a complete check against all fields in the relationship to ensure that
 * an update occurs for any additional relationship fields
 * */
if (!$currentRow || !$this->compareRow($currentRow, $dataToInsert)) {
    $this->addRow($dataToInsert);

    if ($this->self_referencing) {
        $this->addSelfReferencing($lhs, $rhs, $additionalFields); <---- This adds the additional row
    }
    ...
}

Any help is appreciated. Thanks!

0 Jeroen Somhorst over 4 years ago

Shouldnt it be a 1:m? Because one parent can have multiple children?
0 Eron Tancioco over 4 years ago in reply to Jeroen Somhorst

Well, it needs to be M:M because a parent can have multiple children and a child can also have multiple parents.
0 Harald Kuske over 4 years ago

How does your metadata file (in custom/metadata/...) look like?

Harald Kuske
Principal Solution Architect – Professional Services, EMEA
hkuske@sugarcrm.com
SugarCRM Deutschland GmbH
0 Junaid Usman over 4 years ago

I would first look into the subpanel files and make sure that you are using different link names in each file properly. Can you share your metadata and subpanel files for a better understanding of what's happening or setup in those files?
0 Eron Tancioco over 4 years ago in reply to Harald Kuske

Hi, I've edited my question. Thanks!
0 Eron Tancioco over 4 years ago in reply to Junaid Usman

Hi, I've edited my question. Thanks!
0 Neeraja P over 4 years ago

Hi Eron Tancioco,

I would think the issue you described is not an issue, but a feature. If you're using a self-refering M2M relationship, it should actually exist in both places.

If you really need parent child relationship, you will have to use One to Many instead. That would perfectly work - One Parent Product Template can have multiple Child Product Templates - as long as it fits your needs.

If you really want many parents -> many childs, this would need code level changes, there is no easy work around. You will have to add a logic hook for "after_relationship_add" and remove the relationship of parent directly in the DB.