使用SQLAlchemy如何返回多个列的计数

Question

我正在尝试运行这样的查询：

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

如果不把 comments 和 commenttypes 连接起来来获取 name 列，我可以这样做：

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

但是，如果我尝试做类似的事情，我得到的结果就不对了：

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

我对结果有两个问题：

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

问题：

• False 的结果不正确
• 计数也不对

我期望的结果是：

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

我该如何调整我的命令，以获取正确的 name 值和正确的计数呢？

我的模型定义如下：

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)

Answer 1

首先在一个子查询中计算数量，然后在最终查询中将其连接起来。

# 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()

Answer 2

下面的内容应该可以解决你的问题。你需要把两个模型“连接”起来，并且把所有非聚合的列都添加到一个“分组”条件中。(我知道并不是所有的关系型数据库管理系统都严格要求这样做，但我更喜欢这样做以确保安全)

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