Insert on duplicate key upd when inserting a record?

[Vasiliy-Makogon]

Hi! This POST-request create row in many-to-many table:

{
  "data": {
    "type": "legal-entity-warehouse-days",
    "attributes": {
    },
    "relationships": {
      "legal-entity-warehouse": {
        "data": {
          "id": "8",
          "type": "legal-entity-warehouses"
        }
      },
      "warehouse-day": {
        "data": {
          "id": "1",
          "type": "warehouse-days"
        }
      }
    }
  }
}

In table legal-entity-warehouse-days has unique key (legal_entity_warehouse_id - warehouse_day_id).

If you send a post request several times, there will be an error duplicating a unique composite key.

How to fix it?

[lindyhopchris]

Sorry, but I'm unclear on what the scenario is here. Can you provide more information - namely the Model relationships and the JSON:API Schema relationships?

[ben221199]

I think he has 3 objects:

• Warehouse
• Days
• Warehouse Day

I think he wants to connect some known days to some known warehouses using a many-to-many relation. Warehouse day is the pivot in here. As far as I know, I think he has the problem that the id is not unique when making a new Warehouse day as many-to-many relation.

[Vasiliy-Makogon]

I think he has 3 objects:

• Warehouse
• Days
• Warehouse Day

I think he wants to connect some known days to some known warehouses using a many-to-many relation. Warehouse day is the pivot in here. As far as I know, I think he has the problem that the id is not unique when making a new Warehouse day as many-to-many relation.

Yes, you said exactly right about the presence of these three entities: LegalEntityWarehouse // warehouses WarehouseDay // days 1-7 LegalEntityWarehouseDay // pivot

Table LegalEntityWarehouseDay has a composite unique key for fields LegalEntityWarehouseID and WarehouseDayID. When inserting values that are already present in the database, a duplicate error occurs.

[lindyhopchris]

Ok - you'd need to use validation to reject the request. I.e your validation needs to detect that the given combination of warehouse plus day already exists. The JSON:API package cannot do that itself, because it does not know that those two relationships on legal-entity-warehouse-days are a composite unique key.

I.e. it is your business logic that says that that combination must not already exist - therefore your validation needs to pick up on it.

[ben221199]

I don't know, but isn't it an idea to make some helper functions to make validation of this kind a bit easier?

[lindyhopchris]

Hmmm... I think maybe this fits into the whole topic of "many-to-many", which is causing people a lot of issues about how best to implement that. I need to sit down and actually work out what the "official" way of doing it is, then write some docs and implement any changes that are needed.

It just hasn't bubbled to the top of my priorities yet as I haven't stumbled over the issue myself in any of my production apps. Though recognise that based on the number of questions I get about it, I probably need to sort this out relatively soon.