New post
Question
· Apr 19, 2019

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:

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

Discussion (9)0
Log in or sign up to continue
Robert Cemper · Apr 19, 2019

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) 

or

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

and the object is yours.

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

2 0
Eduard Lebedyuk · Apr 23, 2019

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

Arguments:

  • 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)
0 0
Benjamin De Boe · Apr 23, 2019

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.

0 0