Question Vivian Lee · Nov 18, 2021

Create Calculated Property in Persistent Class based on Relationship

Hi Dev Community,

I have a persistent Document class that has a FileName string property and another Question class that has an optional one-to-many relationship with Document.

I'm trying to add a SqlComputed property to the Question class (docFileName) where docFileName = Document.FileName if there is a related Document or an empty string if there isn't one. 

I'd prefer the property to be SqlComputed so that if Question.Document changes, Question.docFileName will automatically update.

Does anyone know the syntax for accessing the properties of a related object in the SqlComputeCode?

Thanks!

Comments

David Hockenbroch · Nov 18, 2021

I don't think you can reference relationships in SqlComputeCode, so I'm not sure how you'd do this. But out of curiosity, why have a calculated field in the question class rather than just referring to it as Document.FileName (or Document->FileName in SQL)?

0
David Hockenbroch · Nov 18, 2021

Here's a possible alternative to using SqlComputeCode.

You can override the getter method for that property so that it always returns the related document name. In your Question class, define your docFileName property as:

Property docFileName As %String [ReadOnly];

You'll want it to be read only because you aren't going to want to be able to set this property in the question object. You're going to want to retrieve it from the document object. If it could also be set here, you'd have things weirdly out of sync. Then, in that same class, include the following method:

Method docFileNameGet() As %String{
    if ..Document '= ""{
        return ..Document.FileName
    }
    else{
        return ""
    }
}

Once you do that, whenever you refer to the question's docFileName, it'll give you the document's file name, or an empty string if there isn't one.

0
Vivian Lee · Nov 18, 2021

@David Hockenbroch - Thanks for the reply!  

I was looking to create a calculated field since my Question class extends AppS.REST.Model.Adaptor and the JSON returned from the GET request 'api/standard/question' would include the class properties but not their relationships (e.g. Question.Document).

I ended up setting the docFileName property when creating a new Question instance - which seemed to be the easiest/most straightforward way to get the docFileName in the returned JSON. 

0
Robert Cemper · Nov 18, 2021

it is possible, but I'd rate it as highly inefficient. You have been warned.
working for objects and SQL

/// The company this employee works for.
Relationship Company As Company [ Cardinality = one, Inverse = Employees ];

Property Cname As %String [ Calculated, SqlComputeCode =
    {set {*}=##class(Company).%OpenId({Company}).Name }, SqlComputed ];
0
Vitaliy Serdtsev · Dec 3, 2021

Another option 

/// This sample persistent class represents an employee.<br>
Class Sample.Employee Extends Person
{

...

/// The company this employee works for.
Relationship Company As Company Cardinality = one, Inverse = Employees ];

Property Cname As %String CalculatedSqlComputeCode = {id,id={%%ID} &sql(select Company->Name into :r from Sample.Employee where %ID=:id{*}=r}, SqlComputed ];

...
}
0
Eduard Lebedyuk · Dec 3, 2021

You do it by chaining GetStored methods:

Class Document Extends %Persistent {

Property Filename As %VarString;

}

Class Question As %Persistent {

// Or relationship
Property Document As Document;

Property Filename As %String [ Calculated, SqlComputeCode = {set {*}=##class(Document).FilenameGetStored({Document})}, SqlComputed ];

}
0
Vitaliy Serdtsev  Dec 3, 2021 to Eduard Lebedyuk

And if you need to get a->b->c->d and they at the same time can be null?

0
Eduard Lebedyuk  Dec 6, 2021 to Vitaliy Serdtsev

if you need to get a->b->c->d 

That's what chained GetStored is for!

and they at the same time can be null

GetStored is a safe method: GetStored("") -> "", so it can be safely used even if some values are NULL.

0
Vitaliy Serdtsev  Dec 6, 2021 to Eduard Lebedyuk

I meant the length of the code.

I can write like this:

<..> select a->b->c->into :r from table where %ID=:id <..>

Can you give a complete code example for a->b->c->d using GetStored?

0
Eduard Lebedyuk  Dec 6, 2021 to Vitaliy Serdtsev

Sure

Property D As %String [ Calculated, SqlComputeCode = {set {*}=##class(CClass).DGetStored(##class(BClass).CGetStored(##class(ClassA).BGetStored({A})))}, SqlComputed ];

Would be way faster than SQL approach too.

0
Vitaliy Serdtsev  Dec 6, 2021 to Eduard Lebedyuk

That's what I expected to see.

But need remember that

This method is only available for stored properties.
In other words, if the storage changes tomorrow, will have to rewrite a bunch of code.

0
Eduard Lebedyuk  Dec 6, 2021 to Vitaliy Serdtsev

Only if the property becomes calculated after being stored which I don't see happening that often.

0