Foreign Key using non-id field

#1

I have two types that are related, but the data that I need to link are not by the typical parent.id <=> child.parent field.

entity type CompressorUnit schema name 'COMP_UNIT' {
  
  SAPInventoryNo : string

  SAP_FLOC : string
  components : [CompressorUnitComponent] (parent,SAP_FLOC)
}

entity type CompressorUnitComponent schema name 'COMPONENT' {
  id : string
  parent    : CompressorUnit
  city : string

}

/* in the above, I believe the notation (parent,SAP_FLOC) means *components* field links :

CompressorUnitComponent.parent field as foreign key to Component.SAP_FLOC  as primary key

*/

I am able to traverse the parent type and link to fields in the child type:

c3Grid(CompressorUnit.fetch({include: 'this, components.city'}))

But I am unable to traverse from child type to parent. That is:

c3Grid(CompressorUnitComponent.fetch({include: 'this, parent.SAPInventoryNo'}))

When I look into the result, I see an what looks like an error message, referenceInvalid: true:

{
  "type": "CompressorUnit",
  "id": "000001-SUPPXX",
  "meta": {
    "referenceInvalid": true
  }
}

I think this must be something simple to fix. What am I missing here?

Thanks

Paul

0 Likes

#2

The line

components : [CompressorUnitComponent] (parent,SAP_FLOC)

is successfully doing a join on all CompressorUnitComponents whose parent.id == SAP_FLOC, and the fetch works from parent down to children.

The line

parent    : CompressorUnit

implies that in your transform, you’re assigning the parent for this component. Given that the first fetch works, this means you’re assigning the parent’s id field on transform. But of course, unless the unit’s id and SAP_FLOC values are the same, no parent with that id will exist!

If the SAP_FLOC value is a unique identifier (one of several unique identifiers the Unit will have, and not the main one), I’d suggest trying:

@db(unique=['SAP_FLOC'])
entity type CompressorUnit schema name 'COMP_UNIT' {
  ...
  components : [CompressorUnitComponent] (parent.SAP_FLOC, SAP_FLOC)
...

and on transform:

type TransformCanonicalXYZToCompressorUnitComponent mixes CompressorUnitComponent transforms CanonicalXYZ {
  ...
  parent: ~ expression "{SAP_FLOC: Parent_SAP_FLOC_Code}"
  ...
}
0 Likes

#3

We had a similar issue for one of the customers: https://c3energy.atlassian.net/browse/PLAT-8177

Quoting @trothwein:

After further discussion we came up with a solution involving stored calcs. Use the following for your ServicePointMeterAsset definition: 

remix type ServicePointMeterAsset { 

// changing the reference from id to sdpgId (a field in GridAsset) 

secondaryKey: string 
lookupAsset: MeterAsset(sdpgId, secondaryKey) 

to: ~ stored calc "lookupAsset" 
} 

and add the unique index as earlier described to MeterAsset.sdpgId.

What we added enabled us to load the data without knowing that id (by setting the new secondaryId field) and having the reference to the real id set up automatically.
0 Likes