Help constructing SQL query using array of objects property
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?
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
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.
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.
Many thanks, Dan, I appreciate any advice regarding the schema definition. I tried to get at this in an earlier post, but I probably didn't ask the right questions.
I thought this might do the trick but I am not 100% sure...
SELECT *
FROM PET.ImageStudy_Injection isi
INNER JOIN PET.RadioTracer rt
ON rt.ID = isi.Injection_RadioTracer
INNER JOIN PET.ImageFile if
ON if.Study = isi.ImageStudy
WHERE rt.TracerName = 'roadrunner'
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=?