CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
MattSom
MattSom

Reputation: 2377

Flask-SQLAlchemy: filter by count of relationship objects

My models with a one-to-many relationship:

class Entry(db.Model):
    __tablename__ = "entries"
    ...
    quantities = db.relationship("Quantity", back_populates="entry", lazy="dynamic")

class Quantity(db.Model):
    __tablename__ = "quantities"
    ...
    entry_id = db.Column(db.ForeignKey("entries.id"), nullable=True)
    entry = db.relationship("Entry", back_populates="quantities")

I have a query like this, works fine to find records where the collection of relationship objects is not empty:

# Get entries with not empty quantities
entries = Entry.query.filter(
    Entry.quantities.any()
).all()

I also see that I can query to their field values as well:

# Get entries with specific quantities
entries_with_specific_quantities = Entry.query.filter(
    Entry.quantities.any(),
    Entry.quantities.any(Quantity.some_field == "needed_value"),
    ~Entry.quantities.any(Quantity.some_field == "not_needed_value"),
).all()

But I can't figure out how can I query for the number of the relationship objects. I tried something like:

# Get entries with specific amount quantities
from sqlalchemy.sql.expression import func

entries_with_specific_amount_of_quantities = Entry.query.filter(
    func.count(Entry.quantities) == 2 # Or any int
).all()

Which returns ProgrammingError: (pymysql.err.ProgrammingError) (1111, 'Invalid use of group function')

How can I group the entries and get only the records having the exact amount of relationship objects?

Upvotes: 0

Views: 946

Answers (1)

Ian Wilson
Ian Wilson

Reputation: 9049

I don't think any is compatible with count because it is an aggregate function.

Here is a short solution using HAVING.

def entries_with_specific_amount_of_quantities(session, only_count):
    return session.query(
        Entry,
    ).outerjoin(
        Entry.quantities
    ).group_by(
        Entry.id
    ).having(
        func.count(Quantity.id) == only_count)

Upvotes: 2

Related Questions