toluaina
parent/child and self-referenced structure
PGSync version: 1.1.28
Postgres version: 13.1
Elasticsearch version: 7.10.1
Redis version: 6.2.1
Python version: 3.7
Problem Description:
Hi. I'm currently trying to work with pgsync and I'm facing two problems and I haven't found a way to solve them. Could you, please, tell me if it is even possible to do the following?
-
I'm trying to define a schema that would map the parent/child association, where parent can have multiple children, child can have multiple children, and so on ... I have database table like this:
Is there a way to create schema, which would generate something like this:
-
I'm trying to define a schema that would map self-referenced association, where item can have array of alternative items I have a database like this:
Is there a way to create schema, which would generate something like this:
Yes you can do 1 and 2.
For 1) you can have a look at the books example here
For 2) you just need to define a relationship of type one_to_many
I can't get 1. scenario (parent/child) working. I have this schema and this DB table:
,
but it will create this documents in elasticsearch:
- scenario is resolved by foreign_key property
I'll be honest self-referential relationships have not been fully explored. Let alone self-referential user-defined foreign key relationships.
Are you sure this shouldn't be
"foreign_key": {
"child": ["id"],
"parent": ["parent_id"]
}
In any case, I feel a self-referencing relationship should be handled as a special case. The complexity for unbounded queries will need to be resolved with CTE's
Yes, I tried both
"foreign_key": {
"child": ["id"],
"parent": ["parent_id"]
}
and
"foreign_key": {
"child": ["parent_id"],
"parent": ["id"]
}
and neither worked as expected.
Yes, recursive CTE is probably the only way to do it. If I could be helpful in any way, just let me know
