sqlalchemy怎么通过外键排序。

这里有两张一对多的关系表,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表是用户的评论, 怎么按用户的评论数做降序排列?

阅读 4.7k
3 个回答
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。

设想是在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,可以扔掉那些所谓的外键关联的写法。

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

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'raymond@dabshots.org', u'shirley', 100L)
(u'cheryl@quaxo.net', u'rachel', 100L)
(u'debra@yoveo.net', u'carol', 100L)
(u'kathleen@kaymbo.com', u'nancy', 100L)
(u'melissa@youtags.org', u'amy', 100L)
(u'margaret@riffwire.net', u'kimberly', 100L)
...
...

但是没有排序:

In [131]: for i in User.query.all():
     ...:     print i.email, i.username, i.comments.count()
     ...:     
raymond@dabshots.org shirley 2
margaret@riffwire.net kimberly 6
brenda@realbuzz.com ashley 1
lillian@devpulse.name julia 4
linda@babbleopia.biz mildred 3
helen@mycat.name douglas 4
kathleen@kaymbo.com nancy 1
teresa@zoombeat.name melissa 5
evelyn@skalith.com stephanie 6
...
...
撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
推荐问题