SQLAlchemy一对多关系，如何过滤集合

巴西利亚Product具有一对多关系ProductPicture.

我的产品图片模型如下所示：

PictureTypes = Literal['main', 'related', 'option']

class ProductPicture(Base):
    __tablename__ = 'product_pictures'

    picture_id: Mapped[int] = mapped_column(primary_key=True)
    product_id: Mapped[int] = mapped_column(ForeignKey('products.product_id'))
    picture_type: Mapped[PictureTypes] = mapped_column(Enum(*get_args(PictureTypes)))
    url: Mapped[str] = mapped_column(String(120))

我的产品模型如下所示：

class Product(Base):
    __tablename__ = 'products'

    product_id: Mapped[int] = mapped_column(primary_key=True)
    product_name: Mapped[str] = mapped_column(String(100))
    product_pictures: Mapped[List["ProductPicture"]] = relationship()

我的问题是，对于一种产品，我可能有不同类型的产品图片。我知道如果我有一个Product实例p， 我可以打电话p.product_pictures获取所有类型的产品图片。但是，我想要类似的东西p.main_pictures获取该类型的所有产品图片'main', and p.option_pictures获取该类型的所有产品图片'option'。有没有好的办法呢？

_{This question has been updated for SQLAlchemy 2.0 to reflect the 2.0 style https://docs.sqlalchemy.org/en/20/glossary.html#term-2.0-style while maintaining the original intent as of this prior revision https://stackoverflow.com/revisions/24947160/2 which was written in the 1.x style.}

_{This answer has been updated for SQLAlchemy 2.0 to reflect the 2.0 style https://docs.sqlalchemy.org/en/20/glossary.html#term-2.0-style while maintaining the original intent of this prior revision https://stackoverflow.com/revisions/24947583/2 which was written in the 1.x style.}

参考关系文档，我们可能会发现在本节的后面部分关系论证的后期评估 https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html#late-evaluation-of-relationship-arguments，可以通过使用显式定义条件来限制所包含的内容primaryjoin https://docs.sqlalchemy.org/en/20/orm/relationship_api.html#sqlalchemy.orm.relationship.params.primaryjoin争论。根据您的要求调整记录的示例，Product类现在可以定义如下：

class Product(Base):
    __tablename__ = 'products'

    product_id: Mapped[int] = mapped_column(primary_key=True)
    product_name: Mapped[str] = mapped_column(String(100))
    product_pictures: Mapped[List["ProductPicture"]] = relationship()
    main_pictures: Mapped[List["ProductPicture"]] = relationship(
        viewonly=True,
        primaryjoin=lambda: and_(
            Product.product_id == ProductPicture.product_id,
            ProductPicture.picture_type == 'main'
        ),
    )
    option_pictures: Mapped[List["ProductPicture"]] = relationship(
        viewonly=True,
        primaryjoin=lambda: and_(
            Product.product_id == ProductPicture.product_id,
            ProductPicture.picture_type == 'option'
        ),
    )

使用会话上下文添加示例产品并打印新添加的属性：

with Session(engine) as session:
    product = Product(
        product_name='test product',
        product_pictures=[
            ProductPicture(picture_type='main', url='p1.main.png'),
            ProductPicture(picture_type='option', url='p1.option1.png'),
            ProductPicture(picture_type='option', url='p1.option2.png'),
        ],
    )
    session.add(product)
    session.commit()
    print("product_pictures:",
        [(pic.picture_type, pic.url) for pic in product.product_pictures])
    print("main_pictures:",
        [(pic.picture_type, pic.url) for pic in product.main_pictures])
    print("option_pictures:",
        [(pic.picture_type, pic.url) for pic in product.option_pictures])

应该产生以下输出：

product_pictures: [('main', 'p1.main.png'), ('option', 'p1.option1.png'), ('option', 'p1.option2.png')]
main_pictures: [('main', 'p1.main.png')]
option_pictures: [('option', 'p1.option1.png'), ('option', 'p1.option2.png')]

最好尝试与完整的代码示例 https://gist.github.com/metatoaster/735da9a5cf82f67707bbb4bc1860a30a因为它包含上面所有代码示例中省略的所有必要的导入。