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.

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

I should update the CSV and CSV2CLASS utility procedures to take advantage of table-valued functions. Then you could select JSON_ARRAYAGG() from CSVTVF(<arguments go here>). Of course you would have to supply the constructor for the objects, probably using JSON_OBJECT.

Have you considered using $xecute?

Version:1.0 StartHTML:00000092 EndHTML:00001371 StartFragment:00000172 EndFragment:00001337 CachÙ Studio clip   Class User.Exec [ Abstract ]
{

ClassMethod test(STATUS = 1) As %String
{
set greeting = $xecute("(STATUS) { return $S(STATUS:""HELLO"", 1:""GOODBYE"") }",STATUS)
return greeting
}

}

and here is this code in action:

PANTHER:XEP:DEV>w ##class(Exec).test()

HELLO

PANTHER:XEP:DEV>w ##class(Exec).test(0)

GOODBYE

As you have already discovered, there is no opportunity for a user to change the collection type class. That class is determined by the compiler when processing LIST and ARRAY keywords. This assignment, indicated by the compile-only keyword of RUNTIMETYPE, occurs during inheritance resolution and it cannot be overridden by the user. Your solution of coercing the RUNTIMETYPE using a method generator is not completely correct even though the runtime behavior seems correct. The problem with using the method generator is that is runs late in the compile cycle and the modification you make will not be processed by compiler actions that have already completed by this time. Your changes - simple addition of new methods - will probably work as you expect as long as those methods don't require any additional compiler processing. 

