python - Sqlalchemy 双关联表？

Question

我想通过注释表在数据集对象和所有类别对象之间创建关联。

数据集包含注释的集合。每个注释都有一个类别。我希望 Dataset.categories 包含唯一的一组类别，由该数据集实例中所有注释的所有类别组成。我尝试使用双关联表(dataset_categories)执行此操作，但它不起作用。这样做的正确方法是什么？这是到目前为止我的代码:

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')

Answer 1

如果不要求关联仅包含唯一类别，这就像使用 association_proxy 一样简单。一种选择是在定义关系时定义用作 set 的集合类:

class Dataset(Base):
    __tablename__ = 'datasets'

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

另一方面，关系的辅助表不必是基表，因此可以使用注释中的简单选择:

class Dataset(Base):
    __tablename__ = 'datasets'

    id = Column(Integer, primary_key=True)
    annotations = relationship("Annotation")
    categories = relationship("Category",
                              secondary="""select([annotations.c.dataset_id,
                                                   annotations.c.category_id]).\\
                                           distinct().\\
                                           alias()""",
                              viewonly=True)