Flask-SQLAlchemy get rows with non-zero count

Question

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!

@dirn Thanks a lot! I still get an error, though. Have updated my question. — ankush981
– ankush981, Commented Mar 12, 2016 at 17:31

Anders Elmgren · Accepted Answer · 2016-03-12 20:15:44Z

1

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)

edited Mar 12, 2016 at 20:15

answered Mar 12, 2016 at 16:59

Anders Elmgren

6585 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ankush981 Over a year ago

Is it not possible to do with Flask-SQLAlchemy? I don't want to use SQLAlchemy as it might cause problems later for not being aware of my app/

dirn Over a year ago

Flask-SQLAlchemy is just a wrapper around SQLAlchemy. Website.query is just a convenience that wraps the session. There's nothing wrong with using the session directly.

Collectives™ on Stack Overflow

Flask-SQLAlchemy get rows with non-zero count

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related