Composite PK causing status 400

Automations
1

Hey everyone,

I’m having an issue with the Rest API generated by Noco on a table that makes use of a composite primary key as seen in the below image:

Screenshot from 2024-02-09 13-55-11

My request ultimately boils down to:

curl -X 'PATCH' \
  'https://.../api/v2/tables/m4echxrt1m53mxi/records' \
  -H 'accept: application/json' \
  -H 'xc-auth: ...' \
  -H 'Content-Type: application/json' \
  -d '{
  "order_id": 87970,
  "customer_id": 28572,
  "order_status_id": 19
}'

And I want to update the field “order_status_id” from the current value of 5, to the 19 shown in the request here. As far as I’m aware, we’re running MySQL with Noco running in a Docker container.

The response object shows this message:

Undefined binding(s) detected when compiling SELECT. Undefined column(s): [customer_id, order_status_id] query: <... VERY long SQL query here that has a where clause of all fields in the composite key being set to '?'>

Any help would be appreciated as I’m most likely just doing something stupid in this request.

2

There are 2 issues,

  1. You are trying to update one of the primary key and it won’t work since in where clause it going to use "order_status_id" = 19 and not "order_status_id" = 5. So updating primary key value is not supported.
  2. Looks like the error throws when trying to extract the record before/after insert for web-hook which looks like a bug from our side and I will have a look.
3

Okay, no worries at all here. Is there an alternative solution that I might be able to use. Along the lines we are running Redash too, and there is a hacky solution to have write queries there but I would really like to avoid this if possible.

The goal right now is to move our architecture off of any manual SQL queries on the database, and only run through Noco and Redash.

4

You can use our v1 update api and in which you have to pass the ids in path separated by ___, for example http://localhost:8080/api/v1/db/data/noco/baseId/tableId/1___1___1.

I’m looking into v2 api issues as well but you can’t update pk with v2 api. The main reason is comments, audit,… etc are associated with the record id and if you are updating id those will no longer associate with same record.

5

Great to hear that there is a solution available. I’m slowly working my through getting this to work. Thank you for the support.