CODEWITHSUNDEEP
flaskflask-sqlalchemy
ankush981
ankush981

Reputation: 5417

Flask-SQLAlchemy get rows with non-zero count

I have the following two models in my database:

class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), unique=True)
    websites = db.relationship('Website', backref='category', lazy='dynamic')

class Website(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
    url = db.Column(db.String(1000), nullable=False)
    class_name = db.Column(db.String(50), nullable=False)

What I now want to do is write an expression for loading all the categories that have one or more websites associated with them. In SQL I can achieve this with a join and having, but I'm having trouble translating it into SQLAlchemy.

I first tried to import and use func, but then realized that it's not defined in Flask-SQLAlchemy. My next try was Category.query.having(Category.websites.count() > 1).all() but this gives me an error that AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Category.websites has an attribute 'count'. What's the right way to do this?

Any help will be appreiciated!

Upvotes: 0

Views: 818

Answers (1)

Anders Elmgren
Anders Elmgren

Reputation: 658

I believe you can still use join with group_by() and the having() func. Here's an example taken from the docs for having() that might be what you're looking for:

q = session.query(Website.id).\
            join(Website.category_id).\
            group_by(Category.id).\
            having(func.count(Website.url) > 0)

Upvotes: 1

Related Questions