SQL joining tables on collection list

SQL, Caché

Class ICT.Experiments.A Extends %Persistent
Property Name As %String;
Property Collection As list Of ICT.Experiments.B;
Property Collection2 As array Of ICT.Experiments.C;

Class ICT.Experiments.B Extends %Persistent
Property Name As %String;

Class ICT.Experiments.C Extends %Persistent
Property Name As %String;

I have the classes above and I can select columns from the array collection by using two joins as follows:

mainTable.ID, mainTable.Name, c.Name
FROM ICT_Experiments.A mainTable
LEFT JOIN ICT_Experiments.A_Collection2 arrayTable ON arrayTable.A = mainTable.%ID
LEFT JOIN ICT_Experiments.C c ON c.%ID = arrayTable.Collection2

This is quite straightforward since array collections map onto a table of their own. But I am having a hard time selecting similar information for the list collection. In the documentaion all I could find was an example, which used a list of string instead of list of objects.

I want to iterate through the resultset of the query in code. But when I use "ResultSet.%Get("Collection")", all I get is a list of the primary keys of the objects. Any idea on how to go about this in a single query?


  • 0
  • 1
  • 146
  • 7
  • 2


You can store list properties same as array properties:

Property Collection As list Of ICT.Experiments.B(SQLPROJECTION = "table/column", STORAGEDEFAULT = "list");

Note that you need first to delete class storage if any exists and either recreate or move the data to comply with the new storage.

Is changing the projection the only way to include them in a single query?

You can also write a stored procedure which accepts id and returns the field you need.

Here's an example:

ClassMethod ListProp(id, prop, listprop) As %String [ SqlProc ]
    set ids = $classmethod(, prop _ "GetStored", id)
    quit:'$lv(ids) ""
    set ptr = 0
    set result = ""
    if listprop="id" {
        while $listnext(ids, ptr, value) {
            set result = result _ value
    } else {
        set propClass = $$$defMemberKeyGet($classname(),$$$cCLASSproperty,prop,$$$cPROPtype)
        while $listnext(ids, ptr, value) {
            set result = result _ $lb($classmethod(propClass, listprop _ "GetStored", $lg(value)))
    quit $lts(result)


  • id - root object id
  • prop - name of "List Of" property (in your case - Collection)
  • listprop - name of element property (in your case  name of any property from ICT.Experiments.B class)

Depends a bit on what you want. If you want to use them in the WHERE clause, you can leave the "list of" structure as-is and use our %FOR SOME ELEMENT syntax. In your case, for retrieving them in the SELECT list, changing the projection or your data model is probably the pragmatic choice. Note that collections are a really powerful feature when working in the Object paradigm, but somewhat constrained by SQL standard operations when accessing through the relational paradigm.

I may misunderstand your intentions but

when I use "ResultSet.%Get("Collection")", all I get is a list of the primary keys of the objects

now you all you miss for each PrimaryKey is 

Set collB=##class(ICT.Experiments.B).%OpenId(primaryKey) 


Set collC=##class(ICT.Experiments.C).%OpenId(primaryKey)

and the object is yours.

With your class definition,  PrimaryKey is the Idkey of the Object.

I am aware that I can use the PKs to open each object but wouldn't it impact the performance if the result set has a lot of rows?

if you just look for a specific property instead of a larger part of the object
you may use 

set value=##class(ICT.Experiments.B).<propertyname>GetStored(primaryKey)

instead of 

set collB=##class(ICT.Experiments.B).%OpenId(primaryKey)

to avoid loading the full object

Thanks but I chose to go for Eduard's first answer.

I am aware that I can use the PKs to open each object but wouldn't it impact the performance if the result set has a lot of rows?