python - SQLAlchemy 一对多查询

Question

我对如何进行查询感到困惑。我有以下声明

class GrandParent(Base):
    __tablename__ = "grandparent"
    id = Column(Integer, primary_key=True)
    name = Column(String(16))
    # One-to-one relationship
    parent_id = Column(Integer, ForeignKey('parent.id'))
    parent = relationship("Parent", backref=backref("grandparent", uselist=False))

    def __init__(self, name):
        self.name = name


class Parent(Base):
    __tablename__ = "parent"
    id = Column(Integer, primary_key=True)
    name = Column(String(16))
    # One-to-many relationship
    children = relationship("Child", backref="parent")

    def __init__(self, name):
        self.name = name


class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key=True)
    name = Column(String(16))
    index = Column(Integer)
    parent_id = Column(Integer, ForeignKey('parent.id'))

    def __init__(self, name, idx):
        self.name = name
        self.index = idx

    def __repr__(self):
        return self.name

我想做的是查询一个 Child 对象，知道它的 index 和 GrandParent.id。我知道这个查询不起作用，只是为了说明我在寻找什么:

c = session.query(Child).filter(Child.parent.grandparent.id == 2 and Child.index == 3).first()

AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Child.parent has an attribute 'grandparent'

然而这是可行的:

grandparent = GrandParent('dad')
grandparent.parent = Parent('bob')
grandparent.parent.children.append(Child('alice', 1))
grandparent.parent.children.append(Child('jo', 2))
grandparent.parent.children.append(Child('blo', 3))
foo = Child('foo', 4)
grandparent.parent.children.append(foo)
session.add(grandparent)
print(foo.grandparent.parent.id)

Answer 1

发布后终于找到了解决方案。

参见 doc供引用

c = session.query(Child).\
        filter(Child.parent.has(Parent.grandparent.has(GrandParent.id == 2))).\
        filter(Child.index == 2).first()