Create Calculated Property in Persistent Class based on Relationship

Question

Question

Vivian Lee · Nov 18, 2021

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!

Discussion (12)1

Log in or sign up to continue

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 ];

1 1

score 0 · Answer 1 · 2021-11-18T15:58:49-05:00

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)?

score 0 · Answer 2 · 2021-11-18T16:48:20-05:00

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.

score 0 · Answer 3 · 2021-11-18T17:11:57-05:00

@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.

score 0 · Answer 4 · 2021-12-03T02:57:29-05:00

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 [ Calculated, SqlComputeCode = {n id,r s id={%%ID} &sql(select Company->Name into :r from Sample.Employee where %ID=:id) s {*}=r}, SqlComputed ];

...
}

score 0 · Answer 5 · 2021-12-03T08:09:42-05:00

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 ];

}

score 0 · Answer 6 · 2021-12-03T08:26:29-05:00

Vitaliy Serdtsev · Dec 3, 2021

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

0 0

score 0 · Answer 7 · 2021-12-06T04:26:00-05:00

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.

score 0 · Answer 8 · 2021-12-06T04:41:27-05:00

I meant the length of the code.

I can write like this:

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

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

score 0 · Answer 9 · 2021-12-06T06:51:00-05:00

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.

score 0 · Answer 10 · 2021-12-06T07:05:02-05:00

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.

score 0 · Answer 11 · 2021-12-06T07:32:20-05:00

Eduard Lebedyuk · Dec 6, 2021

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

0 0