Store Count of 1: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 an one-to-many relationship (an external key field on class B pointing to A).

We will see two flavors of that particular case:

1. On Ticket count the associated WorkOrders
2. On User Request count of sub-UserRequests (special case where classes A and B are the same class)

That information is easily visible on the details of a single Ticket/User Request, but you have no mean to retrieve quickly all the Ticket/User Request with more that 2 WorkOrders/sub-requests or having no WorkOrders/sub-requests at all. This tutorial, will explain how to resolve that issue

In order to be able to do this:

• We need to create a field count_workorders_list on the Ticket class, to store the number permanently so it can be queried.
• We have a workorders_list field on Ticket, providing the list of sub-request under the current one
• We have a ticket_id field on WorkOrder, providing the Ticket which work-order count must be updated

Lets define when we need to compute what?

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

class::Ticket

public function ComputeValues()
{
        $this->ComputeCounter();
}
// This method can recompute from scratch or simply add or remove a delta
public function ComputeCounter($iDelta=0)
{
    if ($iDelta==0) {
        $oSet = $this->Get('workorders_list');
        $i = $oSet->count();
    } else {
        $i = $this->Get('count_workorders_list') + $iDelta;
    }
    $this->Set('count_workorders_list', $i);
    return $i;
}

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

class::WorkOrder

// Generic method which will be reused on create, update and delete of a WorkOrder
public function UpdateRemote($id, $iDelta = 0)
{
    $oObject = MetaModel::GetObject('Ticket', $id, false, true);
    if (is_object($oObject)) {
        $oObject->ComputeCounter($iDelta);
        $oObject->DBUpdate();
    }
}

• Ask the Ticket to recompute its count and update it

class::WorkOrder

protected function AfterInsert()
{
    $i = $this->Get('ticket_id');
    if ($i > 0) $this->UpdateRemote($i, 1);
}

• If the ticket_id is changed
  • Remove one on the old parent Ticket
  • Add one on the new parent Ticket

class::WorkOrder

protected function AfterUpdate()
{
    $aChanges = $this->ListPreviousValuesForUpdatedAttributes();
    if (array_key_exists('ticket_id', $aChanges)) {
         $this->UpdateRemote($aChanges['ticket_id'], -1);
         $this->UpdateRemote($this->Get('ticket_id'), 1);
     }
}

• Remove one on the parent Ticket

class::WorkOrder

protected function AfterDelete()
{
         $this->UpdateRemote($this->Get('ticket_id'), -1);
}

This use case is very similar to the above one, build as classes A and B are the same, all functions will be created on the UserRequest class.

In order to be able to do this:

• We need to create a field count_related_request_list on the UserRequest class, to store the number of sub requests permanently so it can be queried.
• We have a related_request_list field on UserRequest, providing the list of sub-request under the current one
• We have a parent_request_id field on UserRequest, providing the parent

Lets define when we need to compute what?

class::UserRequest

public function ComputeValues()
{
        $this->CountSubRequest();
}
 
public function CountSubRequest($iDelta=0)
{
    if ($iDelta==0)
    {
        $oSet = $this->Get('related_request_list');
        $i = $oSet->count();
    }
    else
    {
        $i = $this->Get('count_related_request_list') + $iDelta;
    }
    $this->Set('count_related_request_list', $i);
    return $i;
}
 
public function UpdateRemote($id, $iDelta = 0)
{
    $oObject = MetaModel::GetObject('UserRequest', $id, false, true);
    if (is_object($oObject))
    {
        $oObject->CountSubRequest($iDelta);
        $oObject->DBUpdate();
    }
}

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

class::UserRequest

// Ask the Parent to recompute its count and update it
protected function AfterInsert()
{
    $i = $this->Get('parent_request_id');
    if ($i > 0) $this->UpdateRemote($i, 1);
}
 
protected function AfterUpdate()
{
    $aChanges = $this->ListPreviousValuesForUpdatedAttributes();
 
    // If the parent_id is changed
    if (array_key_exists('parent_request_id', $aChanges))
    {
         // Remove one on the old parent UserRequest
         $this->UpdateRemote($aChanges['parent_request_id'], -1);
         // Add one on the new parent UserRequest
         $this->UpdateRemote($this->Get('parent_request_id'), 1);
     }
}
 
protected function AfterDelete()
{
        // Remove one on the parent UserRequest 
        $this->UpdateRemote($this->Get('parent_request_id'), -1);
}