Flask-SQLAlchemy combined in 3 models and table design

Question

I have 3 models:

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) 

And table design:

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

I want to get all Customers for SalesManager , which means all customers who have statemented_branch_id in any of Branch es SalesManager.branches .

My query looks something like this:

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

This is obviously not the case.

How can I get all Customers for SalesManager ?

Update

When I try:

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

I get an OperationalError statement:

 *** 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,) 

Answer 1

I needed to add backref to my SalesManager model, which allows SQLAlchemy to figure out how to get from SalesManager to the branch.

 class SalesManager(Model): __tablename__ = 'sales_manager' id = Column(Integer, primary_key=True) branches = relationship( 'Branch', secondary=sales_manager_branches, backref="salesmanagers") 

And build the query like this:

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

Answer 2

Try:

 SalesManager.query \ .join(Branch) \ .join(Customer) \ .filter(SalesManager.id == 123) 

You may need to provide explicit on parameters using the second join argument, or you may need to explicitly add a mapping table, but either way you try to do the following:

 SELECT SM.* FROM sales_manager SM JOIN sales_manager_branches SMB ON SM.id = SMB.sales_manager_id JOIN branch B ON SMB.branch_id = B.id JOIN customer C ON B.id = C.statemented_branch_id WHERE -- Conditions go here