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.

  • 0
  • 0
  • 219
  • 5
  • 2

Answers

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
}

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.

You can store/access list properties same as array properties(via third table):

Property MyListProp As list Of Some.Class(SQLPROJECTION = "table/column", STORAGEDEFAULT = "list");

Note that you need first to delete class storage if any exists and either recreate or move the data to comply with the new storage.

Comments

what's the expected average/max cardinality on that relationship?

Many to many. One child can have 1 to * parents (no maximum). And a parent can have 0 to * children (no maximum). I cannot say what the average can be.

Also, I've edited my question accordingly, since having a reference to the parent from the child would mean 1 to many. The parent has a list of children now.

I mean how many children parent has on average? 10? 100? 1000?

Difficult to say. Let's assume around 50.