Flask-SQLAlchemy:根据关系对象的计数进行筛选

Question

我的一对多关系模型:

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

我有一个这样的查询，可以很好地找到关系对象的集合不为空的记录:

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

我还看到我可以查询他们的字段值:

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

但是我不知道如何查询关系对象的数量。我试过这样写:

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

返回ProgrammingError: (pymysql.err.ProgrammingError) (1111, 'Invalid use of group function')

我如何对条目进行分组并只获得具有确切数量的关系对象的记录?

Answer 1

我不认为any与count兼容，因为它是一个聚合函数。

这里是一个使用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)