· Jul 11, 2017

Relationships - iterate through all the Manys' side

I am working with Caché Relationships.

I know how to iterate through the One side of the relationship and for each One iterate through all the various Manys.

What I have been trying to figure out, is the code (I can put into a Class) that will start at the top of the Manys, iterate down and for each of the Manys, pull the associated One.

I can do this with SQL or Globals, but I want to use only Class type code.

Is that clear? Any help?

Discussion (6)0
Log in or sign up to continue

Sample.Company and Sample.Employee share one company/many employees relationship.

Do you want to iterate over employees and display a company name for each?

set rs = ##class(Sample.Employee).ExtentFunc()
while rs.%Next() { set emp = ##class(Sample.Employee).%OpenId(rs.ID) w emp.Company.Name,! }

You can even get company names even without opening objects:

set rs = ##class(Sample.Employee).ExtentFunc()
while rs.%Next() { w ##class(Sample.Company).NameGetStored(##class(Sample.Employee).CompanyGetStored(rs.ID)),! }


Thank you for your answer, it was just what I was looking for.

But, what is ExtentFunc?

I did a search in the I/S Documentation and found no results.

I looked in %Library.Persistent but did not find it there.

I searched the Developer Community and got 1 hit, this post.

I even tried to Google it.

I did find the class %sqlcq.SAMPLES.cls9 from doing a ZW on the rs variable, but could not find the class.

So my friend, where did you get the method ExtentFunc from? 

I wish to thank Eduard and Daniel for your comments on this post. Although I almost regret asking my question. It will take me awhile to understand everything that was said.

And that brings me to another point, why does asking a simple question, like getting all the Manys' in a relationship have to be so complicated?

Ok, how about  a simpler question, why isn't ExtentFunc documented in the InterSystems Documentation?

The people who create the documentation, listen up!! Or maybe not, your documentation is too complicated and cumbersome as it is.

what is ExtentFunc?

For each persistent class there is an Extent class query that returns IDs.

For Sample.Employee class it is:

SELECT ID, Name, SSN, Home_City, Home_State FROM Sample.Employee

For each class query, <QueryName>Func  method gets generated.

You can see it in the class int code using  Show Other View (Open Sample.Employee and press Ctrl+Shift+V).

Here's the generated <QueryName>Func  method for the Extent query of the Sample.Employee class:

zExtentFunc() public {
    try {
        set tSchemaPath = ##class(%SQL.Statement).%ClassPath($classname())
            set tStatement = ##class(%SQL.Statement).%New(,tSchemaPath)
            do tStatement.prepare(" SELECT ID , Name , SSN , Home_City , Home_State FROM Sample . Employee")
        set tResult = tStatement.%Execute()
    catch tException { if '$Isobject($Get(tResult)) { set tResult = ##class(%SQL.StatementResult).%New() } set tResult.%SQLCODE=tException.AsSQLCODE(),tResult.%Message=tException.AsSQLMessage() }
    Quit tResult }

It executes the query and returns result set. More on class queries.


class %sqlcq.SAMPLES.cls9

To see the code:

  • Go to General SQL settings in SMP and set Cached Query - Save Source to Yes.
  • Purge cached queries from sample namespace.
  • Execute this query again.
  • Check the new query class name- probably  %sqlcq.SAMPLES.cls1
  • It now could be seen in studio