sqlalchemy怎么通过外键排序。

Question

这里有两张一对多的关系表，User和Comment。

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(32), unique=True, index=True)
    username = db.Column(db.String(64), unique=True, index=True)
    password_hash = db.Column(db.String(128))
    timestamp = db.Column(db.DateTime, default=datetime.utcnow)
    comments = db.relationship('Comment', backref='author', lazy='dynamic')

class Comment(db.Model):
    __tablename__ = 'comments'
    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.Text)
    timestamp = db.Column(db.DateTime, default=datetime.utcnow)
    star = db.Column(db.Boolean, default=False)
    author_id = db.Column(db.Integer, db.ForeignKey('users.id'))

User表是用户， Comment表是用户的评论， 怎么按用户的评论数做降序排列？

Answer 1

import sqlalchemy

comment_sub = Comment.query.group_by(Comment.author_id).with_entities(Comment.author_id, sqlalchemy.func.count(Comment.author_id).label('count')).subquery()
result = db.session.query(User, comment_sub.c.count).join(comment_sub, User.id == comment_sub.c.author_id).order_by(comment_sub.c.count.asc()).all()

需要哪些参数，自行在with_entities和下面的query中添加。
更改排序方式的话，自行更改asc或desc。

Answer 2

设想是在flask项目中，

from sqlalchemy import func

sbq = db.session.query(User.email, User.username, func.count(Comment.author_id).label("c_nums")).filter(User.id==Comment.author_id).subquery()

# data = sbq.query.order_by(sbq.c.c_nums.desc()).distinct().all()

data = db.session.query(User.email, User.username, sbq.c.c_nums).order_by(sbq.c.c_nums.desc()).distinct().all()

for d in data:
    print(d.email, d.username, d.c_nums)

用好sqlalchemy，可以扔掉那些所谓的外键关联的写法。

Answer 3

@藕丝空间 结果是出来了。

In [128]: sbq = db.session.query(User.email, User.username, func.count(Comment.author_id).label("c_nums
     ...: ")).filter(User.id==Comment.author_id).subquery()

In [129]: data = db.session.query(User.email, User.username, sbq.c.c_nums).order_by(sbq.c.c_nums.desc()
     ...: ).distinct().all()

In [130]: for d in data:
     ...:     print(d.email, d.username, d.c_nums)
     ...:     
(u'[e-mail]', u'shirley', 100L)
(u'[e-mail]', u'rachel', 100L)
(u'[e-mail]', u'carol', 100L)
(u'[e-mail]', u'nancy', 100L)
(u'[e-mail]', u'amy', 100L)
(u'[e-mail]', u'kimberly', 100L)
...
...

但是没有排序：

In [131]: for i in User.query.all():
     ...:     print i.email, i.username, i.comments.count()
     ...:     
[e-mail] shirley 2
[e-mail] kimberly 6
[e-mail] ashley 1
[e-mail] julia 4
[e-mail] mildred 3
[e-mail] douglas 4
[e-mail] nancy 1
[e-mail] melissa 5
[e-mail] stephanie 6
...
...