A user has only two ways to add methods to a class member - property in this case. The first and easiest is through the use of the declared type class. Internally this is cPROPtype (you have already discovered this I'm sure ;) ). Methods inherited from the datatype class (actually these are "datatype generator" classes) are combined with the property to produce the datatype implementation. These methods are typically not instance methods of the containing class but one could say they are instance methods of the generated datatype.

The second way for a user to inject methods into a class member runtime is by overriding the member super class. In the old days this was referred to as the property class but that definition is expanded to include other member types such as query, index, and so on. Methods inherited by a member from the member supertype class are typically instance methods of the containing class. For a property these methods include Get and Set. There are two keywords that a user can define - MEMBERSUPER and PROPERTYCLASS.

Both mechanisms for adding methods to a member's runtime produce what we call "composite methods". That doesn't really mean anything other than the name of the method which is composed of the member name and the inherited method name. There is an internal difference but that has little impact on runtime behavior. Composite methods look very much like normal methods but sometimes confuse users because it seems there should be a dot in the middle! For example, direct invocation of the Name property's Set method is

    do oref.NameSet(value)

This could be confusing as it seems logical to use

    do oref.Name.Set(value)

It is possible for a user to define a property super class containing method generators that will produce composite member methods.  This is not a simple process but it can be done. If this interests you then I can provide more details.

What I would recommend is an instantiable helper class. You can construct an instance of the helper class, passing in the collection instance. Methods in the helper class then operate on that instance. This is the model we use for iterators operating on instances of %Library.DynamicAbstractObject. 

At one time - in the pre-2.0 days of Caché Objects - we did discuss this but decided to settle on the implementation we currently have. The landscape is much more complex these days and there is a greater chance of a composite method name collision or parameter name collision. Not too long ago, I had to redesign the index constraint projection (primary key, unique) and referential constraint projection (foreign key) because we had name collisions.

There is a short answer to this and a long explanation. The short answer is that the warning is accurate and you might experience problems if you use the same name for two class members. When defining classes myself, I keep method names and property names distinct, mostly by using nouns and verbs. For indices, I prefix the name with a letter indicating the index type.

The reason why you should avoid non-unique names is simple. Most member types inherit methods from what I refer to as the member super tree. The member super tree has an abstract top node with two subnodes, the member super class and the declared type class. The member super class for properties is often called the "property class" and the declared type class is the class the author defines as the type. Methods inherited from the member super tree are implemented in the resolved member runtime as a composite name formed from the member name and the method name. For a property named 'Foo', you might see methods in the class runtime named 'FooSet' and 'FooGet'.

If two members share a name it is possible that a composite method name will conflict between the two member types. We try to avoid this when implementing our member super classes and our type classes but it is still possible to encounter runtime method name conflicts.

This method call is not part of any release but, as others have mentioned, you can use %Net.Http to submit a request and receive a response. I can help you write that request. How does this look?

USER>set matrix = ##class(<your classname goes here>).GetJSON("https://maps.googleapis.com/maps/api/distancematrix/json?units=imperial&origins=40.6655101,-73.89188969999998&destinations=40.6905615%2C-73.9976592%7C40.6905615%2C-73.9976592%7C40.6905615%2C-73.9976592%7C40.6905615%2C-73.9976592%7C40.6905615%2C-73.9976592%7C40.6905615%2C-73.9976592%7C40.659569%2C-73.933783%7C40.729029%2C-73.851524%7C40.6860072%2C-73.6334271%7C40.598566%2C-73.7527626%7C40.659569%2C-73.933783%7C40.729029%2C-73.851524%7C40.6860072%2C-73.6334271%7C40.598566%2C-73.7527626&key=<you api key goes here>",{"SSLConfiguration":"<your SSL Configuration goes here>"})

USER>write matrix.%ToJSON()

{"httpStatus":"200","message":"HTTP/1.1 200 OK","content":{"destination_addresses":["67-89 Pacific St, Brooklyn, NY 11201, USA","67-89 Pacific St, Brooklyn, NY 11201, USA","67-89 Pacific St, Brooklyn, NY 11201, USA","67-89 Pacific St, Brooklyn, NY 11201, USA","67-89 Pacific St, Brooklyn, NY 11201, USA","67-89 Pacific St, Brooklyn, NY 11201, USA","585 Schenectady Ave, Brooklyn, NY 11203, USA","66-0-66-26 103rd St, Rego Park, NY 11374, USA","931-947 N Village Ave, Rockville Centre, NY 11570, USA","300-448 Beach 19th St, Far Rockaway, NY 11691, USA","585 Schenectady Ave, Brooklyn, NY 11203, USA","66-0-66-26 103rd St, Rego Park, NY 11374, USA","931-947 N Village Ave, Rockville Centre, NY 11570, USA","300-448 Beach 19th St, Far Rockaway, NY 11691, USA"],"origin_addresses":["566 Vermont St, Brooklyn, NY 11207, USA"],"rows":[{"elements":[{"distance":{"text":"6.7 mi","value":10754},"duration":{"text":"37 mins","value":2201},"status":"OK"},{"distance":{"text":"6.7 mi","value":10754},"duration":{"text":"37 mins","value":2201},"status":"OK"},{"distance":{"text":"6.7 mi","value":10754},"duration":{"text":"37 mins","value":2201},"status":"OK"},{"distance":{"text":"6.7 mi","value":10754},"duration":{"text":"37 mins","value":2201},"status":"OK"},{"distance":{"text":"6.7 mi","value":10754},"duration":{"text":"37 mins","value":2201},"status":"OK"},{"distance":{"text":"6.7 mi","value":10754},"duration":{"text":"37 mins","value":2201},"status":"OK"},{"distance":{"text":"3.4 mi","value":5426},"duration":{"text":"19 mins","value":1136},"status":"OK"},{"distance":{"text":"8.5 mi","value":13740},"duration":{"text":"23 mins","value":1399},"status":"OK"},{"distance":{"text":"15.9 mi","value":25546},"duration":{"text":"29 mins","value":1755},"status":"OK"},{"distance":{"text":"13.2 mi","value":21296},"duration":{"text":"34 mins","value":2040},"status":"OK"},{"distance":{"text":"3.4 mi","value":5426},"duration":{"text":"19 mins","value":1136},"status":"OK"},{"distance":{"text":"8.5 mi","value":13740},"duration":{"text":"23 mins","value":1399},"status":"OK"},{"distance":{"text":"15.9 mi","value":25546},"duration":{"text":"29 mins","value":1755},"status":"OK"},{"distance":{"text":"13.2 mi","value":21296},"duration":{"text":"34 mins","value":2040},"status":"OK"}]}],"status":"OK"}}

And with a little work, this can also be invoked as an SQL function:

That phrase is not a reference to a special type of class, instead it refers to the reason the class is added to the set of classes to be compiled. For example, if you compile Sample.Employee it becomes a "direct class" in the set of classes to be compiled. The compiler evalutes each class in the set of classes to produce an "expanded set of classes". In this case, Sample.Person would be included as an "expanded class". HTH.

To set a property whose type class is a serializable class (extends %Library.SwizzleObject - streams, serial classes, persistent classes) you need to call the property's SetObject or SetObjectId method if you wish to set the value to a serialized value (id value often). I think that in your example, tvar1 is holds an ID value of the referenced ZenCrm.Relationtypes class? If so, then this should work:

do RelationMatrix.RelationAIDSetObjectId(tvar)

The ID is assigned by %SaveData() and %OnBeforeSave is invoked before %SaveData is called. For a new object the ID value will not be reliable until after %SaveData has returned. I do not know whether or not this is documented explicitly.

I defined this simple class:

 Class Community.IdBeforeSave Extends (%Persistent, %Populate)
{

Parameter USEEXTENTSET = 1;

Parameter DEFAULTGLOBAL = "^C.I";

Property Name As %String;

Property DOB As %Date;

Property SSN As %String;

/// This callback method is invoked by the <METHOD>%Save</METHOD> method to 
/// provide notification that the object is being saved. It is called after 
/// the object's data has been successfully written to disk.
/// 
/// <P><VAR>insert</VAR> will be set to 1 if this object is being saved for the first time.
/// 
/// <P>If this method returns an error then the call to <METHOD>%Save</METHOD> will fail.
Method %OnAfterSave(insert As %Boolean) As %Status [ Private, ServerOnly = 1 ]
{
set id = ..%Id()
set ^C.S($increment(^C.S)) = "OnAfterSave, insert = "_$select(insert:"'true'",1:"'false'") _ ", ID = " _ $select(id'="":id,1:"<null>")
Quit $$$OK
}

/// This callback method is invoked by the <METHOD>%Save</METHOD> method to 
/// provide notification that the object is being saved. It is called before 
/// any data is written to disk.
/// 
/// <P><VAR>insert</VAR> will be set to 1 if this object is being saved for the first time.
/// 
/// <P>If this method returns an error then the call to <METHOD>%Save</METHOD> will fail.
Method %OnBeforeSave(insert As %Boolean) As %Status [ Private, ServerOnly = 1 ]
{
set id = ..%Id()
set ^C.S($increment(^C.S)) = "OnBeforeSave, insert = "_$select(insert:"'true'",1:"'false'") _ ", ID = " _ $select(id'="":id,1:"<null>")
Quit $$$OK
}

and then populated it with 5 objects. This is the result:

LATEST:USER>zw ^C.S

^C.S=10

^C.S(1)="OnBeforeSave, insert = 'true', ID = <null>"

^C.S(2)="OnAfterSave, insert = 'true', ID = 1"

^C.S(3)="OnBeforeSave, insert = 'true', ID = <null>"

^C.S(4)="OnAfterSave, insert = 'true', ID = 2"

^C.S(5)="OnBeforeSave, insert = 'true', ID = <null>"

^C.S(6)="OnAfterSave, insert = 'true', ID = 3"

^C.S(7)="OnBeforeSave, insert = 'true', ID = <null>"

^C.S(8)="OnAfterSave, insert = 'true', ID = 4"

^C.S(9)="OnBeforeSave, insert = 'true', ID = <null>"

^C.S(10)="OnAfterSave, insert = 'true', ID = 5"

HTH,

Dan

Yes, this should work but you are missing a step - you must execute the statement. Dynamic SQL allows for a statement to be prepared once and executed many times. A prepared statement is executed, returning a statement result. You can provide different parameter values for each execution.

set result = tStatement.%Execute()

Alternatively, you can do this in a single comment -

set result = $system.SQL.Execute("UPDATE table Set Status = 'Completed' WHERE ID in (1,2,3,4)")

-Dan