Consider the following Flask SQLAlchemy models (User excluded for brevity):
class Broker(Model):
__tablename__ = 'brokers'
id = Column(Integer, primary_key=True)
name = Column(String)
# relationships
accounts = relationship("Account", back_populates="broker")
class Account(Base):
__tablename__ = 'accounts'
id = Column(Integer, primary_key=True)
broker_id = Column(Integer, ForeignKey('brokers.id'))
user_id = Column(Integer, ForeignKey('users.id'))
alias = Column(String)
# relationships
user_id = relationship("User", back_populates="accounts")
broker = relationship("Broker", back_populates="accounts")
My requirement is the following:
- Return the
Brokers that have relatedAccounts whereAccount.user_id == user.id - For each returned
Broker, only include the relatedAccounts whereAccount.user_id == user.id
Given the following test data
brokers
id | name
---------------------
1 | My first broker
2 | My second broker
3 | My third broker
accounts
id | broker_id | user_id | alias
---------------------------------------------
1 | 1 | 1 | My first account
2 | 1 | 1 | My first account
3 | 2 | 1 | My second account
4 | 1 | 2 | My account one
5 | 2 | 2 | My account two
A filter for Account.user_id == 1 should return a list of two Broker objects and associated Accounts:
<Broker 1> -> [<Account 1>, <Account 2>]<Broker 2> -> [<Account 3>]
I've tried Broker.query.join(Account).filter(Account.user_id == 1).all() which returns only Broker 1 and Broker 2 as needed. However the returned Broker objects include all the Account objects related to the given Broker, disregarding the filter on Account.user_id. My requirement for the returned Broker object is to only include the associated Account objects that match the passed Account.user_id.
How do I write such a query?