toluaina
Hierarchical relationship between same table not being reflected correctly
PGSync version: 2.2.1
Postgres version: 10.8
Elasticsearch version: 7.4.2
Redis version: 6.2.3
Python version: 3.7-slim
Problem Description:
I'm having some difficulties getting the data in the format I expect.
The problem I'm having is in the category property. Basically, they have a hierarchy in our Postgres database, which is not replicating correctly to ElasticSearch. The last level is always being saved. In our relational model we have a parent_id column that points to the same table. It was the way we organized the hierarchy of categories. They can have up to three levels, the first being the root level and the third being the leaf level.
I don't know where I'm going wrong in the schema.json configuration.
I will be very grateful if you can help me.
To make the problem easier to understand, I am attaching the following images and examples:
-
ER diagram I'm trying to replicate to ElasticSearch:
-
PGSync schema.json:
[
{
"database": "ecommerce",
"index": "catalog_pgsync",
"nodes": {
"table": "sku",
"columns": [
"id",
"external_id",
"display_name",
"original_price",
"current_price",
"discount_percentage",
"images",
"model",
"active",
"quantity"
],
"children": [
{
"table": "product",
"columns": [
"id",
"best_seller",
"keywords"
],
"relationship": {
"variant": "object",
"type": "one_to_one"
},
"children": [
{
"table": "category",
"label": "category_3",
"columns": [
"id",
"name",
"active",
"level",
"external_id",
"external_parent_id",
"department_id"
],
"relationship": {
"variant": "object",
"type": "one_to_one"
},
"children": [
{
"table": "category",
"label": "category_2",
"columns": [
"id",
"name",
"active",
"level"
],
"relationship": {
"variant": "object",
"type": "one_to_one",
"foreign_key": {
"child": ["id"],
"parent": ["parent_id"]
}
},
"children": [
{
"table": "category",
"label": "category_1",
"columns": [
"id",
"name",
"active",
"level"
],
"relationship": {
"variant": "object",
"type": "one_to_one",
"foreign_key": {
"child": ["id"],
"parent": ["parent_id"]
}
}
}
]
}
]
}
]
}
]
}
}
]
- Example of document being saved in ElasticSearch (part that went wrong)
"category_3": {
"id": "baa0608e-46b4-4275-b03a-689ceb3c547c",
"name": "Jogos Xbox Series",
"level": 3,
"active": true,
"category_2": {
"id": "baa0608e-46b4-4275-b03a-689ceb3c547c",
"name": "Jogos Xbox Series",
"level": 3,
"active": true,
"category_1": {
"id": "baa0608e-46b4-4275-b03a-689ceb3c547c",
"name": "Jogos Xbox Series",
"level": 3,
"active": true
}
}
...
- Category hierarchy that should be saved (in the example above):
If you need any more information, please ask me.
I took a closer look at this. Self referential relationships are not supported. You can break this down into a single table with a foreign key to itself. The only way to build the query is using recursive traversal with CTE's.
I will add this as a TODO for now as it involves a bit of effort.
FYI. Still working on this. There is a bit of effort required here. Will let you know once I have an update
@Lelouch1
Sure, I will let you know once I have an update. I've had a few distractions and commitments to deal with
