SQLAlchemy ORM查询嵌套对象指定字段报错求助

问题场景 #

我通过SQLAlchemy ORM的dataclass映射了一组存在深度嵌套关联的数据库表（代码如下），全量查询session.execute(select(Project)).all()可以正常获取包含嵌套user_created对象的完整Project数据，但因嵌套层级过多，希望仅查询指定字段及关联对象，尝试多种方式均报错：

映射代码 #

from dataclasses import dataclass, field
from datetime import datetime
from typing import Optional
from sqlalchemy import Column, NUMBER, VARCHAR
from sqlalchemy.orm import mapper_registry, relationship, load_only, selectinload
from sqlalchemy.sql import select

@mapper_registry.mapped
@dataclass
class Project:
    __tablename__ = "project"
    __table_args__ = ()  # 原约束省略

    id: float = field(init=False, metadata={"sa": Column(NUMBER(15, 0, False), primary_key=True)})
    date_created: datetime = field(metadata={"sa": Column(VARCHAR(20), nullable=False)})
    user_created_id: float = field(metadata={"sa": Column(NUMBER(15, 0, False), nullable=False)})
    user_created: Optional["User"] = field(default=None, metadata={"sa": relationship("User", foreign_keys="Project.user_created_id", backref="project")})

@mapper_registry.mapped
@dataclass
class User:
    __tablename__ = "user"
    __table_args__ = ()
    __sa_dataclass_metadata_key__ = "sa"

    id: float = field(
        init=False, metadata={"sa": Column(NUMBER(15, 0, False), primary_key=True)}
    )
    name: str = field(metadata={"sa": Column(VARCHAR(100), nullable=False)})
    organization_id: Optional[float] = field(default=None, metadata={"sa": Column(NUMBER(15, 0, False))})
    department_id: Optional[float] = field(default=None, metadata={"sa": Column(NUMBER(15, 0, False))})
    site_id: Optional[float] = field(default=None, metadata={"sa": Column(NUMBER(15, 0, False))})
    department: Optional["Department"] = field(default=None,metadata={"sa": relationship("Department", backref="user")})
    organization: Optional["Organization"] = field(default=None,metadata={"sa": relationship("Organization", backref="user")})
    site: Optional["Site"] = field(default=None,metadata={"sa": relationship("Site", backref="user")})

@dataclass
class Department:
    __tablename__ = "department"
    __table_args__ = ()
    __sa_dataclass_metadata_key__ = "sa"

    id: float = field(init=False, metadata={"sa": Column(NUMBER(15, 0, False), primary_key=True)})
    label: str = field(metadata={"sa": Column(VARCHAR(255), nullable=False)})

@dataclass
class Organization:
    __tablename__ = "organization"
    __table_args__ = ()
    __sa_dataclass_metadata_key__ = "sa"

    id: float = field(init=False, metadata={"sa": Column(NUMBER(15, 0, False), primary_key=True)})
    label: str = field(metadata={"sa": Column(VARCHAR(255), nullable=False)})

@dataclass
class Site:
    __tablename__ = "site"
    __table_args__ = ()
    __sa_dataclass_metadata_key__ = "sa"

    id: float = field(init=False, metadata={"sa": Column(NUMBER(15, 0, False), primary_key=True)})
    label: str = field(metadata={"sa": Column(VARCHAR(255), nullable=False)})

尝试的错误操作及报错 #

1. 直接选择字段和关联对象：

session.execute(select(Project.id, Project.user_created)).all()

报错：

ORA-00923: FROM keyword not found where expected
[SQL: SELECT project.id, user.id = project.user_created_id AS anon_1 FROM project, user]

2. 使用load_only加载关联对象：

query = select(Project).options(load_only(Project.id, Project.user_created))

报错：

Can't apply "column loader" strategy to property "Project.user_created", which is a "relationship"; this loader strategy is intended to be used with a "column property".

3. 组合load_only和selectinload：

query = select(Project).options(load_only(project.id), selectinload(Project.user_created))

执行失败（因project.id大小写错误）

正确解决方案 #

要实现仅查询Project的指定字段，同时加载关联的User对象并按需限制字段，需结合load_only（限制实体字段）和关联加载器（selectinload/joinedload）配合嵌套load_only（限制关联对象字段），分场景处理：

场景1：仅加载Project指定字段 + 关联User的全部字段 #

如果只需要Project的id，同时完整加载user_created，用load_only限制Project字段，配合selectinload/joinedload加载关联：

query = select(Project).options(
    load_only(Project.id),
    selectinload(Project.user_created)  # 批量加载关联User，避免N+1查询；用joinedload可改为JOIN方式查询
)
result = session.execute(query).scalars().all()

场景2：加载Project指定字段 + 关联User的指定字段 #

如果需要进一步限制User的字段（比如仅加载id和name），在关联加载中嵌套load_only：

query = select(Project).options(
    load_only(Project.id),
    selectinload(Project.user_created).options(
        load_only(User.id, User.name)  # 限制User仅加载指定字段
    )
)
result = session.execute(query).scalars().all()

场景3：直接查询字段组合（返回元组而非ORM对象） #

如果不需要完整ORM对象，仅需指定字段的组合，可直接选择关联对象的字段并显式关联表：

query = select(Project.id, User.id, User.name).join(Project.user_created)
result = session.execute(query).all()

返回结果为元组列表，格式为(project_id, user_id, user_name)

修复原代码小问题 #

原User类中site字段的关联对象写错，需修正为关联Site类：

site: Optional["Site"] = field(default=None,metadata={"sa": relationship("Site", backref="user")})

内容的提问来源于stack exchange，提问作者Isaac