· Aug 24, 2023

How to best cascade deletes for a property that is a list of a persistent class?

I have a persistent class (GetOrgUpdatesResponse) that has a property which is a list of another persistent class (Organization).

What would be the best way to cascade deletes so that on deletion of a parent row each object in the list property is then also deleted? 

I have included a sample of my classes here for clarity

Class Integration.ISIS.BO.SyncOrg.Custom.GetOrgUpdatesResponse Extends (%Persistent, Ens.Response)

Property Organizations As list Of Organization;
// Cascade Deletion
Property NextUpdateBatchID As %String;
Property ResultStatus As %String;

Where Organization is defined as the following class 

Class Integration.ISIS.BO.SyncOrg.Custom.Organization Extends (%Persistent, %XML.Adaptor)

Property ISISID As %String;
Property Name As %String(MAXLEN = 250);
Property Type As %String;
Property IsSupported As %Boolean [ InitialExpression = 0, Required ];
Property SalesPersonGUID As %String;
Property CountryISISID As %String;
Property CountryCode As %String;
Discussion (11)6
Log in or sign up to continue

The list type of Organizations existed prior to the changes I am making and is in use within the application in other places. As such I was ideally hoping implement the cascade deletion without changing that type. However, a Parent/Child relationship as both you and @Iryna Mykhailova suggested is a great option for the automatic cascade deletion and I will definitely consider the benefits of that change. Thank you!

Ahh ... if they need to be used elsewhere then that is a different story.  

I forget off the top of my head exactly how this would work, but you could potentially subclass your Org class and just add the parent cardinality relationship.  I *think* that should force the extent of the subclass to be embedded in the parent class ( @Dan Pasco?), which should automate the cascading delete for you without impacting the Orgs stored independently.  

I don't recommend using the "embedded extent" model. It might be possible to achieve this model but it isn't simple to do and it does produce some odd behavior. I could, reluctantly, describe how this is done. It may not apply to this case anyway. The default serialization of a LIST collection is as an embedded list and we do not support - currently - a serialized list as a child table.

This is an example of a MANY to MANY relationship. IRIS does not support this relationship type. Rather, we recommend using the Associative Entity model where there is a third class/table that maintains the relationship. The structure of the AE class/table is quite simple, consisting of two properties/columns, one referencing of the related classes and the other referencing the other related classes.

If restructuring the user class is not possible then the %OnDelete recommendation is good. Perhaps it would be better to use a FOREACH=ROW/OBJECT, ONAFTER=DELETE trigger to iterate over the collection and delete each referenced item. Triggers have the added advantage of working with both Object and SQL filing operations. Keep in mind that collections use OID format when crafting the delete.

Hello @Hannah Sullivan 
If you need to delete the list of object while deleting the parent object. You can override the %OnDelete method and add the delete implementation refer below code logic. Once you call the ##Class(package.class).%DeleteId(id). It will trigger the %OnDelete Method and delete the list of persistent object as well.

ClassMethod %OnDelete(oid As %ObjectIdentity) As %Status [ Private, ServerOnly = 1 ]
    set object = ..%Open(oid,,.status)
    If $$$ISERR(status) quit status
    If $IsObject(object.Organizations) {
        set org= object.OrganizationsGetSwizzled()
        while org.GetNext(.key){
            do object.Organizations.GetAt(key).%DeleteId(key)
    Quit $$$OK

You can create these object with Parent-child relationship as well.

Yep, that was my first thought - can you change Property Organizations As list Of Organization; to parent/children Relationship? In this case you will get automatic cascade delete. You'll have

Relationship Responce As GetOrgUpdatesResponse  [ Cardinality = parent, Inverse = Organizations ];
Relationship Organizations As Organization [ Cardinality = children, Inverse = Responce ];

And it suggests that each responce has its own organizations objects that don't repeat.

Otherwise, you will have to delete them manually 1 by 1 in callback method %OnDelete for example

/// This callback method is invoked by the <METHOD>%Delete</METHOD> method to 
/// provide notification that the object specified by <VAR>oid</VAR> is being deleted.
/// If this method returns an error then the object will not be deleted.
ClassMethod %OnDelete(oid As %ObjectIdentity) As %Status [ Private, ServerOnly = 1 ]
	Quit $$$OK

To your data storage scheme, I would like to clarify one point.
Let's say there is the following data:

ID Organizations
1 2,3
2 1,2
3 1,3

If you delete ID=1 (GetOrgUpdatesResponse), then ID=2 and ID=3 (Organization) will be automatically deleted. But in this case, the rows ID=2 and ID=3 (GetOrgUpdatesResponse) will contain incorrect data and the referential integrity will be violated. Or am I wrong?

Thank you for the clarification. For the given situation, there would never exist multiple lists that hold the same organization object. Following your example, my tables would be as such. 

ID Organizations
1 2
2 1,3
3 4,5

So that there would not exist a situation where a deletion of a list in GetOrgUpdatesResponse would result in any other list containing incorrect data. 

The solution depends on whether updates are via object, sql or both.
The  %OnDelete() callback method suggested only works for objects. For SQL you can add a foreign key definition to the organisation class, referencing the GetOrgUpdatesResponse with OnDelete = cascade constraint.

The solution that works for both sql and object deletes is an BEFORE DELETE trigger with Foreach = row/object parameter.

I almost never use the %On... methods as 

  1. they are only called via object interaction
  2. if I want to handle the SQL interaction I have to write the code a second time

as Wolf suggests a properly defined trigger with Foreach=row/object will be called during both Object and SQL interaction which means I can write one set of code to handle both and I find for this scenario it's best to think "SQL" instead of objects.