无效请求错误：找不到FROM子句进行连接

Question

我定义的员工和请假申请表的模型如下，这两个表是：

# Class for creating employee table
class Employee(Base):
__tablename__ = "employee"
id = Column(Integer, primary_key=True, nullable=False)
name = Column(String(30), nullable=False)
designation = Column(Integer,
                   ForeignKey("designation.id",
                              use_alter=True,
                              name="fk_designation",
                              onupdate="CASCADE",
                              ondelete="RESTRICT"))
team = Column(Integer, ForeignKey("team.id",
                                use_alter=True,
                                name="fk_team",
                                onupdate="CASCADE",
                                ondelete="RESTRICT"))
workstation_id = Column(String(30), nullable=False)
phone_extension = Column(String(30), nullable=False)
leave_eligibility_id = Column(Integer,
                            ForeignKey("leaves_eligibility.id",
                                       use_alter=True,
                                       name="fk_leave_eligibility", 
                                       onupdate="CASCADE",
                                       ondelete="RESTRICT"))
manager_id = Column(Integer, ForeignKey("employee.id",
                                      use_alter=True,
                                      name="fk_manager_id",
                                      onupdate="CASCADE",
                                      ondelete="RESTRICT"))
leave_approver_id = Column(Integer,
                         ForeignKey("employee.id",
                                    use_alter=True,
                                    name="fk_leaveapprover_id",
                                    onupdate="CASCADE",
                                    ondelete="RESTRICT"))
leave_recommender_id = Column(Integer,
                            ForeignKey("employee.id",
                                       use_alter=True,
                                       name="fk_leaverecommender_id",
                                       onupdate="CASCADE",
                                       ondelete="RESTRICT"))


def __init__(self, name, designation,team,workstation_id,     phone_extension,leave_eligibility_id,manager_id,leave_approver_id,leave_recommender_id):
self.name = name
self.designation = designation
self.team = team
self.workstation_id = workstation_id
self.phone_extension = phone_extension
self.leave_eligibility_id = leave_eligibility_id
self.manager_id = manager_id
self.leave_approver_id = leave_approver_id
self.leave_recommender_id = leave_recommender_id


# Class for creating team table
class LeavesApplied(Base):
__tablename__ = "leaves_applied"
id = Column(Integer, primary_key=True, nullable=False)
employee_id = Column(Integer,
                   ForeignKey("employee.id",
                              use_alter=True,
                              name="fk_emp_id",
                              onupdate="CASCADE",
                              ondelete="RESTRICT"))
from_date = Column(Date, nullable=False)
to_date = Column(Date, nullable=False)
half_day = Column(TINYINT(1))
type_id = Column("type_id",
               mysql.ENUM("CASUAL_LEAVE",
                          "SICK_LEAVE",
                          "PRIVILEGED_LEAVE",
                          "ON_DUTY",
                          "AUTHORISED_LOSS_OF_PAY"))
decided_by_id = Column(Integer,
                     ForeignKey("employee.id",
                                use_alter=True,
                                name="fk_decided_by_id",
                                onupdate="CASCADE",
                                ondelete="RESTRICT"))
reason = Column(TEXT)
status = Column("status",
              mysql.ENUM("RECOMMENDED",
                         "DECLINED",
                         "APPROVED",
                         "PENDING"))
recommended_by_id = Column(Integer,
                         ForeignKey("employee.id",
                                    use_alter=True,
                                    name="fk_recommended_by_id",
                                    onupdate="CASCADE",
                                    ondelete="RESTRICT"))

children=relationship("Employee", 
                         backref=backref('parent',remote_side=[employee_id]),
                     foreign_keys='LeavesApplied.employee_id',

                     # Children will be represented as a dictionary
                     # On the attribute of manager_id
                     collection_class=attribute_mapped_collection(
                                      'id'),)

def   __init__(self,employee_id,from_date,to_date,half_day,type_id,decided_by_id,reason,status,rec    ommended_by_id):
self.employee_id=employee_id
self.from_date = from_date
self.to_date = to_date
self.half_day = half_day
self.type_id = type_id
self.decided_by_id=decided_by_id
self.reason = reason
self.status = status
self.recommended_by_id=recommended_by_id

我正在执行下面的查询

rows=self.db.query(LeavesApplied,Employee)
.join(Employee)
.filter(LeavesApplied.employee_id== Employee.id)
.filter(Employee.id==1255).all()

但是出现了一个错误：
InvalidRequestError: 找不到可以连接的FROM子句。
尝试连接到，但出现了：无法确定'leaves_applied'和'employee'之间的连接；这两个表之间有多个外键约束关系。请明确指定这个连接的'onclause'。

Answer 1

这个错误信息是完全正确的。从你提供的代码来看，有三种可能的方式可以把这两个表连接起来：

• LeavesApplied.employee_id
• LeavesApplied.decided_by_id
• LeavesApplied.recommended_by_id

而这正是sqlalchemy抱怨的地方，因为它不知道你想用哪个relationship或者oncluause来进行连接。

用下面的代码替换你的代码应该能解决这个问题。在这里，我假设你想用LeavesApplied.employee_id来连接，因此我把filter这一部分注释掉了，因为在这种情况下它是多余的：

rows = (session.query(LeavesApplied,Employee)
        .join(Employee, LeavesApplied.children) #.join(Employee)
        #.filter(LeavesApplied.employee_id== Employee.id)
        .filter(Employee.id==1255).all()
        )