Filter nested schema rows in query result - Sqlalchemy (Marshmallow)

Question

Consider the following: A set of Accounts that have a Status that can be ACTIVE or DELETED. A set of Entries for each Account that also have a status ACTIVE/DELETED.

Here is an example of the data object for 2 accounts:

accounts = [{
  "account_id": 1,
  "status": {
    "status_id": 1,
    "status_name": "active"
  },
  "account_entries": [{
    "entry_id": 1,
    "status": {
      "status_id": 1,
      "status_name": "active"
    }
  }, {
    "entry_id": 2,
    "status": {
      "status_id": 2,
      "status_name": "deleted"
    }
  }]
}, {
  "account_id": 2,
  "status": {
    "status_id": 2,
    "status_name": "deleted"
  },
  "account_entries": [{
    "entry_id": 1,
    "status": {
      "status_id": 1,
      "status_name": "active"
    }
  }]
}]

To simplify the above:

accounts:
  1: active
  entries:
    1: active,
    2: deleted
  2: deleted
  entries:
    1: active

Would like to query accounts and their entries, filtered by active status - to return an object that does not include any deleted accounts or entries.

accounts:
  1: active
  entries:
    1: active

To filter the active accounts, currently using:

# query all active accounts
all_accounts = Account.query.filter(Account.status_id == "1")

Is it possible to extend the query to apply the filter to the entries as well?

Here are the schemas for: account, entry, status - in models.py

class Account(db.Model):
    __tablename__ = 'account'

    account_id = db.Column(db.Integer, primary_key=True)

    status_id = db.Column(db.Integer, db.ForeignKey('status.id'))
    status = db.relationship('Status', backref='accounts')

    def __repr__(self):
        return '<Account {}>'.format(self.id)

class Entry(db.Model):
    __tablename__ = 'entry'

    id = db.Column(db.Integer, primary_key=True)

    status_id = db.Column(db.Integer, db.ForeignKey('status.id'), nullable=False)
    status = db.relationship('Status', backref='entries')

    account_id = db.Column(db.Integer, db.ForeignKey('account.id'), nullable=False)
    account = db.relationship('Account', foreign_keys=[account_id], backref=db.backref('account_entries'))

    def __repr__(self):
        return '<Entry {}>'.format(self.id)

class Status(db.Model):
    __tablename__ = 'status'

    id = db.Column(db.Integer, primary_key=True)

    name = db.Column(db.String(100), nullable=False)

    def __repr__(self):
        return '<Status {}>'.format(self.id)

And those are the schemas setup in views.py:

class StatusSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = Status


class EntrySchema(ma.SQLAlchemyAutoSchema):
    status = ma.Nested(StatusSchema)
    account = ma.Nested(lambda: AccountSchema(only=("id", "name")))

    class Meta:
        model = Entry


class AccountSchema(ma.SQLAlchemyAutoSchema):
    account_entries = ma.List(ma.Nested(EntrySchema(exclude=("account",))))
    status = ma.Nested(StatusSchema)

    class Meta:
        model = Account

score 0 · Answer 1 · answered Feb 15 '21 at 19:08

I had a similar problem and solved it by following V. Chikunov answer's here

The thing that worked for me was adding the contains_eager() to my query; without it I was getting all the rows in my nested schema like it was ignoring the filter statment.

In your case, I think your query should be:

all_accounts = Account.query.join(Status) \
                      .filter(Status.name == "active") \
                      .options(contains_eager(Status.name)) \
                      .all()

Filter nested schema rows in query result - Sqlalchemy (Marshmallow)

1 Answers1