SQLAlchemy Returning Parents and Children Based on Filter on Child

Viewed 23

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 related Accounts where Account.user_id == user.id
  • For each returned Broker, only include the related Accounts where Account.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?

0 Answers
Related