Using SQLAlchemy, how can I return a count with multiple columns

Question

I am attempting to run a query like this:

SELECT 
    comment_type_id, name, count(comment_type_id) 
FROM
    comments, commenttypes
WHERE 
    comment_type_id=commenttypes.id
GROUP BY 
    comment_type_id

Without the join between comments and commenttypes for the name column, I can do this using:

session.query(Comment.comment_type_id,func.count(Comment.comment_type_id)).group_by(Comment.comment_type_id).all()

However, if I try to do something like this, I get incorrect results:

session.query(Comment.comment_type_id, Comment.comment_type, func.count(Comment.comment_type_id)).group_by(Comment.comment_type_id).all()

I have two problems with the results:

(1, False, 82920)
(2, False, 588)
(3, False, 4278)
(4, False, 104370)

Problems:

The False is not correct
The counts are wrong

My expected results are:

(1, 'Comment Type 1', 13820)
(2, 'Comment Type 2', 98)
(3, 'Comment Type 2', 713)
(4, 'Comment Type 2', 17395)

How can I adjust my command to pull the correct name value and the correct count?

My model definitions look like this:

class Comment(db.Model):
    __tablename__ = 'comments'
    id = Column(Integer, primary_key=True, unique=True)
    comment_type_id = Column(Integer, ForeignKey('commenttypes.id'), nullable=False, index=True)
    comment_type = relationship('CommentType', backref='comments')

class CommentType(db.Model):
    __tablename__ = 'commenttypes'
    id = Column(Integer, primary_key=True, unique=True)
    name = Column(String(50, convert_unicode=True), nullable=False)

van · Accepted Answer · 2014-06-03 07:21:33Z

2

Below should do it. You need to join two models as well as add all non-aggregate columns to a group_by clause (I know it is not strictly required for all RDBMS, but i prefer to be safe)

qry = (session.query(CommentType.id, CommentType.name,
            func.count(CommentType.id).label('cnt'))
        .select_from(CommentType).join(Comment)
        .group_by(CommentType.id, CommentType.name)
        )

answered Jun 3, 2014 at 7:21

van

77.6k13 gold badges174 silver badges179 bronze badges

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

Comments

davidism · Accepted Answer · 2014-06-02 16:55:38Z

1

First calculate the counts in a subquery, then join on that in the final query.

# calculate the comment counts for each type in a subquery
sub = session.query(
    CommentType.id,
    func.count(Comment.id).label('count')
).join(CommentType.comments
).group_by(CommentType.id
).subquery()

# select types and counts by joining on subquery
types = session.query(CommentType, sub.c.count
).join((sub, sub.c.id == CommentType.id)
).all()

edited Jun 2, 2014 at 16:55

answered Jun 2, 2014 at 16:33

davidism

128k31 gold badges417 silver badges348 bronze badges

Collectives™ on Stack Overflow

Using SQLAlchemy, how can I return a count with multiple columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related