Stored calc on own type doesn't work

If we have a type, say Employee, which has a reference to another Employee which is their boss.
The top level is a VP which doesn’t have a boss, instead the boss field is a reference to themselves. Because this hierarchy can be long we would like to also store an Employees VP so we don’t need to manually traverse the hierachy to the top every time we query. We created the following type:

entity type Employee schema name "EMPL" {
  id: string
  name: string
  boss: Employee
  vp: Employee stored calc "exists(boss) ? boss.vp : {id:id}"
}

The idea being that when you refresh calc fields after the employee chain invalidates multiple times it will eventually point to his or her VP. The problem is that the stored calc doesn’t even calculate. The field is always null. If the invalidation chain didn’t work I would understand most not working but any employee without a boss should get calculated correct?

The only other way I can think of to handle this is to create a BatchJob to calculate but I would prefer it to work in a more automated way during data loads.

For an example of data to recreate the problem:

Emp.createBatch([{
    id: 'vp',
    name: 'vp'
}, {
    id: 'empA',
    name: 'empA',
    boss: 'vp'
}, {
    id: 'empB',
    name: 'empB',
    boss: 'empA'
}])

Because this hierarchy can be long we would like to also store an Employees VP so we don’t need to manually traverse the hierarchy to the top every time we query

Not sure what stored calc problem is, but this requirement can be easily achieved through Hierarchy denormalization feature.

Specifically, you can create a vp field on Employee like

entity type EmployeeHierarchyDenorm mixin HierarchyDenorm<Employee> schema name

entity type Employee {
  /**
   * All hierarchy denorm data that represent boss for this employee
   */
  denormBosses: [EmployeeHierarchyDenorm](to)
  
  /**
   * The top level boss, aka, VP.
   */
  vp : Employee stored calc "denormBosses.(isRoot=='true').from[0]"
}

You can find out hierarchy denorm documentation at <your-env>/api/1/<tenant>/<tag>/documentation/topic/hierarchy-denorm

1 Like

Thank you @chenliu9!

The documentation is great but doesn’t have an example for a strict hierarchy which is what I have. Ideally I can use it to improve performance but I’m running into the following error when running EmpHierarchyDenorm.denormalizeHierarchy():

"Error: Field 'parent' not found for type Emp
    at new C3.client.ActionError (https://3m-dev2-rm2-black.c3iot.ai/typesys/1/all.js?env=browser&compat:1130:13)
    at Object.request (https://3m-dev2-rm2-black.c3iot.ai/typesys/1/all.js?env=browser&compat:882:15)
    at Object.call (https://3m-dev2-rm2-black.c3iot.ai/typesys/1/all.js?env=browser&compat:567:27)
    at c3Call (https://3m-dev2-rm2-black.c3iot.ai/typesys/1/all.js?env=browser&compat:100:20)
    at Object._call (https://3m-dev2-rm2-black.c3iot.ai/typesys/1/all.js?env=browser&compat:2764:20)
    at Object.eval (eval at get (https://3m-dev2-rm2-black.c3iot.ai/typesys/1/all.js?env=browser&compat:3177:20), <anonymous>:5:15)
    at <anonymous>:1:20"

Note: Because Employee already exists I am using Emp

I’m assuming this is something to do with enabling strict because if I turn that off it works as expected. I can’t find anything in the documentation about needing a parent field while in strict mode.

Please show us your type definitions, it looks like you’re missing a declaration somewhere.

Emp.c3typ:

@denorm(target = TypeRef(typeName = "EmpHierarchyDenorm"))
entity type Emp schema name "emp2" {
  id: string
  name: string
  denormBosses: [EmpHierarchyDenorm](from)
  vp: Emp stored calc "denormBosses.(distance == max(denormBosses.distance)).to"
}

EmpHierarchyDenorm.c3typ

@denorm(
    // strictHierarchy=true,
    sources=[
        DenormSource(source = TypeRef(typeName="EmpRelation"), parentFieldName = "denormRelationTo", childFieldName = "denormRelationFrom")
    ]
)
entity type EmpHierarchyDenorm mixin HierarchyDenorm<Emp> schema name "emp_denorm"

EmpRelation.c3typ:

entity type EmpRelation mixes Relation<Emp> schema name 'EMP_REL'

The above code provisions and works as intended. If I uncomment the strict part then it will provision but fails with my previous linked error when attempting to denormHierarchy.

As an update, I did not read the documentation well enough. Here is the docs for Ann.Denorm.strictHierarchy:

false : The source contains first level denormed data. Each node can contain multiple parents (More like a graph) true : The source contains a "parent" field forming a strict hierarchy. Each node can have exactly 1 parent. (More like a tree)

In my example, Emp will need a parent field

Can you post the code once you get it working? This will be a useful example for us to add to documentation

It should be denormBosses: [EmpHierarchyDenorm](to) since in EmpHierarchyDenorm.from represents boss, and EmpHierarchyDenorm.to represents employee.

So if you want to get all EmpHierarchyDenorm boss data for an employee, the logic should be EmpHierarchyDenorm.to.id == employee.id

@rileysiebel Here is an example using strictHierarchy:

Emp.c3typ:

@denorm(target = TypeRef(typeName = "EmpHierarchyDenorm"), strictHierarchy=true)
entity type Emp schema name "emp2" {
  id: string
  name: string
  denormBosses: [EmpHierarchyDenorm](to)
  parent: Emp
}

EmpHierarchyDenorm.c3typ:

@denorm(
    strictHierarchy=true,
    sources=[
        DenormSource(source = TypeRef(typeName="Emp"), parentFieldName: "parent")
    ]
)
entity type EmpHierarchyDenorm mixin HierarchyDenorm<Emp> schema name "emp_denorm"

EmpRelation is not needed for strict.

@chenliu9 Thanks for the change. I have updated it.

Edit: Solved my own problem as I didn’t look hard enough. Use == instead and remove the array portion. I.e. denormBosses.(isRoot=='true').from

When I use denormBosses.(isRoot='true').from[0] in an evaluate I get the following error:

Emp.evaluate({
    projection: 'denormBosses.(isRoot="true").from[0]',
	filter: "id == 'C'"
})

"Error: wrapped RuntimeException: Internat error: unexpected ExprNode class c3.love.expr.ast.AssignmentExpr
    at new C3.client.ActionError (https://3m-dev2-rm2-black.c3iot.ai/typesys/1/all.js?env=browser&compat:1130:13)
    at Object.request (https://3m-dev2-rm2-black.c3iot.ai/typesys/1/all.js?env=browser&compat:882:15)
    at Object.call (https://3m-dev2-rm2-black.c3iot.ai/typesys/1/all.js?env=browser&compat:567:27)
    at c3Call (https://3m-dev2-rm2-black.c3iot.ai/typesys/1/all.js?env=browser&compat:100:20)
    at Object._call (https://3m-dev2-rm2-black.c3iot.ai/typesys/1/all.js?env=browser&compat:2764:20)
    at Object.eval (eval at get (https://3m-dev2-rm2-black.c3iot.ai/typesys/1/all.js?env=browser&compat:3177:20), <anonymous>:5:15)
    at <anonymous>:1:5"

I can use the following to get the “VP”:

Emp.evaluate({
    projection: 'denormBosses.(distance == max(denormBosses.distance)).from',
    filter: "id == 'C'"
})

I don’t believe this is the best calc field as it doesn’t seem to be very well optimized. Do you have a better option? One option is to sort the denormBosses using the @db annotation and just get the top value but not sure if there is a better route.

1 Like