Resolving relationships

[arvindkhadri]

PGSync version:
1.0.1
Postgres version:
11.6
Elasticsearch version:
7.6.1
Redis version:

Python version:
3.7
Problem Description:
I have defined a schema which is being consumed by pgsync, but there is a field which returns null in al cases.
Here is the schema.json

[{
    "index": "application_db",
    "nodes": [{
        "table": "product",
        "columns": ["name", "hsn", "created_date"],
        "children": [{
            "table": "product_variant",
            "columns": ["description", "quantity", "quantity_multiplier"],
            "relationship": {
                "variant": "object",
                "type": "one_to_many"
            },
            "children": [{
                "table": "code_unit",
                "columns": ["unit_name"],
                "relationship": {
                    "variant": "object",
                    "type": "one_to_one"
                }
            },
                         {
                             "table": "stock",
                             "columns": ["mrp", "selling_price"],
                             "relationship": {
                                 "variant": "object",
                                 "type": "one_to_one"
                             }
                         }, {
                             "table": "product_variant_images",
                             "columns": ["image_url"],
                             "relationship": {
                                 "variant": "object",
                                 "type": "one_to_one"
                             }
                         }]
        }, {
            "table": "category",
            "columns": ["name", "type"],
            "relationship" : {
                "variant": "object",
                "type": "one_to_one"
            }
        }]
    }]
}]

category always returns null. Also the table product has two columns which have relationship with category table.
Please advice.

[toluaina]

Can you please post your database schema here. Maybe just the product and category table definition.

[arvindkhadri]

Sharing the SQL statements that were used to create the tables.

CREATE TABLE public.product (
    id bigint NOT NULL,
    name public.citext NOT NULL,
    created_date timestamp with time zone DEFAULT now() NOT NULL,
    category_id bigint NOT NULL,
    brand_id bigint,
    hsn bigint DEFAULT 0 NOT NULL,
    active boolean DEFAULT true NOT NULL,
    description text
);

ALTER TABLE ONLY public.product
    ADD CONSTRAINT product_brand_id_fkey FOREIGN KEY (brand_id) REFERENCES public.category(id) ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE ONLY public.product
    ADD CONSTRAINT product_category_id_fkey FOREIGN KEY (category_id) REFERENCES public.category(id) ON UPDATE RESTRICT ON DELETE RESTRICT;

CREATE TABLE public.category (
    id bigint NOT NULL,
    name public.citext NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    parent_id bigint,
    image_url text,
    type text DEFAULT 'CATEGORY'::text NOT NULL,
    description text
);

[toluaina]

I think your category table is missing a unique constraint. I don't want to assume here.
Can you provide the schema so I can recreate the tables? Possibly the entire schema
Your existing schema assuming it has a unique constraint works ok.

[arvindkhadri]

I have an unique constraint, rather two on that table.

ALTER TABLE ONLY public.category
    ADD CONSTRAINT category_name_key UNIQUE (name);

ALTER TABLE ONLY public.category
    ADD CONSTRAINT category_name_type_key UNIQUE (name, type);


ALTER TABLE ONLY public.category
    ADD CONSTRAINT category_pkey PRIMARY KEY (id);

Sorry that these were missed.

[toluaina]

Can you send the full database schema?
I'm simply unable to reproduce your scenario. The category is not null when I do this so maybe I'm still missing something in your full schema.

pg_dump -d <dbname> -s

[arvindkhadri]

https://gist.github.com/arvindkhadri/e3afec66793e6a74ad00c463ca25cadd That has the complete schema.

[toluaina]

So I've created an s.sql from this db schema

psql -d mydb < s.sql

added some records

INSERT INTO category (id, name, parent_id, image_url, type, description) VALUES (1, 'cat_1', 1, 'http://foo1.bar', 'type_x', 'description 1');
INSERT INTO category (id, name, parent_id, image_url, type, description) VALUES (2, 'cat_2', 2, 'http://foo2.bar', 'type_y', 'description 2');
INSERT INTO category (id, name, parent_id, image_url, type, description) VALUES (3, 'cat_3', 3, 'http://foo3.bar', 'type_z', 'description 3');

INSERT INTO product (id, name, category_id, brand_id, hsn, active, description) VALUES (1, 'product_1', 1, 1, 123, true, 'product description 1');
INSERT INTO product (id, name, category_id, brand_id, hsn, active, description) VALUES (2, 'product_2', 2, 2, 456, false, 'product description 2');
INSERT INTO product (id, name, category_id, brand_id, hsn, active, description) VALUES (3, 'product_3', 3, 3, 789, true, 'product description 3');

pgsync -c s.json

and I get this doc data
are you sure there are related category data for those products in your database?

[toluaina]

Closing as no reply on this.