New post
Question
· Mar 25, 2019

Querying many-to-many relationship objects , dynamic arrays and JSON

#Databases #JSON #SQL #Caché

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.

Discussion (7)0
Log in or sign up to continue
Eduard Lebedyuk · Mar 25, 2019

The easiest way is to use JSON_OBJECT for everything. Fastest would be just writing to device from resultset. Combine these approaches to get the performance you need (add this code to Parent class and call Test method):


Query Display() As %SQLQuery
{
SELECT
    JSON_OBJECT(
        'ID':%ID,
        'Name': Name,
        'Description': Description,
        'Children':    Example.Parent_GetChildrenInfo(%ID)
    )
FROM Example.Parent
}

/// do ##class(Example.Parent).Test()
ClassMethod Test(count = 0)
{
    if count>0 {
        do ##class(Example.Child).%KillExtent()
        do ..%KillExtent()
        
        
        do ##class(Example.Child).Populate(count,,,,$$$NO)
        do ..Populate(count,,,,$$$NO)
    }
    
    do ..DisplayFunc().%Display()
}

ClassMethod GetChildrenInfo(id) As %String [ SqlProc ]
{
    #define CRLF                             $c(13,10)
    #define ZENJSISNUM(%val)                ($IsValidNum(%val)&&(%val=+(%val)))
    #define ZENJSNUM(%num)                    $fnumber(%num,"LON")
    #; JSON utility macros that use the JSON translation table instead of the JS translation table
    #define ZENJSTABLE(%format)                ("JS"_$S(%format["u":"ML",1:""))
    #define ZENJSONTABLE(%format)            ("JSON"_$S((%format["u"):"ML",1:""))
    #define ZENJSONESCAPE(%str,%format)        $S(%format["s":$ZCVT(%str,"O",$$$ZENJSONTABLE(%format)),1:$Replace($ZCVT(%str,"O",$$$ZENJSTABLE(%format)),"\'","'"))
    #define ZENJSONSTR(%str,%format)        (""""_$$$ZENJSONESCAPE(%str,%format)_"""")
    #define ZENJSONPROP(%prop,%format)        $$$ZENJSONSTR(%prop,%format)
    #define ZENJSONVALUE(%val,%format)        $S($$$ZENJSISNUM(%val):$$$ZENJSNUM(%val),$C(0)=(%val)||$ListValid(%val):"""""",1:$$$ZENJSONSTR(%val,%format))
    #define ZENJSONPAIR(%pr,%val,%format)    $$$ZENJSONPROP(%pr,%format)_":"_$$$ZENJSONVALUE(%val,%format)
    
    set out = "["   
    
    
    set ids = ..ChildrenGetStored(id)
    set ptr=0
    set separator=0
    while $listnext(ids,ptr,value) {
        set value = $lg(value)
        set:separator out = out _ ","

        set out = out _  "{"
        set out = out _  $$$ZENJSONPAIR("Name",##class(Example.Child).NameGetStored(value),"") _","
        set out = out _  $$$ZENJSONPAIR("Description",##class(Example.Child).DescriptionGetStored(value),"")
        set out = out _  "}"
        set separator = 1
    }
    
    set out = out _  "]"
    
    quit out
}
0 0
Brendan Bannon · Apr 23, 2019

Giray

Your code does not match your class descriptions.  In your code you are using a property, Parent to do the child lookup, but there is no parent property defined in Example.Child.

If you truly want a Many to Many then you can't define a Parent property in the child, unless it is a list, and I am not recommending that!

I am not a fan of Lists of Objects, but this might not be too bad.  I think I would still rather go with an array of objects as that would give you a third table and line up with the classic Many to Many format using 3 tables.  If you want to stay with a List then I would suggest that you pass the List of Child IDs into the QueryChildren method.  You can then use the list of Object IDs to get each child via SQL or Objects.

0 0