CODEWITHSUNDEEP
pythonflasksqlalchemyflask-sqlalchemy
SeanPlusPlus
SeanPlusPlus

Reputation: 9033

Filter records in SQLAlchemy object if the record has foreign key objects related to it

Using the example from the Flask-SQLAlchemy Quickstart:

from datetime import datetime

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(80))
    body = db.Column(db.Text)
    pub_date = db.Column(db.DateTime)

    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
    category = db.relationship('Category',
        backref=db.backref('posts', lazy='dynamic'))

    def __init__(self, title, body, category, pub_date=None):
        self.title = title
        self.body = body
        if pub_date is None:
            pub_date = datetime.utcnow()
        self.pub_date = pub_date
        self.category = category

    def __repr__(self):
        return '<Post %r>' % self.title


class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return '<Category %r>' % self.name

I've re-created the categories and posts the same way as the example:

>>> py = Category('Python')
>>> p = Post('Hello Python!', 'Python is pretty cool', py)
>>> db.session.add(py)
>>> db.session.add(p)
>>> py.posts.all()
[<Post 'Hello Python!'>]

Then I add a new category:

>>> ru = Category('Ruby')
>>> db.session.add(ru)

How do I construct a query to get all categories that have a post.

Would like to do something like this, but use an SQLAlchemy filter instead:

>>> categories_with_post = []
>>> For c in Category.query.all():
...     if len(c.posts.all()) > 0:
...         categories_with_post.append(c)

Upvotes: 1

Views: 350

Answers (1)

multivac
multivac

Reputation: 146

Since all you want is Categories that have Posts, you can try something like this:

from sqlalchemy import distinct
db.session.query(distinct(Category.id), Category).join(Post.category).all()

The join will prevent any category without posts to be returned.

Notice the usage of the distinct function, it will prevent categories to be repeated whenever more than one post points to the same category. However it can't be used on the Category class itself so I used it in the Category.id property and added Category as a second parameter to obtain the whole object as well. It will return a list of (id, Category) tuples.

Upvotes: 2

Related Questions