Child and parent relationship not being established correctly

[vickmu]

The problem:

• Child and parent relationship not being established correctly.

My workflow:

1. product(parent table) with feature and related(children)
2. the relationship between the product and feature, is many-to-many
3. the relationship between the product and related, is many-to-many

What I want under the product class:

1. relateds = relationship('Related', secondary='product_related')
2. features = relationship('Feature', secondary='product_featured')

What is happening:

class Product(): 
relateds = relationship('Related', secondary='product_related')
(No features)

class Feature():
(feature should be a child for product)
products = relationship('Product', secondary='product_features')

SQL code for the tables:

-- Table: product
CREATE TABLE product (
    id bigserial  NOT NULL,
    mpn text  NULL,
    title text  NULL,
    price real  NULL,
    msrp real  NULL,
    stock_availability boolean  NULL,
    country_id int8  NULL,
    description text  NULL,
    weight_packaging real  NULL,
    item_type varchar(20)  NULL,
    upc varchar(40)  NULL,
    model varchar(60)  NULL,
    sku varchar(40)  NULL,
    badges text  NULL,
    url text  NULL,
    site_item_id text  NULL,
    brand_id int8  NULL,
    CONSTRAINT product_id PRIMARY KEY (id)
);

CREATE INDEX country_id on product (country_id ASC);

CREATE INDEX product_idx_2 on product (brand_id ASC);


-- Table: related
CREATE TABLE related (
    id bigserial  NOT NULL,
    name text  NOT NULL,
    CONSTRAINT related_pk PRIMARY KEY (id)
);

-- Table: product_related
CREATE TABLE product_related (
    product_id int8  NOT NULL,
    related_id int8  NOT NULL,
    CONSTRAINT product_related_pk PRIMARY KEY (product_id,related_id)
);

CREATE INDEX product_related_idx_1 on product_related (product_id ASC);

CREATE INDEX product_related_idx_2 on product_related (related_id ASC);

-- Reference: product_related_related (table: product_related)
ALTER TABLE product_related ADD CONSTRAINT product_related_related
    FOREIGN KEY (related_id)
    REFERENCES related (id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: product_related_product (table: product_related)
ALTER TABLE product_related ADD CONSTRAINT product_related_product
    FOREIGN KEY (product_id)
    REFERENCES product (id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Table: feature
CREATE TABLE feature (
    id bigserial  NOT NULL,
    name text  NOT NULL,
    CONSTRAINT feature_pk PRIMARY KEY (id)
);

-- Table: product_features
CREATE TABLE product_features (
    product_id int8  NOT NULL,
    feature_id int8  NOT NULL,
    CONSTRAINT product_features_pk PRIMARY KEY (product_id,feature_id)
);

CREATE INDEX product_features_idx_1 on product_features (product_id ASC);

CREATE INDEX product_features_idx_2 on product_features (feature_id ASC);

-- Reference: product_features_feature (table: product_features)
ALTER TABLE product_features ADD CONSTRAINT product_features_feature
    FOREIGN KEY (feature_id)
    REFERENCES feature (id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: product_features_product (table: product_features)
ALTER TABLE product_features ADD CONSTRAINT product_features_product
    FOREIGN KEY (product_id)
    REFERENCES product (id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

Classes generated by SQLacodegen:

t_product_features = Table(
    'product_features', metadata,
    Column('product_id', ForeignKey('product.id'), primary_key=True, nullable=False),
    Column('feature_id', ForeignKey('feature.id'), primary_key=True, nullable=False)
)


t_product_related = Table(
    'product_related', metadata,
    Column('product_id', ForeignKey('product.id'), primary_key=True, nullable=False, index=True),
    Column('related_id', ForeignKey('related.id'), primary_key=True, nullable=False, index=True)
)

class Feature(Base):
    __tablename__ = 'feature'

    id = Column(BigInteger, primary_key=True, server_default=text("nextval('feature_id_seq'::regclass)"))
    name = Column(Text, nullable=False)

    products = relationship('Product', secondary='product_features')


class Related(Base):
    __tablename__ = 'related'

    id = Column(BigInteger, primary_key=True, server_default=text("nextval('related_id_seq'::regclass)"))
    name = Column(Text, nullable=False)

class Product(Base):
    __tablename__ = 'product'

    id = Column(BigInteger, primary_key=True, server_default=text("nextval('product_id_seq'::regclass)"))
    mpn = Column(Text)
    title = Column(Text)
    price = Column(Float)
    msrp = Column(Float)
    stock_availability = Column(Boolean)
    country_id = Column(ForeignKey('country.id'), index=True)
    description = Column(Text)
    weight_packaging = Column(Float)
    item_type = Column(String(20))
    upc = Column(String(40))
    model = Column(String(60))
    sku = Column(String(40))
    badges = Column(Text)
    url = Column(Text)
    site_item_id = Column(Text)
    brand_id = Column(ForeignKey('brand.id'), index=True)

    brand = relationship('Brand')
    country = relationship('Country')
    relateds = relationship('Related', secondary='product_related')

How it is supposed to be:

t_product_features = Table(
    'product_features', metadata,
    Column('product_id', ForeignKey('product.id'), primary_key=True, nullable=False),
    Column('feature_id', ForeignKey('feature.id'), primary_key=True, nullable=False)
)


t_product_related = Table(
    'product_related', metadata,
    Column('product_id', ForeignKey('product.id'), primary_key=True, nullable=False, index=True),
    Column('related_id', ForeignKey('related.id'), primary_key=True, nullable=False, index=True)
)

class Product(Base):
    __tablename__ = 'product'

    id = Column(BigInteger, primary_key=True, server_default=text("nextval('product_id_seq'::regclass)"))
    mpn = Column(Text)
    title = Column(Text)
    price = Column(Float)
    msrp = Column(Float)
    stock_availability = Column(Boolean)
    country_id = Column(ForeignKey('country.id'), index=True)
    description = Column(Text)
    weight_packaging = Column(Float)
    item_type = Column(String(20))
    upc = Column(String(40))
    model = Column(String(60))
    sku = Column(String(40))
    badges = Column(Text)
    url = Column(Text)
    site_item_id = Column(Text)
    brand_id = Column(ForeignKey('brand.id'), index=True)

    brand = relationship('Brand')
    country = relationship('Country')
    relateds = relationship('Related', secondary='product_related')
    features = relationship('Feature', secondary='product_feature')

class Feature(Base):
    __tablename__ = 'feature'

    id = Column(BigInteger, primary_key=True, server_default=text("nextval('feature_id_seq'::regclass)"))
    name = Column(Text, nullable=False)

class Related(Base):
    __tablename__ = 'related'

    id = Column(BigInteger, primary_key=True, server_default=text("nextval('related_id_seq'::regclass)"))
    name = Column(Text, nullable=False)

[vickmu]

The parent-child relationship is determined based on the order of table names in the database. The table names are alphabetically sorted in the database. In this instance, the Product is coming before Related, therefore, the Product is the parent of Related. However, since Feature comes before Product, the former is bein defined as the parent of the latter.