SQLAlchemy一对多的关系，如何过滤集合 [英] SQLAlchemy one to many relationship, how to filter the collection

问题描述

细菌产品与 ProductPicture 有一对多的关系。

$ b

  picture_type_enums =（'main'，'related'，'option '）
 
 class ProductPicture（Base）：
 
 __tablename__ ='product_pictures'
 
 picture_id = Column（Integer，primary_key = True）
 product_id = Column（Integer，ForeignKey（'products.product_id'））
 picture_type = Column（Enum（* picture_type_enums））
 url = Column（String（120））
  

和我的产品模型如下所示：

  
 
 
 
 
 
 
 
 
 
 $ product $ （100））
 product_pictures = relationship（ProductPicture）
  

我的问题是针对产品我可能有不同的类型产品图片。我知道如果我有一个产品实例 p ，我可以调用 p.product_pictures 来获得所有类型的产品图片。然而，我想要的东西像 p.main_pictures ，它获得所有类型的产品图片'main'和 p.option_pictures 获得'选项'类型的所有 product_pictures 。有没有一个很好的方法来做到这一点。

谢谢 如果您阅读了 sqlalchemy.orm .relationship ，您可以看到，您可以通过使用 primaryjoin 参数显式定义条件来进一步限制关系。完美地说明您所需的场景。

 类适应您的要求，现在 Product  Product（Base）：
 
 __tablename__ ='products'
 
 product_id = Column（Integer，primary_key = True）
 product_name = Column（String（100））
 product_pictures = relationship（ProductPicture）
 
 main_pictures = relationship（ProductPicture，
 primaryjoin =and_（Product.product_id == ProductPicture.product_id，
 ProductPicture.picture_type =='main'））
 option_pictures = relationship（ProductPicture，
 primaryjoin =and_（Product.product_id == ProductPicture.product_id，
ProductPicture。 
  

示例会话

 >>> p = Product（）
>>> p.product_name ='test product'
>>> p.product_id = 1 
>>> session.add（p）
>>> pic1 = ProductPicture（）
>>> pic1.product_id = p.product_id 
>>> pic1.picture_type ='main'
>>> pic1.url ='http://example.com/p1.main.png'
>>> session.add（pic1）
>>> pic2 = ProductPicture（）
>>> pic2.product_id = p.product_id 
>>> pic2.picture_type ='option'
>>> pic2.url ='http://example.com/p1.option1.png'
>>> session.add（pic2）
>>> pic3 = ProductPicture（）
>>> pic3.product_id = p.product_id 
>>> pic3.picture_type ='option'
>>> pic3.url ='http://example.com/p1.option2.png'
>>> session.add（pic3）
>>> session.commit（）
>>> [（pic.picture_type，pic.url）for p.product_pictures] 
 [（u'main'，u'http：//example.com/p1.main.png'），（u'option '，u'http：//example.com/p1.option1.png'），（u'option'，u'http：//example.com/p1.option2.png'）] 
> >> [（pic.picture_type，pic.url）for p.main_pictures] 
 [（u'main'，u'http：//example.com/p1.main.png'）] 
 >>> [（pic.picture_type，pic.url）for p.option_pictures] 
 [（u'option'，u'http：//example.com/p1.option1.png'），（u'option '，u'http：//example.com/p1.option2.png'）] 
  

Bacially Product has a one to many relationship to ProductPicture.

My product picture model looks like this:

picture_type_enums = ('main', 'related', 'option')

class ProductPicture(Base):

    __tablename__ = 'product_pictures'

    picture_id = Column(Integer, primary_key = True)
    product_id = Column(Integer, ForeignKey('products.product_id'))
    picture_type = Column(Enum(*picture_type_enums))
    url = Column(String(120))

and my product model looks like this:

class Product(Base):

    __tablename__ = 'products'

    product_id = Column(Integer, primary_key=True)
    product_name = Column(String(100))
    product_pictures = relationship("ProductPicture")

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.

Thanks

解决方案

If you read the documentation for sqlalchemy.orm.relationship, you can see that you can further limit the relationship by explicitly define the condition using the primaryjoin argument, with an example that perfectly illustrating your required scenario. Adapting that with your requirements, the Product class now follows:

class Product(Base):

    __tablename__ = 'products'

    product_id = Column(Integer, primary_key=True)
    product_name = Column(String(100))
    product_pictures = relationship("ProductPicture")

    main_pictures = relationship("ProductPicture",
        primaryjoin="and_(Product.product_id==ProductPicture.product_id, "
                    "ProductPicture.picture_type=='main')")
    option_pictures = relationship("ProductPicture",
        primaryjoin="and_(Product.product_id==ProductPicture.product_id, "
                    "ProductPicture.picture_type=='option')")

Example session:

>>> p = Product()
>>> p.product_name = 'test product'
>>> p.product_id = 1
>>> session.add(p)
>>> pic1 = ProductPicture()
>>> pic1.product_id = p.product_id
>>> pic1.picture_type = 'main'
>>> pic1.url = 'http://example.com/p1.main.png'
>>> session.add(pic1)
>>> pic2 = ProductPicture()
>>> pic2.product_id = p.product_id
>>> pic2.picture_type = 'option'
>>> pic2.url = 'http://example.com/p1.option1.png'
>>> session.add(pic2)
>>> pic3 = ProductPicture()
>>> pic3.product_id = p.product_id
>>> pic3.picture_type = 'option'
>>> pic3.url = 'http://example.com/p1.option2.png'
>>> session.add(pic3)
>>> session.commit()
>>> [(pic.picture_type, pic.url) for pic in p.product_pictures]
[(u'main', u'http://example.com/p1.main.png'), (u'option', u'http://example.com/p1.option1.png'), (u'option', u'http://example.com/p1.option2.png')]
>>> [(pic.picture_type, pic.url) for pic in p.main_pictures]
[(u'main', u'http://example.com/p1.main.png')]
>>> [(pic.picture_type, pic.url) for pic in p.option_pictures]
[(u'option', u'http://example.com/p1.option1.png'), (u'option', u'http://example.com/p1.option2.png')]

这篇关于SQLAlchemy一对多的关系，如何过滤集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！