Defining and Using Relationships
This chapter describes relationships, which are a special kind of property that you can define only in persistent classes. It discusses the following topics:
When viewing this book online, use the preface of this book to quickly find related topics.
Overview of Relationships
A relationship is an association between two persistent objects, each of a specific type. To create a relationship between two objects, each must have a relationship property, which defines its half of the relationship. Caché directly supports two kinds of relationships: one-to-many and parent-child.
Caché relationships have the following characteristics:
-
Relationships are binary — a relationship is defined either between two, and only two, classes or between a class and itself.
-
Relationships can only be defined for persistent classes.
-
Relationships are bidirectional — both sides of a relationship must be defined.
-
Relationships automatically provide referential integrity. They are visible to SQL as foreign keys. See “SQL Projection of Relationships” for more information on this topic.
-
Relationships automatically manage their in-memory and on-disk behavior.
-
Relationships provide superior scaling and concurrency over object collections.
On the other hand, in an object collection, there is an inherent order of the objects; the same is not true for relationships. If you insert objects A, B, and C, in that order, into a list of objects, that order is retained. If you insert objects A, B, and C, in that order, into a relationship property, that order is not retained.
It is also possible to define foreign keys between persistent classes, rather than adding relationships. With a foreign key, you have a greater degree of control over what happens when an object in one class is added, updated, or deleted. See “Using Triggers” in Using Caché SQL.
One-to-Many Relationships
In a one-to-many relationship between class A and class B, one instance of class A is associated with zero or more instances of class B.
For example, a company class may define a one-to-many relationship with an employee class. In this case, there may be zero or more employee objects associated with each company object.
These classes are independent of each other as follows:
-
When an instance of either class is created, it may or may not be associated with an instance of the other class.
-
If an instance of class B is associated with a given instance of class A, this association can be removed or changed. The instance of class B can be associated with a different instance of class A. The instance of class B does not have to have any association with an instance of class A (and vice versa).
There can be a one-to-many relationship within a single class. One instance of that class can be associated with zero or more other instances of that class. For example, the Employee class might define a relationship between an employee and any employees who directly report that employee.
Parent-Child Relationships
In a parent-child relationship between class A and class B, one instance of class A is associated with zero or more instances of class B. Also, the child table is dependent on the parent table, as follows:
-
When an instance of the class B is saved, it must be associated with an instance of class A. If you attempt to save the instance, and that association is not defined, the save action fails.
-
The association cannot be changed. That is, you cannot associate the instance of class B with a different instance of class A.
-
If the instance of class A is deleted, all associated instances of class B are deleted as well.
-
You can delete an instance of class B. Class A is not required to have associated instances of class B.
For an example, an invoice class may define a parent-child relationship with a line item class. In this case, an invoice consists of zero or more line items. Those line items cannot be moved to a different invoice. Nor do they have meaning on their own.
Also, in the child table (class B), the IDs are not purely numeric. As a consequence, it is not possible to add a bitmap index to the relationship property in this class, although other forms of index are permitted (and are useful, as shown later in this chapter).
Parent-Child Relationships and Storage
If you define a parent-child relationship before compiling the classes, the data for both classes is stored in the same global. The data for the children is subordinate to that of the parent, in a structure similar to the following:
^Inv(1)
^Inv(1, "invoice", 1)
^Inv(1, "invoice", 2)
^Inv(1, "invoice", 3)
...
As a result, Caché can read and write these related objects more quickly.
Common Relationship Terminology
This section explains, by example, phrases that are in common use for convenience when discussing relationships.
Consider a one-to-many relationship between a company and its employees; that is, one company has multiple employees. In this scenario, the company is called the one side and the employee is called the many side.
Similarly, consider a parent-child relationship between a company and its products; that is, the company is the parent, and the products are the children. In this scenario, the company is called the parent side and the employee is called the children side or the child side.
Defining a Relationship
To create a relationship between the records of two classes, you create a pair of complementary relationship properties, one in each class. To create a relationship between records of the same class, you create a pair of complementary relationship properties in that class.
Studio provides a convenient wizard (the New Property Wizard), which simplifies this task. See the “Relationships” section of Using Studio for details.
The following subsections describe the general syntax and then discuss how to define one-to-many relationships and parent-child relationships.
General Syntax
The syntax for a relationship property is as follows:
Relationship Name As classname [ Cardinality = cardinality_type, Inverse = inverseProp ];
Where:
-
classname is the class to which this relationship refers. This must be a persistent class.
-
cardinality_type (required) defines how the relationship “appears” from this side as well as whether it is an independent relationship (one-to-many) or a dependent relationship (parent-child). cardinality_type can be one, many, parent, or children.
-
inverseProp (required) is the name of the complementary relationship property, which is defined in the other class.
In the complementary relationship property, the cardinality_type keyword must be the complement of the cardinality_type keyword here. The values one and many are complements of each other. Similarly, the values parent and children are complements of each other.
Because a relationship is a kind of property, other property keywords are available for use in them, including Final, Required, SqlFieldName, and Private. Some property keywords, such as MultiDimensional, do not apply. See the Class Definition Reference for more information.
Defining a One-to-Many Relationship
This section describes how define a one-to-many relationship between classA and classB, where one instance of classA is associated with zero or more instances of classB.
It is possible to have a one-to-many relationship between records of a single class. That is, in the following discussion, classA and classB can be the same class.
Class A must have a relationship property of the following form:
Relationship manyProp As classB [ Cardinality = many, Inverse = oneProp ];
Where oneProp is the name of the complementary relationship property, which is defined in classB.
Class B must have a relationship property of the following form:
Relationship oneProp As classA [ Cardinality = one, Inverse = manyProp ];
Where manyProp is the name of the complementary relationship property, which is defined in classA.
On the one side (class A), the relationship uses a query to populate the relationship object. You can improve the performance of this query in almost all cases by adding an index on the complementary relationship property (that is, adding an index on the many side, class B).
The New Property wizard in Studio prompts you to create such an index. See the “Relationships” section of Using Studio for details.
Defining a Parent-Child Relationship
This section describes how define a parent-child relationship between classA and classB, where one instance of classA is the parent of zero or more instances of classB. These cannot be the same class.
Class A must have a relationship property of the following form:
Relationship childProp As classB [ Cardinality = children, Inverse = parentProp ];
Where parentProp is the name of the complementary relationship property, which is defined in classB.
Class B must have a relationship property of the following form:
Relationship parentProp As classA [ Cardinality = parent, Inverse = childProp ];
Where childProp is the name of the complementary relationship property, which is defined in classA.
On the parent side (class A), the relationship uses a query to populate the relationship object. You can improve the performance of this query in almost all cases by adding an index on the complementary relationship property (that is, adding an index on the child side, class B).
The New Property wizard in Studio prompts you to create such an index. See the “Relationships” section of Using Studio for details.
Parent-Child Relationships and Compilation
For a parent-child relationship, Caché can generate a storage definition that stores the data for the parent and child objects within a single global, as shown earlier. Such a storage definition improves the speed with which you can access these related objects.
If you add a relationship after compiling the classes, Caché does not generate this optimized storage definition. In such a case, you can delete any test data you might have, delete the storage definitions of the two classes, and then recompile.
Examples
This section presents examples of a one-to-many relationship and a parent-child relationship.
Example One-to-Many Relationship
This example represents a one-to-many relationship between a company and its employees. The company class is as follows:
Class MyApp.Company Extends %Persistent
{
Property Name As %String;
Property Location As %String;
Relationship Employees As MyApp.Employee [ Cardinality = many, Inverse = Employer ];
}
And the employee class is as follows:
Class MyApp.Employee Extends (%Persistent, %Populate)
{
Property FirstName As %String;
Property LastName As %String;
Relationship Employer As MyApp.Company [ Cardinality = one, Inverse = Employees ];
Index EmployerIndex On Employer;
}
Example Parent-Child Relationship
This example represents a parent-child relationship between an invoice and its line items. The invoice class is as follows:
Class MyApp.Invoice Extends %Persistent
{
Property Buyer As %String;
Property InvoiceDate As %TimeStamp;
Relationship LineItems As MyApp.LineItem [ Cardinality = children, Inverse = Invoice ];
}
And the line item class is as follows:
Class MyApp.LineItem Extends %Persistent
{
Property ProductSKU As %String;
Property UnitPrice As %Numeric;
Relationship Invoice As MyApp.Invoice [ Cardinality = parent, Inverse = LineItems ];
Index InvoiceIndex On Invoice;
}
Connecting Objects
A relationship is bidirectional. Specifically, if you update the value of the relationship property in one object, that immediately affects the value of the corresponding relationship property in the related object. As a consequence, you can specify the value for a relationship property in one object, and the effect appears in both objects.
Because the nature of the relationship property is different in the two classes, there are two general scenarios for updating any relationship:
-
Scenario 1: The relationship property is a simple reference property. Set the property equal to the appropriate object.
-
Scenario 2: The relationship property is an instance of %RelationshipObjectOpens in a new tab, which has an array-like interface. Use methods of that interface to insert objects into the relationship. Note that the objects in the relationship are not ordered; the relationship does not retain the order in which you inserted objects into it.
The following subsections give the details. The third subsection describes a variation of Scenario 1 that is especially suitable when you have a large number of objects in the relationship.
The information here describes how to add objects to relationships. The process of modifying objects is similar, with an important exception (by design) in the case of parent-child relationships: Once associated with a particular parent object (and then saved), a child object can never be associated with a different parent.
Scenario 1: Updating the Many or Child Side
On the many side or the child side (ObjA), the relationship property is a simple reference property that points to ObjB. To connect the objects from this side:
-
Obtain an OREF (ObjB) for an instance of the other class. (Either create a new object or open an existing object, as appropriate.)
-
Set the relationship property of ObjA equal to ObjB.
For an example, consider the example parent-child classes shown earlier. The following steps would update the relationship from the MyApp.LineItem side:
//obtain an OREF to the invoice class
set invoice=##class(MyApp.Invoice).%New()
//...specify invoice date and so on
set item=##class(MyApp.LineItem).%New()
//...set some properties of this object such as the product name and sale price...
//connect the objects
set item.Invoice=invoice
When you call the %Save() method for the item object, the system saves both objects (item and invoice).
Also see the last subsection for a variation of this technique.
Scenario 2: Updating the One or Parent Side
On the one side or the parent side, the relationship property is an instance of %RelationshipObjectOpens in a new tab. On this side, you can do the following to connect the objects:
-
Obtain an OREF for an instance of the other object. (Either create a new object or open an existing object, as appropriate.)
-
Call the Insert() method of the relationship property on this side and pass that OREF as the argument.
Consider the example parent-child classes shown earlier. For those classes, the following steps would update the relationship from the MyApp.Invoice side:
set invoice=##class(MyApp.Invoice).%OpenId(100034)
//set some properties such as the customer name and invoice date
set item=##class(MyApp.LineItem).%New()
//...set some properties of this object such as the product name and sale price...
//connect the objects
do invoice.LineItems.Insert(item)
When you call the %Save() method for the invoice object, the system saves both objects (item and invoice).
Caché does not maintain information about the order in which objects are added into the relationship. That is, if you open a previously saved object and use GetNext() or similar methods to iterate through a relationship, the order of objects in that relationship is different from when the objects were created.
Fastest Way to Connect Objects
When you need to add a comparatively large number of objects to a relationship, use a variation of the technique given in Scenario 1. In this variation:
-
Obtain an OREF (ObjA) for Class A.
-
Obtain the ID for an instance of ClassB.
-
Use the property setter method of the relationship property of ObjA, passing the ID as the argument.
If the relationship property is named MyRel, the property setter method is named MyRelSetObjectId().
(For details on property setter methods, see the chapter “Using and Overriding Property Methods.”
Consider the example classes described in Scenario 1. For those classes, the following steps would insert a large number of invoice items into an invoice (and would do so more rapidly than the technique given in that section):
set invoice=##class(MyApp.Invoice).%New()
//set some properties such as the customer name and invoice date
do invoice.%Save()
set id=invoice.%Id()
kill invoice //OREF is no longer needed
for index = 1:1:(1000)
{
set Item=##class(MyApp.LineItem).%New()
//set properties of the invoice item
//connect to the invoice
do Item.InvoiceSetObjectId(id)
do Item.%Save()
}
Removing a Relationship
In the case of a one-to-many relationship, it is possible to remove a relationship between two objects. One way to do so is as follows:
-
Open the instance of the child object (or the object on the many side).
-
Set the applicable property of this object equal to null.
For example, there is a one-to-many relationship between Sample.CompanyOpens in a new tab and Sample.EmployeeOpens in a new tab in the SAMPLES namespace. The following demonstrates that the employee whose ID is 101 works for the company whose ID is 5. Notice that this company has four employees:
SAMPLES>set e=##class(Sample.Employee).%OpenId(101)
SAMPLES>w e.Company.%Id()
5
SAMPLES>set c=##class(Sample.Company).%OpenId(5)
SAMPLES>w c.Employees.Count()
4
Next for this employee, we set the Company property equal to null. Notice that this company now has three employees:
SAMPLES>set e.Company=""
SAMPLES>w c.Employees.Count()
3
It is also possible to remove the relationship by modifying the other object. In this case, we use the RemoveAt() method of the collection property. For example, the following demonstrates that for the company whose ID is 17, the first employee is employee ID 102:
SAMPLES>set e=##class(Sample.Employee).%OpenId(102)
SAMPLES>w e.Company.%Id()
17
SAMPLES>set c=##class(Sample.Company).%OpenId(17)
SAMPLES>w c.Employees.Count()
4
SAMPLES>w c.Employees.GetAt(1).%Id()
102
To remove the relationship between this company and this employee, we use the RemoveAt() method, passing the value 1 as the argument, to remove the first collection item. Notice that after we do so, this company has three employees:
SAMPLES>do c.Employees.RemoveAt(1)
SAMPLES>w c.Employees.Count()
3
In the case of a parent-child relationship, it is not possible to remove a relationship between two objects. You can, however, delete a child object.
Deleting Objects in Relationships
For a one-to-many relationship, the following rules govern what occurs when you attempt to delete objects:
-
The relationship prevents you from deleting an object on the one side, if there are any objects on the many side that reference this object. For example, if you try to delete a company, and the employee table has records that point to that company, the delete operation fails.
Thus it is necessary to first delete the records on the many side.
-
The relationship does not prevent you from deleting an object on the many side (the employee table).
For a parent-child relationship, the rules are different:
-
The relationship causes a deletion on the parent side to affect the child side. Specifically, if you delete an object on the parent side, the associated objects on the child side are automatically deleted.
For example, if there is a parent-child relationship between invoices and line items, if you delete an invoice, its line items are deleted.
-
The relationship does not prevent you from deleting an object on the child side (the line item table).
For both one-to-many and parent-child relationships, you can change the default behavior of deleting an object on the one side or the parent side by using the OnDelete property keyword.
Working with Relationships
Relationships are properties. Relationships with a cardinality of one or parent behave like atomic (non-collection) reference properties. Relationships with a cardinality of many or children are instances of the %RelationshipObjectOpens in a new tab class, which has an array-like interface.
For example, you could use the Company and Employee objects defined above in the following way:
// create a new instance of Company
Set company = ##class(MyApp.Company).%New()
Set company.Name = "Chiaroscuro LLC"
// create a new instance of Employee
Set emp = ##class(MyApp.Employee).%New()
Set emp.LastName = "Weiss"
Set emp.FirstName = "Melanie"
// Now associate Employee with Company
Set emp.Employer = company
// Save the Company (this will save emp as well)
Do company.%Save()
// Close the newly created objects
Set company = ""
Set emp = ""
Relationships are fully bidirectional in memory; any operation on either side is immediately visible on the other side. Hence, the code above is equivalent to the following, which instead operates on the company:
Do company.Employees.Insert(emp)
Write emp.Employer.Name
// this will print out "Chiaroscuro LLC"
You can load relationships from disk and use them as you would any other property. When you refer to a related object from the one side, the related object is automatically swizzled into memory in the same way as a reference (object-valued) property. When you refer to a related object from the many side, the related objects are not swizzled immediately; instead a transient %RelationshipObjectOpens in a new tab collection object is created. As soon as any methods are called on this collection, it builds a list containing the ID values of the objects within the relationship. It is only when you refer to one of the objects within this collection that the actual related object is swizzled into memory.
Here is an example that displays all Employee objects related to a specific Company:
// open an instance of Company
Set company = ##class(Company).%OpenId(id)
// iterate over the employees; print their names
Set key = ""
Do {
Set employee = company.Employees.GetNext(.key)
If (employee '= "") {
Write employee.Name,!
}
} While (key '= "")
In this example, closing company removes the Company object and all of its related Employee objects from memory. Note, however, that every Employee object contained in the relationship will be swizzled into memory by the time the loop completes. To reduce the amount of memory that this operation uses—perhaps there are thousands of Employee objects—then modify the loop to “unswizzle” the Employee object after displaying the name, by calling the %UnSwizzleAt() method:
Do {
Set employee = company.Employees.GetNext(.key)
If (employee '= "") {
Write employee.Name,!
// remove employee from memory
Do company.Employees.%UnSwizzleAt(key)
}
} While (key '= "")
Relationships do not support the list interface. That means you cannot get the count of related objects and iterate over the relationship by incrementing a pointer from one (1) by one (1) up to the number of related objects; instead, you must use array-collection style iteration. For more information on iterating through objects in a relationship, see the reference page for %Library.RelationshipObjectOpens in a new tab.
SQL Projection of Relationships
As described earlier in this book, a persistent class is projected as an SQL table. This section describes how relationships of such a class are projected to SQL.
Although you can modify the projection of the other properties of the classes involved, it is not possible to modify the SQL projection of relationships per se. For example, it is not supported to specify the CLASSNAME property parameter for the relationship. This parameter is mentioned in “Defining Object-Valued Properties” earlier in this book.
SQL Projection of One-to-Many Relationships
This section describes the SQL projection of a one-to-many relationship. As an example, consider the example one-to-many classes shown earlier. In this case, the classes are projected as follows:
-
On the one side (that is, in the company class), there is no field that represents the relationship. The company table has fields for other properties, but there is no field that holds the employees.
-
On the many side (that is, in the employee class), the relationship is a simple reference property, and that is projected to SQL in the same way as other reference properties. The employee table has a field named Employer, which points to the company table.
To query these tables together, you can query the employee table and use arrow syntax, as in the following example:
SELECT Employer->Name, LastName,FirstName FROM MyApp.Employee
Or you can perform an explicit join, as in the following example:
SELECT c.Name, e.LastName, e.FirstName FROM MyApp.Company c, MyApp.Employee e WHERE e.Employer = c.ID
Also, this pair of relationship properties implicitly adds a foreign key to the employee table; the foreign key has UPDATE and DELETE both specified as NOACTION.
SQL Projection of Parent-Child Relationships
Similarly, consider the example parent-child classes shown earlier, which have a parent-child relationship between an invoice and its line items. In this case, the classes are projected as follows:
-
On the parent side (that is, in the invoice class), there is no field that represents the relationship. The invoice table has fields for other properties, but there is no field that holds the line items.
-
On the child side (that is, in the line item class), the relationship is a simple reference property, and that is projected to SQL in the same way as other reference properties. The line item table has a field named Invoice, which points to the invoice table.
-
Also on the child side, the IDs always include the ID of the parent record, even if you explicitly attempt to create an IDKey based exclusively on the child. Also, if the definition of the IDKey in the child class explicitly includes the parent relationship, the compiler recognizes this and does not add it again; this allows you to alter the sequence in which the parent reference appears as a subscript in the generated global references.
As a consequence, it is not possible to add a bitmap index to this property, although other forms of index are permitted.
To query these tables together, you can query the invoice table and use arrow syntax, as in the following example:
SELECT
Invoice->Buyer, Invoice->InvoiceDate, ID, ProductSKU, UnitPrice
FROM MyApp.LineItem
Or you can perform an explicit join, as in the following example:
SELECT
i.Buyer, i.InvoiceDate, l.ProductSKU,l.UnitPrice
FROM MyApp.Invoice i, MyApp.LineItem l
WHERE i.ID = l.Invoice
Also, for the class on the child side, the projected table is “adopted” as a child table of the other table.
Creating Many-to-Many Relationships
Caché does not directly support many-to-many relationships, but this section describes how to model such a relationship indirectly.
To establish a many-to-many relationship between class A and class B, do the following:
-
Create a intermediate class that will define each relationship.
-
Define a one-to-many relationship between that class and class A.
-
Define a one-to-many relationship between that class and class B.
Then, create a record in the intermediate class for each relationship between an instance of class A and an instance of class B.
For example, suppose that class A defines doctors; this class defines the properties Name and Specialty. Class B defines patients; this class defines the properties Name and Address. To model the many-to-many relationship between doctors and patients, we could define an intermediate class as follows:
/// Bridge class between MN.Doctor and MN.Patient
Class MN.DoctorPatient Extends %Persistent
{
Relationship Doctor As MN.Doctor [ Cardinality = one, Inverse = Bridge ];
Index DoctorIndex On Doctor;
Relationship Patient As MN.Patient [ Cardinality = one, Inverse = Bridge ];
Index PatientIndex On Patient;
}
Then the doctor class looks like this:
Class MN.Doctor Extends %Persistent
{
Property Name;
Property Specialty;
Relationship Bridge As MN.DoctorPatient [ Cardinality = many, Inverse = Doctor ];
}
And the patient class looks like this:
Class MN.Patient Extends %Persistent
{
Property Name;
Property Address;
Relationship Bridge As MN.DoctorPatient [ Cardinality = many, Inverse = Patient ];
}
The easiest way to query both doctors and patients is to query the intermediate table. The following shows an example:
SELECT top 20 Doctor->Name as Doctor, Doctor->Specialty, Patient->Name as Patient
FROM MN.DoctorPatient order by doctor
Doctor Specialty Patient
Davis,Joshua M. Dermatologist Wilson,Josephine J.
Davis,Joshua M. Dermatologist LaRocca,William O.
Davis,Joshua M. Dermatologist Dunlap,Joe K.
Davis,Joshua M. Dermatologist Rotterman,Edward T.
Davis,Joshua M. Dermatologist Gibbs,Keith W.
Davis,Joshua M. Dermatologist Black,Charlotte P.
Davis,Joshua M. Dermatologist Dunlap,Joe K.
Davis,Joshua M. Dermatologist Rotterman,Edward T.
Li,Umberto R. Internist Smith,Wolfgang J.
Li,Umberto R. Internist Ulman,Mo O.
Li,Umberto R. Internist Gibbs,Keith W.
Li,Umberto R. Internist Dunlap,Joe K.
Quixote,William Q. Surgeon Black,Charlotte P.
Quixote,William Q. Surgeon LaRocca,William O.
Quixote,William Q. Surgeon Black,Charlotte P.
Quixote,William Q. Surgeon Smith,Wolfgang J.
Quixote,William Q. Surgeon LaRocca,William O.
Quixote,William Q. Surgeon LaRocca,William O.
Quixote,William Q. Surgeon Black,Charlotte P.
Salm,Jocelyn Q. Allergist Tsatsulin,Mark S.
As a variation, you can use a parent-child relationship in place of one of the one-to-many relationships. This provides the physical clustering of the data as described earlier in this chapter, but it means that you cannot use a bitmap index on that relationship.
Variation with Foreign Keys
Rather than defining relationships between the intermediate class and classes A and B, you can use reference properties and foreign keys, so that the intermediate class MN.DoctorPatient looks like this instead of the version shown previously:
Class MN.DoctorPatient Extends %Persistent
{
Property Doctor As MN.Doctor;
ForeignKey DoctorFK(Doctor) References MN.Doctor();
Property Patient As MN.Patient;
ForeignKey PatientFK(Patient) References MN.Patient();
}
Foreign keys are discussed in more detail in “Using Foreign Keys” in Using Caché SQL. Also see “Foreign Key Definitions” in the reference “Class Definitions” in the Class Definition Reference.
One advantage to using a simple foreign key model is that no inadvertent swizzling of large numbers of objects will occur. One disadvantage is that no automatic swizzling is available.