New post
Question
· Jun 28, 2017

Navigating through relationships via SqlComputeCode

#Object Data Model #SQL #Caché

Hi!

I have a global-mapped class that I want to add a transient property through (or at least, not stored on the database). This property is the sum total of various charge classes that are linked to the class via child relationships.

I want this new transient property to be visible at least via SQL.

My current approach is to total it up in a ClassMethod called via SqlComputeCode. It seems to work, but feels clunky. I wonder if there is a way without passing the %%ID through? I tried it with $this but that only returns the object name in a ClassMethod. I think it has to be a ClassMethod if called from SqlComputeCode?

Code snippet:

Property Amount As %Numeric [ SqlComputeCode = { set {*} = ##class(MyClass).GetTotalAmount({%%ID})}, SqlComputed, Transient ];

ClassMethod GetTotalAmount(currentID) As %Numeric
{
Set oMyClass = ##class(MyClass).%OpenId(currentID)

set total=0

// iterate over the Freight Charges, and add them to the total
  set chargeCodeKey = ""
  Do {
     Set oFreightChargesDetail = oMyClass.FreightChargesDetailPointer.GetNext(.chargeCodeKey)
     set:$IsObject(oFreightChargesDetail) total = total + oFreightChargesDetail.Amount
  } While (chargeCodeKey '= "")

​quit total
}

I also tried overriding the properties Get method, but that doesn't seem to be visible in SQL if it's transient.

Thanks!

Chris

Discussion (4)0
Log in or sign up to continue
Dan Pasco · Jun 28, 2017

Hi Chris,

Good question! This is a poorly understood area and there is much to say about it. I'll try to be brief. Feel free to follow up.

Your new property falls into the category of properties commonly referred to as 'derived columns' in other products. We support two different flavors - 'always computed' and 'triggered computed'. An 'always computed' property is indicated by the CALCULATED keyword. A 'triggered computed' property is 'NOT CALCULATED' although this is the default if you don't specify it. A triggered computed property can be TRANSIENT or stored (not transient - again, the default) and its value is triggered to recompute if the value of any property specified in the SQLCOMPUTEONCHANGE list of properties changes. In an object context, you need to be a aware that the value of a TRANSIENT property is directly settable unless you take precautions to prevent that.

The advantage to using SQLCOMPUTED/SQLCOMPUTECODE to derive a property value is that SQL and Objects seamlessly (almost) recognize the derived value and invoke the computation at the proper time. Overriding Get/Set methods can be used to derive property values but SQL will not recognize this as you have already discovered. 

SQL does not operate in an instance context. In other words, there is no oref available to SQL so SQLCOMPUTECODE must run in a class context and not in an instance (object) context. No oref. The way you have implemented your code is correct and, sorry to say, there isn't a cleaner way that I know about. 

There is one other thing you should know. When you open the object you may not actually get a new oref if that object was previously open in your process. That goes for any call to %Open([d]  - we cache the OID's with their active orefs and always return the existing oref from %Open if one is present in process memory. What does that mean? It means that you may see data in memory that has not committed to storage - Objects sees the data because of the in-memory context but SQL won't because it runs in an on-disk (storage) context. By opening objects you may produce a computed value that is different from the same data that is stored. There are ways you can avoid this problem, one being to store the derived value and trigger it to recompute on INSERT and UPDATE. This is easily defined by adding %%INSERT and %%UPDATE to the SQLCOMPUTEONCHANGE list of trigger properties.

Not so brief.

-Dan

4 0
Chris Bransden · Jul 13, 2017

Hi Again!

I'm not sure if this related, but I am seeing strange behaviour when I try and calculate and array property.

Eg:

 Property Amount2 As array Of %Numeric [ SqlComputeCode = { set {*} = ##class(ContainerSize).GetTotalAmount2({%%ID})}, SqlComputed, SqlComputeOnChange = (%%UPDATE, %%INSERT), Transient ];

Even if my method does nothing but quits out with a new Array object:

 quit ##class(%Collection.ArrayOfDT).%New()

When I open an instance:

macdocs:MACDEV1>w ##class(MacDocs.SalesMarketing.Quotation.ContainerSize).%OpenId("0||WALK04332||1||20").Amount2
 
 Set ptr=0 While $listnext(serialcol,ptr,item) { Set i%Amount2($list(item))=$lis
           ^
t(item,2) }
<LIST>zAmount2GetSwizzled+5^MacDocs.SalesMarketing.Quotation.ContainerSize.1
macdocs:MACDEV1 2e1>w ##class(MacDocs.SalesMarketing.Quotation.ContainerSize).%OpenId("0||WALK04332||1||20").Amount2
3@%Collection.ArrayOfDT

Bizarrely it works the second time?!

Perhaps there's a better way of calculating array properties upon object load?

0 0
Stephen Canzano · Jul 13, 2017

If I was asked to do this the one change I would do/have done in the past is implement the class method with sql code vs object code.  Whether you are using a storage strategy based on CacheSQLStorage or CacheStorage when you open an object all of the properties that are stored in the global are "swizzled" into memory and allocated a memory location so that later on when you do object.property you have the value.  In your specific question, you want the total of all charges from the parent.  I would do something like

 

&sql(select sum(Amount) into :Total FROM FreightChargesDetail where Freight=:currentID)

in truth when I have done this I've actually not used a class method at all but rather in my SQLComputeCode I would do something like

 { 
  New ParentId,Total Set ParentId={%%ID} &SQL(SELECT SUM(Amount) into :Total FROM FreightChargesDetail where Freight=:ParentId) Set {*}=$Get(Total)}, SqlComputed, Transient ];
 

A couple of reasons.

  • I do not see a reason to expose an interface/classmethod that gets the total as it's already satisfied by the property definition.
  • I do not believe SQLCompute code is implemented inside of a procedure  so any local variable should be New'ed so that do not flow out of the stack.
0 0