New post
Question
· Aug 25, 2020

How to create index inside Array

#ObjectScript #SQL #InterSystems IRIS

I use these example classes:
Class Sample.Address Extends (%SerialObject, %Populate)
{Property Street As %String;
Property City As %String;
Property State As %String(POPSPEC = "USState()");
Property Zip As %String;}

Class Sample.Person1 Extends (%Persistent, %Populate)
{  Property Name As %String;
Property Home As Sample.Address;
Property AR As array Of Sample.Address; }

In SQL it is Table Sample.Person1

No problem to define INDEX on Home_State

But Table Sample.Person1_AR

HowTo define an INDEX on AR_State ?
Index st On AR.State;

Does not compile!

Discussion (12)3
Log in or sign up to continue
Benjamin De Boe Aug 25, 2020 to Ba Moser

This is not possible today, but happens to be something due for 2020.4 or 2021.1 in the form of expression indices.

The documentation may be slightly confusing here as the BuildValueArray() mechanism applies to non-collection properties and not "array of" or "list of" properties. The section that describes it just happens to be placed right after the section describing how to use the ELEMENTS trick to index them in their entirety. Note that you can use BuildValueArray() for %ArrayOfObjects properties, but those aren't projected as a child table.

A possible workaround would be through a separate property that's just there as an intermediary for your index:

Property AR As array Of Sample.Address;
Property AllStates As %String [ Calculated, ReadOnly, SqlComputeCode = {set {*} = {%%ID}}, SqlComputed ];
Index AllStatesIDX On AllStates(ELEMENTS);
ClassMethod AllStatesBuildValueArray(value, ByRef valueArray) As %Status
{
  kill valueArray
  set tObj = ..%OpenId(value), tKey = ""
  for {
    set tAddress = tObj.AR.GetNext(.tKey)
    quit:tKey=""
    set valueArray(tKey) = tAddress.State
  }
  quit $$$OK
}

Then you can include it in queries:

select * from sample.person where FOR SOME %ELEMENT(AllStates) (%VALUE = 'KY')
4 0
Timothy Leavitt · Aug 25, 2020

I'm intrigued to hear about expression indices - sounds really cool.

Without those, another option is just to have a separate class/table. Suppose the key to the AR array is the address type (Home, Office, etc.); then you could have:

Class Sample.Person1 Extends (%Persistent, %Populate)
{

Property Name As %String;

Relationship Addresses As Sample.PersonAddress [ Cardinality = children, Inverse = Person ];

}

Class Sample.PersonAddress Extends (%Persistent, %Populate)
{

Relationship Person As Sample.Person1 [ Cardinality = parent, Inverse = Addresses ];

Property Type As %String;

Property Address As Sample.Address;

}

Sample.PersonAddress then can have whatever plain old normal indices you want (except bitmap indices - if you want those, make it one-to-many instead of parent/child).

Generally: any time you add an array property - especially an array of objects - it's worth stepping back and thinking about whether it should just be its own full-blown class/table.

4 0
Dan Pasco · Aug 25, 2020

Timothy Leavitt's excellent response notwithstanding, this is supported. I do fully embrace the option presented by Timothy Leavitt. The structures I demonstrate here actually produce a model very close to his and the index, since it includes both KEYS and ELEMENTS is projected to the child table projected from the addresses array. Of course, reversing KEYS and ELEMENTS in the index key specification would make the index more useful for searching on city name.

This definition:

Property addresses As array Of Sample.Address;
Index xA On (addresses(KEYS), addresses(ELEMENTS).City)

Not only works but the filing code also recognizes the ability to fold both properties in the index into the same iterator:

    If ('pIndexHandle)||($Ascii($Get(pIndexHandle("Sample.Person")),5)=1) {
        set bsv26N1 = $Order(^Sample.PersonD(id,"addresses",""))
        While bsv26N1 '= "" {
            Set bsv0N8=$zu(28,##class(Sample.Address).%Open($select(^Sample.PersonD(id,"addresses",bsv26N1)="":"",1:$listbuild(^Sample.PersonD(id,"addresses",bsv26N1)_""))).City,7,32768)
            Set ^Sample.PersonI("xA",bsv26N1,bsv0N8,id)=$listget(bsv0N2,1)
            set bsv26N1 = $Order(^Sample.PersonD(id,"addresses",bsv26N1))
        }
    }

And a quick test shows this structure is produced:

panther.local:XDBC:USER>d ##class(Sample.Person).Populate(10) 

panther.local:XDBC:USER>zw ^Sample.PersonI("xA")

^Sample.PersonI("xA","A886"," GANSEVOORT",3)=""

^Sample.PersonI("xA","B350"," MIAMI",6)=""

^Sample.PersonI("xA","B748"," NEWTON",3)=""

^Sample.PersonI("xA","C135"," UKIAH",9)=""

^Sample.PersonI("xA","C261"," ALBANY",1)=""

^Sample.PersonI("xA","C883"," DENVER",2)=""

^Sample.PersonI("xA","D162"," ST LOUIS",4)=""

And this has been in the product since maybe 2010. I couldn't find the original release note for this but I did find a change that fixed a problem when consolidating the iterators and that fix is present in 2010.2.

0 0