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.

score 25 · Accepted Answer · answered Nov 10 '16 at 11:48

25

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

answered Nov 10 '16 at 11:48

van

74,297
13
168
171

Thanks @van but I'm trying to replicate the Quantopian API for get_fundamentals, see also this question: http://stackoverflow.com/questions/40497528/sqlalchemy-model-for-quantopian-get-fundamentals. I cannot understand how they defined the underlying model. – kostia Nov 22 '16 at 19:42
I read your question as *how can I get `Parent` object?* and I believe that my answer covers it. As to your other question and its link to Quantopian: the question is not clear - what works and what does not? I can only confirm that accessing relationships using multi-level property navigation (such as `Parent.child[.subchild].property`) does not work with sqlalchemy. And the example of Quantopian does not show anything contrary to that - `fundamentals` is the module, not a mapped class. – van Nov 22 '16 at 22:54
Yes @van, you're perfectly right. I just wanted to point to my other question, which originated this one and you give me a good hint. Now I also think that fundamentals has to be the module. – kostia Nov 23 '16 at 21:49

score 1 · Answer 2 · answered Nov 10 '16 at 15:29

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

SQLAlchemy query filter on child attribute

2 Answers2

Linked