Using Caché Objects
Defining and Using Relationships
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

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:
Note:
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:
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:
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.
Important:
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 Caché 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:
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.
Note:
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.
Important:
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 Caché 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.
Important:
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 Caché 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 one-to-many 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:
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:
  1. Obtain an OREF (ObjB) for an instance of the other class. (Either create a new object or open an existing object, as appropriate.)
  2. 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, Caché 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 %RelationshipObject. On this side, you can do the following to connect the objects:
  1. Obtain an OREF for an instance of the other object. (Either create a new object or open an existing object, as appropriate.)
  2. 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, Caché saves both objects (item and invoice).
Important:
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:
  1. Obtain an OREF (ObjA) for Class A.
  2. Obtain the ID for an instance of ClassB.
  3. 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:
  1. Open the instance of the child object (or the object on the many side).
  2. Set the applicable property of this object equal to null.
For example, there is a one-to-many relationship between Sample.Company and Sample.Employee 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:
For a parent-child relationship, the rules are different:
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 %RelationshipObject 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 %RelationshipObject 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 '= "")
Important:
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.RelationshipObject.
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.
Note:
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:
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:
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:
  1. Create a intermediate class that will define each relationship.
  2. Define a one-to-many relationship between that class and class A.
  3. 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 looks like this:
Class MNFK.DoctorPatient Extends %Persistent
{

Property Doctor As MNFK.Doctor;

ForeignKey DoctorFK(Doctor) References MNFK.Doctor();

Property Patient As MNFK.Patient;

ForeignKey PatientFK(Doctor) References MNFK.Patient();

}
Foreign keys are discussed in more detail in Using Foreign Keys in Using Caché SQL.
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.