SQLAlchemy query filter on child attribute

Question

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)

my test data are the following:

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

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)

but this error occurs:

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

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

Answer 1

You should change your query.

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

Answer 2

I know you mentioned you don't want to query the child class, but as that's what's happening behind the scenes (SQLAlchemy is just hiding it from you), you might as well. Then you can simply access the parent object through the backref. The speed will be exactly the same since you specified lazy=joined.

q = s.query(Child).filter(Child.value > 20)
parent_obj = q.parent