Written by

UKK Köln

Question Dmitrii Baranov · Oct 20, 2024

IRIS SQL - query nested collection property

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 (0)0

Add reply

Comments

Robert Cemper · Oct 20, 2024

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

0 0

Dmitrii Baranov Oct 20, 2024 to Robert Cemper

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

0 0

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

0 0

Robert Cemper · Oct 20, 2024

a somewhat 'dirty' approach

selectID, Name_GivenName from Demo.DemoPatient 
whereFORSOME %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, ...

0 0

Dmitrii Baranov Oct 21, 2024 to Robert Cemper

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

0 0

Robert Cemper · Oct 21, 2024

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=0ANDparent=:classname
        ANDname=:propname
    )
    if SQLCODE set pos="-1;"_SQLCODE
    quit pos
}

0 0