IRIS SQL - query nested collection property

Question

Question

I'm experimenting with adapting SDA3 object model to store medical data in relational form, e.g.:

class Demo.DemoPatient extends (%Persistent, HS.SDA3.Patient) {}

The HS.SDA3.Patient class has the Aliases property which is a nested collection (list) of objects of type HS.SDA3.Name:

#dim record as Demo.DemoPatient = ##class(Demo.DemoPatient).%New()
set record.Name.FamilyName = "Clemens"
set record.Name.GivenName = "Samuel"
set record.BirthTime = "1935-11-30T12:00:00"

#dim alias as HS.SDA3.Name = ##class(HS.SDA3.Name).%New()
set alias.FamilyName = "Twain"
set alias.GivenName = "Mark"  
do record.Aliases.Insert(alias)

set status = record.%Save()
if $$$ISERR(status) {
    Write "Error: " _ $SYSTEM.Status.GetErrorText(status)   
}

How could I select a patient record searching by his/her Aliases.FamilyName? Something like:

select ID, Name_GivenName from Demo.DemoPatient where FOR SOME %ELEMENT(Demo.DemoPatient.Aliases) (???? = 'Twain')

Product version: IRIS 2024.1

Discussion (6)1

Log in or sign up to continue

Robert Cemper · Oct 20

the example builds on the fact that the storage structure of the serial object is known

 Storage Default
{
<Data name="serItemState">
<Value name="1">
<Value>Subject</Value>
</Value>
<Value name="2">
<Value>Change</Value>
</Value>
<Value name="3">
<Value>Color</Value>
</Value>
</Data>
<State>serItemState</State>
<StreamLocation>^rcc.IC.serItemS</StreamLocation>

in this case color is #3 in serial storage ==>> $list(%Value,3)

So you need to know the position of FamilyName in HS.SDA3.Name (e.g.7 ?)

then you can use $list(%value,7) = 'Twain' as condition

7 is just a guess as I don't have any HS* classes at hands

1 0

score 0 · Answer 1 · 2024-10-20T17:00:39-04:00

This article is focused on indexing, though it shows the principle of the SQL query
Effective use of Collection Indexing and Querying Collections through SQL

score 0 · Answer 2 · 2024-10-20T17:09:59-04:00

Thanks Robert, I've read this article, but the syntax isn't entirely clear to me:

Select ID,Company from rcc_IC.ItemList
Where FOR SOME %ELEMENT(rcc_IC.ItemList.Items) ($list(%Value,3) in ('blue','yellow'))

Here the integer is used, but I need to search using a named property

score 1 · Answer 3 · 2024-10-20T19:29:27-04:00

a somewhat 'dirty' approach

select ID, Name_GivenName from Demo.DemoPatient 
where FOR SOME %ELEMENT(Demo.DemoPatient.Aliases) ($LISTFIND(%value,'Twain')>0)

advantage - no need to know the structure of your serial class
risk - false positives for unexpected coincidences. eg. Washington, John, ...

score 0 · Answer 4 · 2024-10-21T02:30:51-04:00

The option with $LIST(...) works, thanks again (not so elegant though but OK as a workaround). I'm thinking about registering a feature request.

score 1 · Answer 5 · 2024-10-21T10:17:00-04:00

To get the property position dynamically by program you may use this ClassMethod

ClassMethod PropSeq(classname As %String = "", propname As %String = "")
   As %String [ SqlProc ]
{
    if classname="" set classname=..%ClassName(1)
    set pos=0
    &sql(
        SELECT SequenceNumber INTO :pos 
        FROM %Dictionary.CompiledProperty
        WHERE transient=0
        AND parent=:classname
        AND name=:propname
    )
    if SQLCODE set pos="-1;"_SQLCODE
    quit pos
}