Association table through two columns?

[Mateja Putic]

I would like to create an association between a Dataset object and all Category objects through an intermediate Annotation table. 

A Dataset contains a collection of Annotations. Each Annotation has a single Category. I want Dataset.categories to contain the unique set of Categories made up of all the Categories of all the Annotations in that Dataset. I have tried doing this with a double association table (dataset_categories), but it is not working. What is the right way to do this? Here is my code so far:

Base = declarative_base()

dataset_categories = Table('dataset_categories', Base.metadata,
    Column('dataset_id', Integer, ForeignKey('datasets.id')),
    Column('annotation_id', Integer, ForeignKey('annotations.id')),
    Column('category_id', Integer, ForeignKey('categories.id')))

class Dataset(Base):
    __tablename__ = 'datasets'

    id = Column(Integer, primary_key=True)
    annotations = relationship("Annotation")
    categories = relationship("Category", secondary=dataset_categories)

class Annotation(Base):
    __tablename__ = 'annotations'
    id = Column(Integer, primary_key=True)
    category_id = Column(Integer, ForeignKey('categories.id'), nullable=False)
    category = relationship("Category")
    dataset_id = Column(Integer, ForeignKey('datasets.id'))

class Category(Base):
    __tablename__ = 'categories'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False, unique=True)
    dataset = relationship("Dataset", secondary=dataset_categories)
    dataset_id = Column(Integer, ForeignKey('datasets.id'),
                        back_populates='categories')

[Mike Bayer]

On Mon, Sep 30, 2019, at 12:25 AM, Mateja Putic wrote:

I would like to create an association between a Dataset object and all Category objects through an intermediate Annotation table. 

A Dataset contains a collection of Annotations. Each Annotation has a single Category. I want Dataset.categories to contain the unique set of Categories made up of all the Categories of all the Annotations in that Dataset. I have tried doing this with a double association table (dataset_categories), but it is not working. What is the right way to do this? Here is my code so far:

hello -

your code looks fine although I have not carefully parsed your rather complex statement of intent.  Can you provide specifics for "not working" please and do you have a specific database schema designed in order to achieve what you are looking for?    if you have a general database schema design issue you will get more help on stack overflow.

Base = declarative_base()

dataset_categories = Table('dataset_categories', Base.metadata,
    Column('dataset_id', Integer, ForeignKey('datasets.id')),
    Column('annotation_id', Integer, ForeignKey('annotations.id')),
    Column('category_id', Integer, ForeignKey('categories.id')))

class Dataset(Base):
    __tablename__ = 'datasets'

    id = Column(Integer, primary_key=True)
    annotations = relationship("Annotation")
    categories = relationship("Category", secondary=dataset_categories)

class Annotation(Base):
    __tablename__ = 'annotations'
    id = Column(Integer, primary_key=True)
    category_id = Column(Integer, ForeignKey('categories.id'), nullable=False)
    category = relationship("Category")
    dataset_id = Column(Integer, ForeignKey('datasets.id'))

class Category(Base):
    __tablename__ = 'categories'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False, unique=True)
    dataset = relationship("Dataset", secondary=dataset_categories)
    dataset_id = Column(Integer, ForeignKey('datasets.id'),
                        back_populates='categories')

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/39a80b36-617a-4fed-95e3-7dde244d1abf%40googlegroups.com.