IRIS SQL - query nested collection property

Question

Question

Dmitrii Baranov · Oct 20, 2024

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, 2024 to Dmitrii Baranov

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

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