Filter results by count of items in relationship

Question

Let's say I have these 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'd like to retrieve all Client with at least 1 Invoice and less than 20 Invoice. I would be expecting it to work like this :

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

Or even this would be nice :

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

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

Answer 1

Thanks to coworkers and code lying around, we got it working:

    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 it helps someone!