sqlalchemy: 使用声明式和反射多次连接同一张表

Question

这是我问题的简化版本：

假设我有两个表：'procedure'（过程）和 'role'（角色）。

'role' 表有两个字段：角色唯一标识（role_uid）和角色名称（role_name）。

'procedure' 表有四个字段：过程唯一标识（procedure_uid）、过程名称（procedure_name）、通知角色唯一标识（inform_role_uid）和咨询角色唯一标识（consult_role_uid）。

所以，'role' 表和 'procedure' 表之间有两个一对多的关系。

一些代码：

class Role(Base):
    __tablename__ = "role"
    __table_args__ = ({'autoload':True, 'useexisting': True})

class Procedure(Base):
    __tablename__ = "procedure"
    __table_args__ = (sqlalchemy.ForeignKeyConstraint(['consult_role_uid','inform_role_uid'],['role.role_uid', 'role.role_uid']),
        {'autoload':True, 'useexisting': True})

Procedure.consult_role = sqlalchemy.orm.relationship(Role,
 primaryjoin="Procedure.consult_role_uid==Role.role_uid", foreign_keys=Role.role_uid)
Procedure.inform_role = sqlalchemy.orm.relationship(Role,
 primaryjoin="Procedure.inform_role_uid==Role.role_uid", foreign_keys=Role.role_uid)

consult_role = sqlalchemy.orm.aliased(Role, name="consult_role")
inform_role = sqlalchemy.orm.aliased(Role, name="inform_role")

query = session.query(
    Procedure.procedure_name, 
    consult_role.role_name.label("consult_role_name"),
    inform_role.role_name.label("inform_role_name")).join(consult_role, inform_role)

这段代码生成了以下 SQL 语句：

SELECT 
  `procedure`.procedure_name AS procedure_procedure_name, 
  consult_role.role_name AS consult_role_name, 
  inform_role.role_name AS inform_role_name 
FROM 
  `procedure` 
  INNER JOIN role AS consult_role 
    ON consult_role.role_uid = `procedure`.consult_role_uid
      AND consult_role.role_uid = `procedure`.inform_role_uid 
  INNER JOIN role AS inform_role 
    ON inform_role.role_uid = `procedure`.consult_role_uid 
      AND inform_role.role_uid = `procedure`.inform_role_uid

正如你所看到的，我并不打算让每一个内部连接都在两个字段上进行连接。为什么看起来我的 'primaryjoin' 参数被忽略了呢？

Answer 1

这个：

sqlalchemy.ForeignKeyConstraint(['consult_role_uid','inform_role_uid'],['role.role_uid', 'role.role_uid'])

大致上是说两个表之间的关系是通过两个属性来建立的，就像是这个引用有一个组合主键一样。如果你想要有两个外键引用，你需要指定 ForeignKey 两次。

class Procedure(Base):
    __tablename__ = "procedure"
    __table_args__ = (
        sqlalchemy.ForeignKeyConstraint(['consult_role_uid'],['role.role_uid']),
        sqlalchemy.ForeignKeyConstraint(['inform_role_uid'],['role.role_uid']),
        {'autoload':True, 'useexisting': True})

Answer 2

为了完整起见，这里是解决上面问题的修正代码。我添加了两个外键约束，并且还需要指定在连接时使用哪个关系。

class Role(Base):
    __tablename__ = "role"
    __table_args__ = ({'autoload':True, 'useexisting': True})


class Procedure(Base):
    __tablename__ = "procedure"
    __table_args__ = (
        sqlalchemy.ForeignKeyConstraint(['consult_role_uid'], ['role.role_uid']),
        sqlalchemy.ForeignKeyConstraint(['inform_role_uid'], ['role.role_uid']),
        {'autoload':True, 'useexisting': True})

Procedure.consult_role = sqlalchemy.orm.relationship(Role,
 primaryjoin="Procedure.consult_role_uid==Role.role_uid", foreign_keys=Role.role_uid)
Procedure.inform_role = sqlalchemy.orm.relationship(Role,
 primaryjoin="Procedure.inform_role_uid==Role.role_uid", foreign_keys=Role.role_uid)

consult_role = sqlalchemy.orm.aliased(Role, name="consult_role")
inform_role = sqlalchemy.orm.aliased(Role, name="inform_role")

query = session.query(
    Procedure.procedure_name, 
    consult_role.role_name.label("consult_role_name"),
    inform_role.role_name.label("inform_role_name")).join((consult_role, Procedure.consult_role), (inform_role, Procedure.inform_role))

这样生成了以下正确的SQL语句：

SELECT 
  `procedure`.procedure_name AS procedure_procedure_name, 
  consult_role.role_name AS consult_role_name, 
  inform_role.role_name AS inform_role_name 
FROM 
  `procedure` 
  INNER JOIN role AS consult_role ON `procedure`.consult_role_uid = consult_role.role_uid     
  INNER JOIN role AS inform_role ON `procedure`.inform_role_uid = inform_role.role_uid