CODEWITHSUNDEEP
pythonsqlsqlalchemyflaskflask-sqlalchemy
user3210323
user3210323

Reputation: 41

How can I rank entries using sqlalchemy?

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)

Upvotes: 4

Views: 3326

Answers (2)

zzzeek
zzzeek

Reputation: 75127

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)

Upvotes: 5

Paolo Casciello
Paolo Casciello

Reputation: 8202

Just cycle on all your users:

users = User.query.order_by(User.score._desc()).all() #fetch them all in one query
for (rank, user) in enumerate(users):
    user.rank = rank + 1 #plus 1 cause enumerate starts from zero

db.session.commit()

Upvotes: 0

Related Questions