Store Count of n:n relation
Prerequisite: You must be familiar with the Syntax used in Tutorials and have already created an extension.
- learning:
- Update an object based on related objects
- level:
- Advanced
- domains:
- PHP, Automation
- min version:
- 2.3.0
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. In this example, we will store on a Team the count of Persons on that Team.
That information is easily visible on the details of a single Team, but you have no mean to retrieve quickly all the teams with more that 12 persons or less than 1.
In order to be able to do this:
-
We need to create a field
nb_persons
on the Team class, to store the number of Persons in that team. -
We have a lnkPersonToTeam class with a
person_id
and ateam_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
Lets define when we need to compute what?
On Team
First 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->ComputeMembers(); } public function ComputePersons($iDelta=0) { if ($iDelta==0) { $oSet = $this->Get('persons_list'); $i = $oSet->count(); } else { $i = $this->Get('nb_persons') + $iDelta; } $this->Set('nb_persons', $i); return $i; }
On lnk objects
What to do when a link
object is created, deleted
or modified?
Creation
-
Ask the Team to recompute its count
Modification
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
-
Deletion
-
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(); } } public function AfterInsert() { $this->UpdateRemote($this->Get('team_id'), 1); } public 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); } } public function AfterDelete() { $this->UpdateRemote($this->Get('team_id'), -1); }
On Person
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 !!!
Generic UpdateCounter function
- 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(); } } }