Deep Upsert / update with NodeJS cds 3.16.3

[former_member630950]

I'm trying to do a deep upsert using CDS 3.16.3 on an Entity A with a one-to-one relationship to another Entity B.

I've tried many different calls using Postman, but all without success.

After reading changelogs, patch notes and documentation, I've come to believe that the following should work and create a new Entity of type B directly linked to A, or update an existing entity of type B linked to A.

PATCH /EntityA(id)

Body:

{
  "field": "value",
  "navigationPropertyToB": {
    "field_b": "another-value"
  }
}

The server returns http 500 error and the log displays:

"invalid column name: FIELD_B_VAL".

Using OData-Version: 4.01 as an additional header does not make a difference.

[pierre_dominique2]

Hi Bastiaan,

What does your data model look like and what is the type of the association? I believe it has to be a composition for deep insert/update/delete to work the way you describe :

entity Orders {
  Items : Composition of many OrderItems on Items.order = $self;
}
entity OrderItems {
  key order : Association to Orders;
  product : ...; 
  quantity : ...;
}

Cheers,

Pierre

[former_member630950]

We're using Composition as well.

Our model looks like this:

entity Orders {
  Address : Composition of one Addresses on Address.order = $self;
}
entity Addresses {
  key ID : UUID;
  order : Association to one Orders;
  field1 : ...; 
}

[david_kunz2 · Accepted solution]

Hi,

We fixed the bug with the next release of @sap/cds (>3.17.4).
Sorry for the inconvenience.

Best regards
David

[david_kunz2 · Accepted solution]

Typo: > 3.18.0

[priyankaG · Accepted solution]

Hi David, I am getting this issue of column no found, when i am trying UPSERT, Is there any fix for this??

[david_kunz2]

Hi,

I can reproduce this error, I'm very sorry for this inconvenience. We will fix this bug as soon as possible!

Thanks a lot and best regards
David Kunz

[david_kunz2]

Hi,

An on-condition is not necessary when you have a composition to one. Can you try the following:

entity Orders {
  Address : Composition of one Addresses;
}
entity Addresses {
  keyID: UUID;
  field1 :...;
}

with the request PATCH /Orders(id)

{
  "Adress": {
    "field1": "another-value"
  }
}

Best regards

David

[vfweiss]

Just tried this, but running into a bit of an odd error:

Cannot build UPDATE().set() statement. Invalid data provided: {"Address":{"field1":"Test","ID":"48df0b50-9272-407f-8e16-9853920be1d3"}}

And if I add logging for the Query:

[2019-10-01T07:51:00.524Z | ERROR | 1633715]: sql syntax error: incorrect syntax near ")": line 1 col 108 (at pos 108)

[ramana_saps4hana43]

Hi David,

I'm trying post Deep Insert using an unbound action with array in parameter. When I try to INSERT.into(<TableName>, [Array of Data]) then CDS framework giving an error "Invalid Column Name Items".

my entity definitions are

entity Orders: cuid, managed {

orderDate: Date;

orderType: String(4);

Items: Composition of many OrderItems on Items.order = $self;

};

entity OrderItems: cuid, managed {

order: Association to Orders;

quantity: Decimal(13, 3);

unit: String(3);

price: Decimal(13,2);

currency: String(3);

};

when I try to Post Orders by calling unbound action with Array IN parameter as below

[

{

"orderDate": "2023-04-21",

"orderType": "OR",

"Items": [

{

"quantity": 10,

"unit": "KG",

"price": 1000,

"currency": "USD"

}

]

}

]

The above payload is giving an error during INSERT.into() statement as "Invalid column name Items". Kindly guide me to solve the above error.

Regards,

Ramana.

[david_kunz2]

Hi ramana_saps4hana43 ,

Could you try await INSERT.into(TABLE).entries([...])?

Thanks and best regards,
David