Flask-SQLAlchemy join across 3 models and a Table construct

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


When I try:

         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 = ?'

Best answer

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:

         filter(SalesManager.id == 1).all()