Filter results by the number of elements in the relationship

Question

Let's say I have two models:

def Client(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    invoices = db.relationship('Invoice', backref='client')


def Invoice(db.Model):
    id = db.Column(db.Integer, primary_key=True)

I would like to get everything Clientwith at least 1 Invoiceand less than 20 Invoice. I expected it to work as follows:

Client.query.join(Invoice).filter(and_(Invoice.count() > 1, Invoice.count() <= 20))

Or even that would be nice:

Client.query.join(Invoice).filter(and_(count_(Invoice) > 1, count_(Invoice) <= 20))

But of course, it cannot be so simple. .count()can't work from there obviously and i can't find count_()in sqlalchemy.func.

Answer 1

Thanks to the staff and the code that lie around, we got it:

    client = Client.query\
        .outerjoin(Client.invoices)\
        .group_by(Client)\
        .having(\
             func.and_(\
                 func.count_(Client.invoices) >= 1)\
                 func.count_(Client.invoices) <= 20)\
             )
        ).all()

I hope this helps someone!