Store Count of n:n relation

Prerequisite: You must be familiar with the Syntax used in Tutorials and have already created an extension.

This example is another specific example of that tutorial Calculated field & Cascading update

Here we want to see in the details of an object A, the count of objects B which are linked to the object A through a many-to-many relationship.

• We want to be able to see this count in a list a A objects,
• We want to query easily A objects having more than 10 related B objects for eg.

We will use the Team to Person relationship and store on the Team the count of team members.

• We have a lnkPersonToTeam class with a person_id and a team_id external keys, which store the many-to-many relationships.
• We have a persons_list field on Team, providing the list of Persons linked to that Team
• We have a teams_list field on Person, providing the list of Teams linked to that Person

• We need to create a field persons_count on the Team class, to store the number of Persons in that team.

With the notion of event, you can subscribe to a particular event and execute a method as soon as this event is triggered.

• We will create a method on the Team class to compute the members in the team, so the count of links, and store it in a team field
• Manage fields: Create a new integer field and flag the persons_list LinkedSet attribute so the event EVENT_DB_LINKS_CHANGED will be triggered on that particular side of the relation, any time a linked object (here a lnkPersonToTeam) is created, modified or deleted.
• Subscribe to the event and specify the callback method to use.

class::Team

public function CountMembers()
{
    $oLnkSet = $this->Get('persons_list');
    if (is_object($oLnkSet)) {
        $this->Set('persons_count', $oLnkSet->Count());
        $this->DBUpdate();
    }
}

itop-design / classes

    <class id="Team" _delta="must_exist">
      <fields>
        <field id="persons_list" _delta="must_exist">
          <with_php_computation>true</with_php_computation>
        </field>
        <field id="persons_count" xsi:type="AttributeInteger" _delta="define">
          <sql>persons_count</sql>
          <default_value>0</default_value>
          <is_null_allowed>false</is_null_allowed>
        </field>
      </fields>
    </class>

itop-design / classes

    <class id="Team"  _delta="must_exist">
      <event_listeners>
        <event_listener id="CountMembers" _delta="define">
          <event>EVENT_DB_LINKS_CHANGED</event>
          <callback>CountMembers</callback>
          <rank>0</rank>
        </event_listener>
      </event_listeners>
    </class>

This is old way of doing things, as you will notice, it's a bit more complex.

Lets define when we need to compute what?

Add the counter field

itop-design / classes

    <class id="Team" _delta="must_exist">
      <fields>
        <field id="persons_count" xsi:type="AttributeInteger" _delta="define">
          <sql>persons_count</sql>
          <default_value>0</default_value>
          <is_null_allowed>false</is_null_allowed>
        </field>
      </fields>
    </class>

Then we create functions on the Team class. They will be called on multiple events, to avoid duplicating the code.

class::Team

public function ComputeValues()
{
        $this->ComputePersons();
}
 
public function ComputePersons($iDelta=0)
{
    if ($iDelta==0)
    {
        $oSet = $this->Get('persons_list');
        $i = $oSet->count();
    }
    else
    {
        $i = $this->Get('persons_count') + $iDelta;
    }
    $this->Set('persons_count', $i);
    return $i;
}

What to do when a link object is created, deleted or modified?

• Ask the Team to recompute its count

In this case I am looking at all sorts of possible change on this lnk object. In the Standard user interface, most of those cases are limited to an administrator or a REST/json API. But to be bulletproof, you need to suppose that everything can happen:

• If the team is changed
  • Remove one on the old team
  • Add one on the new team

• Remove one on the team

class::lnkPersonToTeam

public function UpdateRemote($iTeamId, $iDelta = 0)
{
    $oTeam = MetaModel::GetObject('Team', $aChanges['Team'], false, true);
    if (is_object($oTeam ))
    {
        $oTeam->ComputePersons($iDelta);
        $oTeam->DBUpdate();
    }
}
 
protected function AfterInsert()
{
        $this->UpdateRemote($this->Get('team_id'), 1);
}
 
protected function AfterUpdate()
{
    $aChanges = $this->ListPreviousValuesForUpdatedAttributes();
 
    if (array_key_exists('team_id', $aChanges))
    {
         $this->UpdateRemote($aChanges['team_id'], -1);
         $this->UpdateRemote($this->Get('team_id'), 1);
     }
}
 
protected function AfterDelete()
{
         $this->UpdateRemote($this->Get('team_id'), -1);
}

Then we have to imagine the various cases which can happen to a Component

• A Person is created ⇒ this is handle by the creation of associated links
• A Person is deleted ⇒ this is handle by the cascading deletion of associated links

So nothing to do !!!

UpdateCounter

/**
 * @param $id id of the remote object to update
 * @param $sClass class name of the remote object
 * @param $sCounterCode attribute code of the remote class storing the count of the relation
 * @param $iDelta numeric value added to the counter (can be negative)
 */
public function UpdateCounter($id, $sClass, $sCounterCode, $iDelta=0)
{
    if (in_array($sCounterCode,MetaModel::GetAttributesList($sClass)))
    {
        $oObject = MetaModel::GetObject($sClass, $id, false, true);
        if (is_object($oObject ) && ($iDelta!=0))
        {
            $i = $oObject->Get($sCounterCode) + $iDelta;
            $oObject->Set($sCounterCode, $i);
            $oObject->DBUpdate();      
        }
    }
}