Problem Description:
I have 2 tables "variants" and "codes," i don't use foreign keys and usually join by 2 primary key columns "mp_account_id", "id" in "variants" and "mp_account_id", "variant_id" in "codes" which are unique constraints. When i try to use user defined foreign keys on just "ids" it works fine, but it joins some unwanted info because ids can be the same but mp_account_ids can be different. So if i do like this:
"foreign_key": {
"child": ["mp_account_id", "variant_id"],
"parent": ["mp_account_id", "id"]
}
it seems that it ignores the order and tries to join by mp_account_id=id and variant_id=mp_account_id which is obviously wrong.
It looks like it is doing it alphabetically placing "id" in front of "mp_account_id"
My schema:
[
{
"database": "my_db",
"index": "my_index",
"nodes": {
"table": "variants",
"schema": "public",
"columns": [
"client_uuid",
"mp_account_id",
"id",
"image",
"name",
"description",
"brand_name"
],
"transform": {
"mapping": {
"client_uuid": {"type": "keyword"},
"mp_account_id": {"type": "integer"},
"id": {"type": "keyword"},
"image": {"type": "keyword"},
"name": {
"type": "text",
"analyzer": "simple"
},
"description": {"type": "text"},
"brand_name": {"type": "text"}
}
},
"children": [
{
"table": "codes",
"schema": "public",
"columns": [
"value"
],
"transform": {
"value": {"type": "text"}
},
"relationship": {
"variant": "scalar",
"type": "one_to_many",
"foreign_key": {
"child": ["mp_account_id", "variant_id"],
"parent": ["mp_account_id", "id"]
}
}
}
]
}
}
]
Error Message (if any):
WHERE mp_product_codes_1.mp_account_id = mp_product_variants_1.id AND mp_product_codes_1.variant_id = mp_product_variants_1.mp_account_id GROUP BY mp_product_codes_1.mp_account_id, mp_product_codes_1.variant_id) AS anon_1 ON anon_1.mp_account_id = mp_product_variants_1.id AND anon_1.variant_id = mp_product_variants_1.mp_account_id
WHERE CAST(CAST(mp_product_variants_1.xmin AS TEXT) AS BIGINT) < %(param_1)s]
PGSync version: 2.5.0
Postgres version: 14
Elasticsearch version: 7.17.10
Redis version: 7.0.11
Python version: 3.10
Problem Description: I have 2 tables "variants" and "codes," i don't use foreign keys and usually join by 2 primary key columns "mp_account_id", "id" in "variants" and "mp_account_id", "variant_id" in "codes" which are unique constraints. When i try to use user defined foreign keys on just "ids" it works fine, but it joins some unwanted info because ids can be the same but mp_account_ids can be different. So if i do like this: "foreign_key": { "child": ["mp_account_id", "variant_id"], "parent": ["mp_account_id", "id"] } it seems that it ignores the order and tries to join by mp_account_id=id and variant_id=mp_account_id which is obviously wrong. It looks like it is doing it alphabetically placing "id" in front of "mp_account_id" My schema: [ { "database": "my_db", "index": "my_index", "nodes": { "table": "variants", "schema": "public", "columns": [ "client_uuid", "mp_account_id", "id", "image", "name", "description", "brand_name" ], "transform": { "mapping": { "client_uuid": {"type": "keyword"}, "mp_account_id": {"type": "integer"}, "id": {"type": "keyword"}, "image": {"type": "keyword"}, "name": { "type": "text", "analyzer": "simple" }, "description": {"type": "text"}, "brand_name": {"type": "text"} } }, "children": [ { "table": "codes", "schema": "public", "columns": [ "value" ], "transform": { "value": {"type": "text"} }, "relationship": { "variant": "scalar", "type": "one_to_many", "foreign_key": { "child": ["mp_account_id", "variant_id"], "parent": ["mp_account_id", "id"] } } } ] } } ]
Error Message (if any):