New post
Question
· Jul 31, 2018

Help constructing SQL query using array of objects property

#SQL #Caché

I have the following class definitions:

Class PET.RadioTracer Extends %Persistent
{
Property TracerName As %String;
}
Class PET.Injection Extends %SerialObject
{
Property RadioTracer As RadioTracer;
}
Class PET.ImageStudy Extends %Persistent
{
Property Injection As array Of Injection;
}
Class PET.ImageFile Extends %Persistent
{
Relationship Study As PET.ImageStudy [ Cardinality = parent, Inverse = ImageFiles ];
Property InjKey As %String;
}

I can refer to the TracerName for a given ImageFile object with the following syntax:
obj.Study.Injection.GetAt(obj.InjKey).RadioTracer.TracerName

Is it possible to write an SQL statement to search the PET.ImageFile table to find a match based on the RadioTracer.TracerName?

Discussion (6)1
Log in or sign up to continue
Dan Pasco · Aug 1, 2018

If the default array projection for PET.ImageStudy.Injection is used then there will be an SQL table projected from the array of objects. By default, the name of this table will be PET.ImageStudy_Injection. This projected table will define a column referencing the PET.ImageStudy table. I believe this column will be named ImageStudy. There will also be a column named Injection_RadioTracer in this table and that column will be a reference to the RadioTracer table.

If the array, Injection, in the PET.ImageStudy class is not stored using defaults then this becomes a more complex problem - still solvable but more difficult. 

Let me know if array storage defaults are used and then I can help compose a query - or perhaps you can do it with the information I provided above. You can verify this information using the System Explorer->SQL to view the table definitions.

-Dan

1 0
Virginia Rogers · Aug 1, 2018

Thank you Dan.

It does not appear to be using the default storage, as there is not a separate table for ImageStudy_Injection.  The Injection is stored as a column in the ImageStudy table.  I'm not sure why.  I didn't change any defaults. Though perhaps I tried using a list, instead of array, earlier.  If it's easier when stored in a separate table, can I change the storage mechanism?

Appreciate your help.   I will not get back to working on this until next week.

0 0
Dan Pasco · Aug 2, 2018

If you have flexibility with the schema definition then there are certainly other models that would make a query such as this simpler.

With an array collection that is stored as a serialized list you will have to either write a procedure, define some computed properties, or you will have to use some of the InterSystems collection extensions that may not work very well with collections of objects.

Our projection of serial types (classes that extend %SerialObject) uses a "flattening model" and we don't use that for serial collections stored as "listnode" structures. We do use it in the specific case of an array of serial objects stored using a "subnode" structure - the default.

I know this all sounds a bit scary but it doesn't need to be. When you get back to this we can walk through the options.

0 0
Virginia Rogers · Aug 20, 2018

Thank you Dan and Gerd.  After reviewing your suggestions I reset the storage so that the array property would project in the default way, as a separate table.  After some trial and error, I came up with the following query, which seems to do what I need:

select * from PET.ImageStudy as std

left outer join PET.ImageStudy_Injection as inj  on std.ID=inj.ImageStudy

left outer join PET.PETImageFile as img on img.Study=std.ID AND img.InjKey=inj.element_key  

where Injection_RadioTracer->TracerName=?

0 0