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',ForeignKey('sales_manager.id'))
)

我希望能够为SalesManager获取所有客户,这意味着所有客户在任何分支中具有statemented_branch_id与SalesManager.branches关系.

我的查询看起来有点像这样：

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

这显然是不对的.

如何获得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,)

解决方法

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()

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!