我有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,)
解决方法
我需要在我的SalesManager模型中添加一个backref,允许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()