如何查询一对多层级中特定用户的深度嵌套子表X3？

在一对多层级结构中，能否获取单个/全部深度嵌套的子表？假设已登录并获取用户实例：

u = db.session.execute(db.select(U).where(U.email == 'user@example.com')).scalar()

数据层级结构如下：

<- X3 ->
    <- X1 -><- X2 ->
                    <- X3 ->
-- U1 ->
                        <- X3 ->
        <- X1 -><- X2 ->
                        <- X3 ->

--------------------------------

                        <- X3 ->
        <- X1 -><- X2 ->
                        <- X3 ->
-- U2 ->
                        <- X3 ->
        <- X1 -><- X2 ->
                        <- X3 ->

对应的模型类代码：

class U(UserMixin, db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String(64), index=True, unique=True)
    # ...
    x1: Mapped[list["X1"]] = db.relationship(back_populates="u", cascade='all, delete')

class X1(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    # ...
    u: Mapped["U"] = db.relationship(back_populates="x1", cascade='all, delete')
    u_id: Mapped[int] = mapped_column(ForeignKey("u.id"))
    x2: Mapped[list["X2"]] = db.relationship(back_populates="x1", cascade='all, delete')

class X2(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    # ...
    x1: Mapped["X1"] = db.relationship(back_populates="x2", cascade='all, delete')
    x1_id: Mapped[int] = mapped_column(ForeignKey("x1.id"))
    x3: Mapped[list["X3"]] = db.relationship(back_populates="x2", cascade='all, delete')
    
class X3(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    # ...
    x2: Mapped["X2"] = db.relationship(back_populates="x3", cascade='all, delete')
    x2_id: Mapped[int] = mapped_column(ForeignKey("x2.id"))

获取该用户下的所有X3记录 #

有两种常用实现方式：

方式1：通过关联关系遍历 #

利用已有的用户实例，逐层遍历关联子表收集X3对象：

all_x3 = []
for x1 in u.x1:
    for x2 in x1.x2:
        all_x3.extend(x2.x3)

注意：直接遍历可能触发N+1查询问题，若数据量较大，建议预加载关联数据优化性能：

from sqlalchemy.orm import selectinload

# 获取用户时预加载所有层级关联
u = db.session.execute(
    db.select(U)
    .where(U.email == 'user@example.com')
    .options(
        selectinload(U.x1)
        .selectinload(X1.x2)
        .selectinload(X2.x3)
    )
).scalar()

# 预加载后遍历不再触发额外查询
all_x3 = []
for x1 in u.x1:
    for x2 in x1.x2:
        all_x3.extend(x2.x3)

方式2：直接数据库JOIN查询 #

跳过层级遍历，通过外键关联直接筛选用户所属的X3，性能更优：

all_x3 = db.session.execute(
    db.select(X3)
    .join(X2)
    .join(X1)
    .join(U)
    .where(U.id == u.id)
).scalars().all()

获取该用户下的单个X3记录 #

若已知X3的唯一标识（如id），可直接查询并校验归属：

# 示例：获取id为123且属于当前用户的X3
target_x3 = db.session.execute(
    db.select(X3)
    .join(X2)
    .join(X1)
    .join(U)
    .where(X3.id == 123, U.id == u.id)
).scalar()

返回None则表示该X3不存在或不属于当前用户。

内容的提问来源于stack exchange，提问作者Hi-tecX