SQLAlchemy count function for nested join subquery

Question

I'm very new at SQLAlchemy and as you can see I have 3 models:

categories = db.Table(
    'categories',
    db.Column('post_id', db.Integer, db.ForeignKey('post.id')),
    db.Column('category_id', db.Integer, db.ForeignKey('category.id'))
)


class User(db.Model):
     id = db.Column(db.Integer, primary_key=True)
     nickname = db.Column(db.String(64), index=True, unique=True)
     email = db.Column(db.String(120), index=True, unique=True)
     posts = db.relationship('Post', backref='author', lazy='dynamic')
     about_me = db.Column(db.String(140))
     last_seen = db.Column(db.DateTime)


class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.String(140))
    timestamp = db.Column(db.DateTime)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    categories = db.relationship("Category",
                             secondary="categories",
                             backref="posts")



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

I can count how many post each user has by some subquery like this:

stmt = db.session.query(models.Post.user_id, func.count('*').label('post_count')).group_by(models.Post.user_id).subquery()

db.session.query(models.User, stmt.c.post_count).outerjoin(stmt, models.User.id==stmt.c.user_id).order_by(models.User.id)

but I can't figure it out the right query to calculate how many categories each users has.

Ilja Everilä · Accepted Answer · 2016-12-29 22:57:17Z

2

You could for example add a join to categories in your subquery and count distinct Post.ids and category_ids grouped by user_id:

stmt = db.session.query(
    models.Post.user_id,
    func.count(models.Post.id.distinct()).
        label('post_count'),
    func.count(models.categories.c.category_id.distinct()).
        label('category_count')
).\
    join(models.categories).\
    group_by(models.Post.user_id).\
    subquery()

db.session.query(models.User,
                 stmt.c.post_count,
                 stmt.c.category_count).\
    outerjoin(stmt, models.User.id==stmt.c.user_id).\
    order_by(models.User.id)

The post id's have to be distinct as well in case a post has multiple categories, which will manifest as multiple rows for the post after the join.

edited Dec 29, 2016 at 22:57

answered Dec 28, 2016 at 21:41

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

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

3 Comments

nim4n Over a year ago

thanks for your reply, but the query result is wrong,for example I have a user who participate on three category but your query return two for this user.

nim4n Over a year ago

I changed your code from "stmt.c.post_count" to "stmt.c.category_count" and It worked! thanks for your help

Ilja Everilä Over a year ago

Forgot to add/change the attribute in the final query, glad that you sorted it out yourself.

Collectives™ on Stack Overflow

SQLAlchemy count function for nested join subquery

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related