python - Flask-SQLAlchemy跨3个模型和一个Table构造进行联接

我有3种型号:

class Customer(Model):
    __tablename__ = 'customer'

    id = Column(Integer, primary_key=True)
    statemented_branch_id = Column(Integer, ForeignKey('branch'))
    ...

class Branch(Model):
    __tablename__ = 'branch'

    id = Column(Integer, primary_key=True)
    ...

class SalesManager(Model):
    __tablename__ = 'sales_manager'

    id = Column(Integer, primary_key=True)
    branches = relationship('Branch', secondary=sales_manager_branches)

和一个表构造:

sales_manager_branches = db.Table(
    'sales_manager_branches',
    Column('branch_id', Integer, ForeignKey('branch.id')),
    Column('sales_manager_id', Integer, ForeignKey('sales_manager.id'))
)

我希望能够获取Customers的所有SalesManager，这意味着在任何statemented_branch_id中具有Branch的所有客户都具有SalesManager.branches关系。

我的查询看起来像这样:

branch_alias = aliased(Branch)
custs = Customer.query.join(branch_alias, SalesManager.branches).\
        filter(Customer.statemented_branch_id == branch_alias.id)

这显然是不对的。

如何获得Customers的所有SalesManager？

更新

当我尝试时:

Customer.query.\
         join(Branch).\
         join(SalesManager.branches).\
         filter(SalesManager.id == 1).all()

我收到一个OperationalError:

*** OperationalError: (OperationalError) ambiguous column name: branch.id u'SELECT
customer.id AS customer_id, customer.statemented_branch_id AS
customer_statemented_branch_id \nFROM customer JOIN branch ON branch.id
customer.statemented_branch_id, "SalesManager" JOIN sales_manager_branches AS
sales_manager_branches_1 ON "SalesManager".id = sales_manager_branches_1.sdm_id JOIN
branch ON branch.id = sales_manager_branches_1.branch_id \nWHERE "SalesManager".id = ?'
(1,)

最佳答案

我需要在backref模型中添加SalesManager，以允许SQLAlchemy弄清楚如何从SalesManager到分支。

class SalesManager(Model):
    __tablename__ = 'sales_manager'

    id = Column(Integer, primary_key=True)
    branches = relationship(
        'Branch', secondary=sales_manager_branches, backref="salesmanagers")

并像这样构造查询:

Customer.query.\
         join(Branch).\
         join(Branch.salesmanagers).\
         filter(SalesManager.id == 1).all()

关于python - Flask-SQLAlchemy跨3个模型和一个Table构造进行联接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/17801747/