[regression] many-to-many relationships (FK-only, no or different PK) #2751
-
Environment
Description of issueSince v10.0.0, many-to-many relationships are not discovered properly. Last known working version: v9.0.1 (couldn't test the releases between due to #2410 ). Schema: CREATE ROLE data_admin nologin;
CREATE SCHEMA api;
GRANT USAGE ON SCHEMA api TO data_admin;
CREATE TABLE api.test_a (id text PRIMARY KEY);
CREATE TABLE api.test_b (id text PRIMARY KEY);
CREATE TABLE api.test_mapping (id_a text NOT NULL REFERENCES api.test_a(id), id_b text NOT NULL REFERENCES api.test_b(id));
INSERT INTO api.test_a VALUES ('value for A');
INSERT INTO api.test_b VALUES ('value for B');
INSERT INTO api.test_mapping VALUES ('value for A', 'value for B');
GRANT SELECT ON ALL TABLES IN SCHEMA api TO data_admin; Docker compose service: my-admin-api:
image: "postgrest/postgrest:v10.2.0"
networks:
- local-net
depends_on:
- my-database
environment:
PGRST_DB_URI: "postgresql://test:test@my-database:5432/mydb"
PGRST_DB_SCHEMA: "api,public"
PGRST_DB_EXTRA_SEARCH_PATH: "public"
PGRST_DB_ANON_ROLE: "data_admin"
PGRST_DB_POOL: "5"
PGRST_DB_POOL_TIMEOUT: "15"
PGRST_LOG_LEVEL: "info"
PGRST_SERVER_HOST: "*"
PGRST_SERVER_PORT: "3345" API call: curl -s 'http://<container-ip>:3345/test_a?select=b:test_b(*)' | jq '.' Expected response: [
{
"b": [
{
"id": "value for B"
}
]
}
] Actual response: {
"code": "PGRST200",
"details": "Searched for a foreign key relationship between 'test_a' and 'test_b' in the schema 'api', but no matches were found.",
"hint": "Perhaps you meant 'test_mapping' instead of 'test_b'.",
"message": "Could not find a relationship between 'test_a' and 'test_b' in the schema cache"
} Was this change done intentionally for v10? If so, please close this issue, since the current documentation states the composite key requirement. |
Beta Was this translation helpful? Give feedback.
Replies: 1 comment 2 replies
-
Yes, this change was included in version 10.0.0 as mentioned in the CHANGELOG: Lines 196 to 197 in ce378e6 This means that for a many-to-many relationship to be detected, the intermediate table must contain the foreign keys as primary keys. In your example, adding this constraint will make the embedding work as you expect: alter table api.test_mapping add constraint test_mapping_pk primary key (id_a, id_b); |
Beta Was this translation helpful? Give feedback.
Yes, this change was included in version 10.0.0 as mentioned in the CHANGELOG:
postgrest/CHANGELOG.md
Lines 196 to 197 in ce378e6
This means that for a many-to-many relationship to be detected, the intermediate table must contain the foreign keys as primary keys. In your example, adding this constraint will make the embedding work as you expect: