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?
Comments
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)),! }Eduard,
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 work with Cache for decades and never knew about ExtentFunc(). There is always something to learn...
Mike, in Studio use Show Other View to display the generated INT code for the Sample. Employee class. You'll find the ExtentFunc() there. You may need to compile the Sample.Employee class first to re-generate the INT code.
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.
Func is documented in release notes, and in a queries interface class. But I do agree that it's fairly difficult to find without prior knowledge of it (I searched for Func keyword).
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