Fabio Goncalves · Jan 20, 2016

Ordered Collection/Relationship

Does Caché support a relationship property ordered according to a specifc value? 

What shoud be the best approach? 

5 0 6 471
Log in or sign up to continue


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>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


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!



Hi Daniel!

Thank you for you help. I got your explanation but my question may be very poor about what I was expecting. 

Considering the same classes that you mentioned above, from SAMPLES (Sample.Company and Sample. Employee) I ran the following code:

USER>zn "samples"
SAMPLES>set c = ##class(Sample.Company).%OpenId(1)
SAMPLES>w c.Name
PicoPlex Gmbh.
SAMPLES>w c.Employees
SAMPLES>w c.Employees.Count()
SAMPLES>w c.Employees.GetAt(1)
SAMPLES>w c.Employees.GetAt(1).Name
Thompson,Vincent L.
SAMPLES>w c.Employees.GetAt(2).Name
Garcia,Amanda C.
SAMPLES>w c.Employees.GetAt(3).Name
Noodleman,Barbara T.
SAMPLES>w c.Employees.GetAt(4).Name
Smith,Dick A.

From the result above you can notice that the employee names, for instance, is not ordered. My question was about choose the order by name, salary or any other property value from the "child class". Is it possible? What would be an alternative for that?


PS: I also have tried to use SetObjectAt method and pass a key parameter value with the value that I want to be ordered. However when I try to retrieve data by using thoses key values, it does not work according to the key order that I tried to use on SetObjectAt key parameter value.


Hi Dan,

The major advantage to have the possibility to define orderby in a relationship is the no need to use SQL when working in OO.

In my case, if a have this possibility, in the process I don´t need to use SQL in nothing.


Lucas Boeing Scarduelli (@lucasscarduelli)


This view bypasses what makes Cache such a compelling technology in my opinion. We are not "just" an OO database or a relational database. We support both models fluidly and give you the important features and functions of each in a complimentary fashion.


We are truly multi-model not Object with SQL or SQL mapped to Objects. We let you define structure in Objects where real world modeling is important but then seemlessly query data using SQL - which is what the language was designed for, not for defining data structures.


SQL is not evil. It has simply been abused for cases it was not intended in many DBMS implementations.


my .02 cents


I understand and agree with their point of view, in my application I use both SQL and OO, but what I means is that in this case I would not mix the two and I'm already using OO.

In this case an ordered list would be much more practical.



Lucas Boeing Scarduelli (@lucasscarduelli)