请教各位一个关于 Flask-sqlalchemy group_by 的问题

class Post(db.Model):
    __tablename__ = 'post'

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(32), unique=True)
    body = db.Column(db.Text)
    author_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))

有一个分类表模型是

class Category(db.Model):
    __tablename__ = 'category'

    id = db.Column(db.Integer, primary_key=True)
    posts = db.relationship('Post', backref='postcategory', lazy='dynamic')
    categoryName = db.Column(db.String(32), unique=True, index=True)

我想查出category 文章分类每个分类有多少篇文章

我有 sql 能查,语句是

SELECT COUNT(id),(SELECT categoryName from category WHERE post.category_id=category.id) as N FROM post GROUP BY category_id

请教一下各位大神,如果用 flask-sqlalchemy 怎么写呀谢谢各位了

5 条回复 • 2017-06-05 09:26:51 +08:00

ossicee

2017-06-01 09:26:27 +08:00 via Android

💪

fangzq

2017-06-01 11:09:28 +08:00

可以试试这个：

```python
from sqlalchemy import func
db.session.query(func.count(Post.id), Category.categoryName).select_from(Category).\
join(Post, Category.id == Post.category_id).group_by(Post.category_id).all()
```

ossicee

2017-06-01 15:56:08 +08:00 via Android

@fangzq 谢谢您，我等下回家测试一下，谢谢您帮助我

ossicee

2017-06-03 10:50:02 +08:00

@fangzq 谢谢您，，。。原来我查资料，关键字全是 flask-sqlalchemy .原来用 sqlalchemy 就可以完成。谢谢您

fangzq

2017-06-05 09:26:51 +08:00

解决了就好，不客气