AllAccepted answers
Eduard Lebedyuk · Jul 16, 2017

It depends on a queries you want to run. For example you want to get all parents' ids which have a child with a specific property value. In a child class you can index for one or several properties, and execute this query:

SELECT parentId
FROM child
WHERE propertyA = 'value'

Documentation on indexes.

Or you can define a computed property in your parent class and index that.

Eduard Lebedyuk · Jul 14, 2017

When you define a query:

Query MyQuery() As %Query
{
  QUERY TEXT
}

Or

Query MyQuery() As %SQLQuery
{
  QUERY TEXT
}

That means that this query implements an interface provided by %Library.Query or %Library.SQLQuery respectively. They define method generators for Fetch/Execute/Func methods (more on them). Each method of the interface class gets called during compilation

You can subclass %Library.Query or %Library.SQLQuery to define your own methods. Here's custom query interface that does 2 things:

  • Changes ROWSPEC to Id123:%String
    • It can be seen in class definition after compilation
    • <QueryName>GetInfo and <QueryName>GetODBCInfo methods also reflect new ROWSPEC properly
  • Generates <QueryName>GetText method that returns query text
Class Utils.MyQuery Extends %SQLQuery
{

/// This method would be ran before others and changes ROWSPEC to "Id123:%String"
ClassMethod %ChangeRowspec() [ CodeMode = objectgenerator, ServerOnly = 1 ]
{
    // quit if we're not compiling a query
    if %mode="method" quit $$$OK
    
    set class = %class.Name
    set query = %compiledmethod.parent.Name        
    set rowspec = "Id123:%String"
    
    // Modify query definition
    $$$defSubMemberSet(class,$$$cCLASSquery,query,$$$cQUERYparameter,"ROWSPEC",rowspec)
    
    // Modify compiled query definition
    $$$comSubMemberSet(class,$$$cCLASSquery,query,$$$cQUERYparameter,"ROWSPEC",rowspec)
    
    // Update compile-time parameter value
    set %parameter("ROWSPEC") = rowspec
    
    // Update class definition
    do UpdClsDef^%occLibrary(class)
    
    quit $$$OK
}

/// GetText is a method that is used to get query text as a %String
ClassMethod GetText() As %String [ CodeMode = objectgenerator, ServerOnly = 1 ]
{
    if %mode="method" quit $$$OK
    $$$comMemberKeyGetLvar(query,%classname,$$$cCLASSquery,%property,$$$cQUERYsqlquery)
    do %code.WriteLine("    quit " _ $$$quote(query))
    quit $$$OK
}

}

And here's a test class with our new query:

Class Utils.MyQueryTest
{
Query ABC() As Utils.MyQuery
{
SELECT 1
}
}

After Utils.MyQueryTest is compiled it looks like this:

Class Utils.MyQueryTest
{
Query ABC() As Utils.MyQuery(ROWSPEC = "Id123:%String")
{
SELECT 1
}
}

You can modify this interface to get any behavior you want.

Code is available on GitHub.

Eduard Lebedyuk · Jul 12, 2017

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
Eduard Lebedyuk · Jul 11, 2017

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)),! }