Querying many-to-many relationship objects , dynamic arrays and JSON
I have two classes:
Class Example.Parent Extends %Persistent { Property Name As %String; Property Description As %String; Property Children as list of Example.Child; }
Class Example.Child Extends %Persistent { Property Name As %String; Property Description As %String; }
A parent can have many children. I want to query for parents and add children for each parent to my result.
Here is the code that I query with:
Class Example.QueryParents [ Abstract ] { ClassMethod QueryParents() As %String { Set query = "SELECT %ID ID, Name, Description FROM Example.Parent" Set statement = ##class(%SQL.Statement).%New() Set sc = statement.%Prepare(query) If ($$$ISOK(sc)) { Set resultSet = statement.%Execute() Set parentArray = ##class(%DynamicArray).%New() Set parentArrayIndex = 0 While (resultSet.%Next()) { Set parent = ##class(%DynamicObject).%New() Set parent = { "Name" : (resultSet.%Get("Name")), "Description" : (resultSet.%Get("Description")), "Children" : (..QueryChildren(resultSet.%Get("ID"))) } Do parentArray.%Set(parentArrayIndex, parent) Set parentArrayIndex = parentArrayIndex + 1 } Do resultSet.%Close() Return parentArray.%ToJSON() } Return "" } ClassMethod QueryChildren(ParentID As %String) As %DynamicArray [ Private ] { Set query = "SELECT Name, Description FROM Example.Child WHERE Parent = '" _ ParentID _ "'" Set statement = ##class(%SQL.Statement).%New() Set sc = statement.%Prepare(query) Set childArray = ##class(%DynamicArray).%New() If ($$$ISOK(sc)) { Set resultSet = statement.%Execute() Set childArrayIndex = 0 While (resultSet.%Next()) { Set child = ##class(%DynamicObject).%New() Set child = { "Name" : (resultSet.%Get("Name")), "Description" : (resultSet.%Get("Description")) } Do childArray.%Set(childArrayIndex, child) Set childArrayIndex = childArrayIndex + 1 } } Return childArray } }
In the QueryParents method, while iterating through the result set, I trigger a new query for each parent to find the children. And at the end I return a JSON structure. Now, this works but it is not going to scale well when there are many records in the database.
Is there a better approach to doing this?
I have version 2017.2 installed.