如何使用sqlalchemy对条目进行排名? [英] How can I rank entries using sqlalchemy?

问题描述

我创建了一个模型User，该用户具有列的得分和排名.我想定期更新User中所有用户的排名，以使得分最高的用户具有排名1，第二得分最高的用户排名2，依此类推.总之，在Flask-SQLAlchemy中是否可以有效地实现这一点?

谢谢！

顺便说一句，这是模型:

  app = Flask(__ name__)db = SQLAlchemy(应用程序)类User(db.Model):id = db.Column(db.Integer，primary_key = True)分数= db.Column(db.Integer)等级= db.Column(db.Integer) 

解决方案

那么，为什么要这样做，是因为您可以查询等级"而无需执行汇总查询，这样可以提高性能.特别是如果您想查看"456号用户的等级是多少?"而不用碰到每一行.

执行此操作的最有效方法是单个UPDATE.使用标准SQL，我们可以使用如下相关子查询:

 更新用户SET等级=(从用户AS u1那里选择计数(*)u1.score> user.score)+ 1 

某些数据库具有PG的UPDATE..FROM之类的扩展名，我对此经验不足，也许如果您可以进行UPDATE..FROM SELECT语句，则可以使用更有效的窗口函数立即获得排名，尽管我不太确定.

无论如何，带有SQLAlchemy的标准SQL看起来像:

从sqlalchemy.orm中的

 导入为别名从sqlalchemy导入功能u1 =别名(用户)subq = session.query(func.count(u1.id)).filter(u1.score> User.score).as_scalar()session.query(User).update({"rank":subq + 1}，synchronize_session = False) 

I have created a model User that has the columns score and rank. I would like to periodically update the rank of all users in User such that the user with the highest score has rank 1, second highest score rank 2, etc. Is there anyway to efficiently achieve this in Flask-SQLAlchemy?

Thanks!

btw, here is the model:

app = Flask(__name__)
db = SQLAlchemy(app)        
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    score = db.Column(db.Integer)
    rank = db.Column(db.Integer)

解决方案

Well as far as why one might do this, it's so that you can query for "rank" without needing to perform an aggregate query, which can be more performant. especially if you want to see "whats the rank for user #456?" without hitting every row.

the most efficient way to do this is a single UPDATE. Using standard SQL, we can use a correlated subquery like this:

UPDATE user SET rank=(SELECT count(*) FROM user AS u1 WHERE u1.score > user.score) + 1

Some databases have extensions like PG's UPDATE..FROM, which I have less experience with, perhaps if you could UPDATE..FROM a SELECT statement that gets the rank at once using a window function that would be more efficient, though I'm not totally sure.

Anyway our standard SQL with SQLAlchemy looks like:

from sqlalchemy.orm import aliased
from sqlalchemy import func
u1 = aliased(User)
subq = session.query(func.count(u1.id)).filter(u1.score > User.score).as_scalar()
session.query(User).update({"rank": subq + 1}, synchronize_session=False)

这篇关于如何使用sqlalchemy对条目进行排名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！