Rule Application Patterns, Part II: Collection Aggregation – The Sum of Counts
About Rule Application Patterns
This blog post is the second in a series which highlight common rule authoring patterns of note. The InRule ROAD Team (Rule Oriented Application Design) has compiled a wide selection of useful rule authoring patterns which we intend to share through these blogs.
The Pattern: Nesting an Aggregate Function Inside an Aggregate Function
Our second rule authoring pattern is an intermediate-level pattern with the not-uncommon goal of obtaining the number of grandchildren collection members that match a filter. While irAuthor’s collection functions are easy to use for any collection off of a parent entity, interrogating grandchildren from this parent context (where both the child and the grandchild are collections) involves a trick.
That trick is nested collection functions. But there are some things you need to know about nesting irAuthor’s collection functions:
- It is generally easier to create these nested collection functions in syntax and then convert them to business language once they’re complete
- A Count() function cannot be nested within another Count() function
- A Sum() function, however, can be nested within another Sum() function
A quick pattern summary is below.
Step 1: Create Your Schema with Collections of Children and Grandchildren
The first step in our design is to set up three entities: ParentyEntity, ChildEntity, and GrandchildEntity. Then under the ParentEntity, add a collection of ChildEntity called Children.
Under the ChildEntity, add a collection of GrandChildEntity called Grandchildren. See the picture below for a reference.
Step 2: Create the Calculated Field under the Child Entity
Create one calculated field under the ChildEntity called NumberofGrandChildren. Set the value of this calculated field to Count(GrandChildren). Using a function to evaluate collection members from that collection’s parent context—as we’re doing here—is easy.
Step 3: Create a Calculated Field under the Parent Entity
Create one calculated field under the ParentEntity called NumberofGrandChildren. Because we are now two levels above the GrandChildEntity, if we wish to interrogate that grandchild collection from the parent context, we need to nest those two functions. But we cannot nest two Count() functions, so we combine the Sum() function with an inner Count() function to achieve our intended result:
And in business language:
And We’re Done!
So the lesson here is: interrogating collections inside of collections in InRule requires the use of two or more nested collection functions.
Also Worth Knowing
- This nested collection technique would not be necessary if the children or the grandchildren collections were instead an entity reference (a one-to-one relationship). Entity child and grandchild relationships would only require standard notation to reference those fields (ex: ChildEntity.GrandchildEntity.Gender)
- Filters can be added to these nested collection functions to interrogate specific fields on those grandchildren. For example: Sum(Children, Count(Grandchildren, Age >= 17)).
Feel free to drop me a line with questions. Happy rule authoring.