Foreign key field matching multiple conditions?

#1

Is it possible to have a foreign key field matching multiple conditions?

Given:

entity type Foo {
  fooOne: string
  fooTwo: string
}

entity type Bar {
  barOne: string
  barTwo: string
}

I need to define a field barObjs on type Foo such that the following is true:

var fooObj = Foo.fetch({
  include: "fooOne, fooTwo, barObjs"
}).at('objs[0]');

var result = Bar.fetch({
  filter: Filter.eq('barOne', fooObj.fooOne).and.eq('barTwo', fooObj.fooTwo)
}).at('objs');

expect(fooObj.barObjs).toEqual(result); // should pass

I’ve tried the following approaches unsuccessfully:

An fkey field with multiple conditions, but this causes a provisioning error:

entity type Foo {
  ...
  barObjs: [Bar](barOne, fooOne, barTwo, fooTwo) // E mismatched input ',' expecting ')'
}

A stored calc field with the intersection of multiple array fields. There doesn’t seem to be an intersection function, but maybe I’m missing something.

entity type Foo {
  ...
  oneBarObjs: [Bar](barOne, fooOne)
  twoBarObjs: [Bar](barTwo, fooTwo)
  barObjs: [Bar] stored calc 'intersection(oneBarObjs, twoBarObjs)' // Invalid Function call at line 1, column 0 in expression intersection(oneBarObjs, twoBarObjs).
}

Or maybe there’s a way to reference a field from Foo (via “this” or “self”) in an expression dot filter?

entity type Foo {
  ...
  oneBarObjs: [Bar](barOne, fooOne)
  barObjs: [Bar] stored calc 'oneBarObjs.(barTwo == this.fooTwo)'
  // Calc field barObjs in type -type-Foo.Foo references field fooTwo which
  // is not defined in type -type-Bar.Bar or any of its extension types
}

Is there a way I can achieve this functionality using just the type system?

#2

What you are describing here sounds like it is really a composite key. We do have support for composite keys that was added (beta) for a POC. This may work for this situation but I’m not sure as I only tested it for the POC needs. Please add a ticket for me to investigate to see if it can work as is (or with platform changes) and advise.

Other than that, you would need to use a stored calc to define the composite key fields as a concatenation with a delimiter. For example, in type Foo:

fooKey: string stored calc "fooOne + '#' + "entity type Foo {
  fooOne: string
  fooTwo: string

  fooKey: string stored calc "fooOne + '#' + fooTwo"
  barObjs: [Bar](barKey, fooKey)
}

entity type Bar {
  barOne: string
  barTwo: string

  barKey: string stored calc "barOne + '#' + barTwo"
  fooObjs: [Foo](barKey, fooKey)
}
1 Like
#3

Hi,

I am in a similar situation of Matt.
I have a External Type1 which is actually a “look up table” and a External Type2 that represents some objects.
Both of them has composite keys, and in the External Type2 I have all the fields that I need to generate the Type1. It’s basically a simple join on multiple conditions. Is this supported?

Ex.

entity type Type2 {

a: string schema name …
b: string schema name …

}

a+b identifies a single row in Type1. I need the reference of Type1 in Type2.
How do I do?
(All types are external so I cannot create store calc fields).

The thread says the support was in beta and it’s rather old…

Hi

#4

@alessandro.perina Yes it is supported. Here is an example for your basic case:

type Type1Key mixes CompositeKey {
a: string schema name ‘COL_A_IN_T1’
b: string schema name ‘COL_B_IN_T1’
}

entity type Type1 mixes Type1Key, External, NoSystemCols schema name ‘TABLE_1’

entity type Type2 mixes External, NoSystemCols schema name ‘TABLE_2’ {

@schema(fieldOverrides = {a : ‘COL_A_IN_T2’, b2: ‘COL_B_IN_T2’})
type1Key: Type1Key

type1Ref: Type1(id, type1Key)
}

Note the use of the @schema to allow for the columns being named differently between Type1 and Type2.