CODEWITHSUNDEEP
pythonfiltersqlalchemyone-to-many
hashchen
hashchen

Reputation: 1041

SQLAlchemy one to many relationship, how to filter the collection

Bacially Product has a one to many relationship to ProductPicture.

My product picture model looks like this:

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

and my product model looks like this:

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

My question is for one product I might have different types of product pictures. I know if I have a Product instance p, I can call p.product_pictures to get all types of product pictures. However, I want something like p.main_pictures which get all the product pictures of type 'main', and p.option_pictures gets all the product pictures of type 'option'. Is there a good way to do it?

This question has been updated for SQLAlchemy 2.0 to reflect the 2.0 style while maintaining the original intent as of this prior revision which was written in the 1.x style.

Upvotes: 3

Views: 2750

Answers (1)

metatoaster
metatoaster

Reputation: 18898

This answer has been updated for SQLAlchemy 2.0 to reflect the 2.0 style while maintaining the original intent of this prior revision which was written in the 1.x style.

Referencing the documentation for relationships, we may find that under the later portion of section late-evaluation of relationship arguments, that it is possible to limit what's being included by explicitly define the condition using the primaryjoin argument. Adapting the documented examples to your requirements, the Product class may now be defined follows:

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

Using a session context to add an example product and print the newly added attributes:

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

Should produce the following output:

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

Best tried with the complete code example as it includes all the necessary imports which were omitted from all the code examples from above.

Upvotes: 3

Related Questions