SQLAlchemy多对多查询返回主键而非完整对象问题

问题原因 #

你的查询语句select(Employee, User)会同时返回关联表和User表的结果，scalars()默认提取第一个实体（也就是Employee表的字段），所以得到的是主键值而非完整User对象。另外原模型的关系定义也存在方向混淆的问题，需要先修正。

步骤1：修正User模型的关系定义 #

原关系的primaryjoin和secondaryjoin方向搞反了，且backref命名不准确。调整后的模型如下：

class User(BaseModel):
    __tablename__ = "user"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)  # 补充遗漏的主键字段
    email = db.Column(db.String, unique=True, index=True)
    password = db.Column(db.String, nullable=False)

    # 雇员关联自己的雇主
    employers = relationship(
        "User",
        secondary=Employee,
        primaryjoin="User.id == Employee.c.employee_id",
        secondaryjoin="User.id == Employee.c.employer_id",
        back_populates="employees"
    )
    # 雇主关联自己的雇员
    employees = relationship(
        "User",
        secondary=Employee,
        primaryjoin="User.id == Employee.c.employer_id",
        secondaryjoin="User.id == Employee.c.employee_id",
        back_populates="employers"
    )


Employee = Table(
    "employee",
    BaseModel.metadata,
    Column("employer_id", ForeignKey("user.id"), primary_key=True),
    Column("employee_id", ForeignKey("user.id"), primary_key=True),
)

步骤2：正确查询雇主ID为1的所有雇员 #

有两种简洁的实现方式：

方式一：利用模型的关系直接查询 #

先获取雇主对象，再通过关系属性直接获取雇员列表：

# 获取雇主ID为1的用户对象
employer = await db.get(User, 1)
# 直接通过employees属性获取所有关联的雇员对象
employees = employer.employees

方式二：使用JOIN查询并指定返回User对象 #

如果需要直接通过查询语句实现，要明确指定查询的是雇员的User对象，而非关联表：

user_employee = aliased(User)
user_employer = aliased(User)

employees = await db.execute(
    select(user_employee)
    .join(Employee, user_employee.id == Employee.c.employee_id)
    .join(user_employer, user_employer.id == Employee.c.employer_id)
    .where(user_employer.id == 1)
)
# 获取所有User对象，unique()避免重复结果
return employees.scalars().unique().all()

验证结果 #

调整后执行查询，返回的将是完整的User对象列表，包含email、password等字段，而非单纯的主键值。

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