通过grandparent...in sqlalchemy按父选择的最佳方法

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import join
from sqlalchemy.orm import aliased

# 创建数据库引擎和会话
engine = create_engine('数据库连接字符串')
Session = sessionmaker(bind=engine)
session = Session()

# 创建表的映射类
class Grandparent(Base):
    __tablename__ = 'grandparent'
    id = Column(Integer, primary_key=True)
    name = Column(String)

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    grandparent_id = Column(Integer, ForeignKey('grandparent.id'))

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

# 创建表的关联关系
Grandparent.children = relationship("Parent", back_populates="grandparent")
Parent.grandparent = relationship("Grandparent", back_populates="children")
Parent.children = relationship("Child", back_populates="parent")
Child.parent = relationship("Parent", back_populates="children")

# 使用join()方法和filter()方法进行查询
grandparent_alias = aliased(Grandparent)
parent_alias = aliased(Parent)

query = session.query(Child).\
    join(parent_alias, Child.parent).\
    join(grandparent_alias, parent_alias.grandparent).\
    filter(grandparent_alias.name == '父名称')

result = query.all()