Flask-SQLAlchemy join across 3 models and a Table construct

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 a Table construct:

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 be able to get all Customers for a SalesManager, which means all Customers that have a statemented_branch_id in any of the Branches the SalesManager.branches relationship.

My query is looking something a little like this:

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

Which is obviously not right.

How can I get all Customers for a SalesManager?

Update

When I try:

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

I get an 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,)

Answer 1

I needed to add a backref to my SalesManager model that allows SQLAlchemy to figure out how to get from SalesManager to branch.

class SalesManager(Model):
    __tablename__ = 'sales_manager'

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

And construct 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 params, via the second argument to join, or you may need to explicitly add the mapping table in - but in either case what you are trying to do is 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