Ordered Collection/Relationship

Caché supports binary relationships with cardinality 1:N (one to many, many to one).  A relationship presents two behaviors. One is to logically link the two objects togther, allowing one to be accessed from the other. When persisted, the relationship is only valid for as long as both objects exist in their respective extents. In this, a relationship is very much like a foreign key constraint in SQL. The other behavior is to maintain the relationship in memory when both of the related objects are present. Think of this as in-memory referential integrity.

The concept of an "ordered relationship" only is relevant when you consider that one object can be related to many others. From the perspective of the one object, the many objects could be ordered. But what defines that order? Is it the order in which the relationship with each of the many side objects was formed? Is it some implied order based on the state of the many side object? A key of some sort? What happens if the key is not defined in all of the many side objects? What if the ordered key value is changed while the object is in memory?

There are many questions that come up in the conversation about "ordered relationship". Often, the user assumes that the order in which many side objects were related to the one side object will define an order. That is not true nor can it ever be true. There is no defined order and any order implied cannot be assumed to hold over time.

The best approach is to not assume any order. If you require the related many side objects to be presented in a particular order then use a simple SQL select statement to retrieve those related objects and use an ORDER BY clause to explciitly define the order. This recommendation comes with the caveat that all of the related objects must have been persisted before executing the SQL statement. It also presumes that the many side of the relationship is a persistent class.

It is possible to prepare and execute the order relationship SQL statement in a manner that will honor the in-memory state of the related objects. The uncommitted state of the relationship will not be honored. Only relationships already formed and persisted are visible to SQL.

The SAMPLES database includes an example of a 1:N relationship - Sample.Company and Sample.Employee.

In the following example, I use dynamic SQL with %ObjectSelectMode = 1 (true). I open an instance of Sample.Employee and change its name to "State,Altered". Before I run the statement, I write the value of the Name property before changing it. Notice the output - it contains the altered name because of the basic rule of swizzling. If I were to also select the Name column in the SELECT statement, it would be the value as it current resides on disk, not as it resides in memory. I will leave that as an exercise for the reader.

LATEST:SAMPLES>kill

LATEST:SAMPLES>set employeeInMemory = ##class(Sample.Employee).%OpenId(193)

LATEST:SAMPLES>write employeeInMemory.Name

Gibbs,Greta A.

LATEST:SAMPLES>set employeeInMemory.Name = "State,Altered"

LATEST:SAMPLES>set sql = "select %ID As Employee from Sample.Employee where Company = ? order by SSN"                                     

LATEST:SAMPLES>set statement = ##class(%SQL.Statement).%New()

LATEST:SAMPLES>set statement.%ObjectSelectMode = 1

LATEST:SAMPLES>do statement.prepare(.sql)

LATEST:SAMPLES>set employees = statement.execute(10)

LATEST:SAMPLES>while employees.%Next() { set employee = employees.Employee write !,employee.%Id(),$c(9),employee.Name,?30,employee.SSN,! }

195 Beatty,Stuart T.          123-22-3131

200 Vanzetti,Kevin Q.         186-73-9108

170 Eagleman,Bart C.          247-83-1354

191 LaRocca,Tara W.           338-71-2352

164 Clay,Kristen S.           476-52-4746

193 State,Altered             523-94-2490

165 Campos,Edgar D.           558-21-6591

115 Davis,Angela G.           767-95-5164

155 Sato,Alfred L.            845-25-6807

Hi,

There is no capability to order the objects returned by the various iterator methods implemented by %Library.RelationshipObject. Your best alternative is to use SQL to select the related objects using a restriction similar to "WHERE Company = :companyID" and then to include explicit ORDER BY to define the desired ordering. Of course, this comes with some requirements - the set of related objects, Employee instance in this example, has to be saved to the Employee extent before you can use SQL to query them and also any changes made to those instances in memory will not be reflected by the query results. That is why I used the technique of querying using dynamic SQL and %ObjectSelectMode as it reduces the impact of in-memory changes.

It doesn't completely eliminate the "persistence" requirement for querying as  any newly related objects have to be saved before they can be queried. 

The only other possiblity is for you to iterate over the set of related objects and build your own sorted array.

Hope this helps!

Dan