Issue with one-to-many relation with foreign keys to parent table

[foboni]

PGSync version: 2.2.1

Postgres version: 14.2

Elasticsearch version: 7.16.2

I have got similar use case like the one explained in #63

Tables:

CREATE TABLE zone
(
    id text NOT NULL PRIMARY KEY,
    place_name text
);

CREATE TABLE zone_relation_parent
(
    zone_id text  NOT NULL REFERENCES zone (id),
    zone_id_relation text NOT NULL REFERENCES zone (id)
);

INSERT INTO zone VALUES ('1', 'abc'), ('2', 'bcd'), ('3', 'cde');

INSERT INTO zone_relation_parent VALUES ('1', '2'), ('1', '3'), ('2', '3');

Schema:

[
    {
        "database": "mydb",
        "index": "zones",
        "nodes": {
            "table": "zone",
            "columns": [
                "id",
                "place_name"
            ],
            "children": [
                {
                    "table": "zone",
                    "columns": [
                        "id",
                        "place_name"
                    ],
                    "label": "parents",
                    "relationship": {
                        "variant": "object",
                        "type": "one_to_many",
                        "through_tables": [
                            "zone_relation_parent"
                        ]
                    }
                }
            ]
        }
    }
]

But when I use, pgsync it is not able to replicate the one-to-many mapping

Elasticsearch

{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 3,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "zones",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 1.0,
        "_source" : {
          "id" : "1",
          "parents" : [
            null
          ],
          "place_name" : "abc",
          "_meta" : { }
        }
      },
      {
        "_index" : "zones",
        "_type" : "_doc",
        "_id" : "2",
        "_score" : 1.0,
        "_source" : {
          "id" : "2",
          "parents" : [
            null
          ],
          "place_name" : "bcd",
          "_meta" : { }
        }
      },
      {
        "_index" : "zones",
        "_type" : "_doc",
        "_id" : "3",
        "_score" : 1.0,
        "_source" : {
          "id" : "3",
          "parents" : [
            null
          ],
          "place_name" : "cde",
          "_meta" : { }
        }
      }
    ]
  }
}

For _id "1"

I'm expecting it to populate parents like this,

[{"id": 2, "place_name": "bcd"}, {"id": 3, "place_name": "cde"}]

and for _id "2"

[{"id": 3, "place_name": "cde"}]

[toluaina]

I don't understand the hierarchy here. You've got 2 foreign keys in your through table. How would you expect this to be translated in SQL?

The way through tables works is this:

Parent -> Through -> Child

where Through table contains a set of foreign keys for Parent or Child e.g parent_id or child_id

[foboni]

The way through tables works is this:

Parent -> Through -> Child

Instead of this, I'm trying to link to parent table Parent > Through > Parent

Expected Output:

{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 3,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "zones",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 1.0,
        "_source" : {
          "id" : "1",
          "parents" : [{"id": 2, "place_name": "bcd"}, {"id": 3, "place_name": "cde"}],
          "place_name" : "abc",
          "_meta" : { }
        }
      },
      {
        "_index" : "zones",
        "_type" : "_doc",
        "_id" : "2",
        "_score" : 1.0,
        "_source" : {
          "id" : "2",
          "parents" : [{"id": 3, "place_name": "cde"}],
          "place_name" : "bcd",
          "_meta" : { }
        }
      },
      {
        "_index" : "zones",
        "_type" : "_doc",
        "_id" : "3",
        "_score" : 1.0,
        "_source" : {
          "id" : "3",
          "parents" : [],
          "place_name" : "cde",
          "_meta" : { }
        }
      }
    ]
  }
}

This is similar to the relationship defined in #63

[toluaina]

So your Through table contains a foreign key which is either zone_id or zone_id_relation. Foreign keys are assumed to be compound i.e Through.column_1 and Through.column_2 etc.. I don't think you would be able to build a query that represents this type of relationship