子属性的SQLAlchemy查询过滤器 [英] SQLAlchemy query filter on child attribute

问题描述

我的模型由一对一的父母和孩子组成:

My model consists of a Parent and Child with a one-to-one relationship:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    child = relationship("Child", backref="parent", uselist=False, lazy='joined')


class Child(Base):
    __tablename__ = 'child'
    child_id = Column(Integer, ForeignKey(Parent.id), primary_key=True)
    value = Column(Integer)

我的测试数据如下:

q = s.query(Parent)
pd.read_sql(q.statement,s.bind) 
    id  name  child_id  value
    1      a         1     10
    2      b         2     20
    3      c         3     30

现在我想使用此查询仅获取child.value> 20的父母:

Now I'd like to get only the parents with child.value > 20 using this query:

q = s.query(Parent).filter(Parent.child.value > 20)

但是会发生此错误:

AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object 
associated with Parent.child has an attribute 'value'

我当然可以直接在Child类上进行查询，但我的目标是检索Parent对象.

Of course I can query direct on the Child class but my goal is to retrieve a Parent object.

推荐答案

您应该更改查询.

# version-1: use JOIN
q = s.query(Parent).join(Child, Parent.child).filter(Child.value > 20)

# or:
# version-2: use EXISTS
q = s.query(Parent).filter(Parent.child.has(Child.value > 20))

这篇关于子属性的SQLAlchemy查询过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！