Flask-SQLAlchemy join across 3 models and a Table construct

flask, python, sqlalchemy

Solution

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

Problem

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 `Branch`es 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,) ```

Original source