flask-sqlalchemy max value of column

Question

Lets say I have a user model as such

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)
    numLogins = db.Column(db.Integer)

    def __init__(self, username, email):
        self.username = username
        self.email = email

    def __repr__(self):
        return '<User %r>' % self.username

How do I query the records for user with maximum logins?

Answer 1

You could order by numLogins and take the first result, but that won't work if multiple people have the same number. Instead, find the max number, then find all users with that number.

from sqlalchemy import func

max_logins = db.session.query(func.max(User.numLogins)).scalar()
users = db.session.query(User).filter(User.numLogins == max_logins).all()

You could reduce this to one query.

sub = db.session.query(func.max(User.numLogins).label('ml')).subquery()
users = db.session.query(User).join(sub, sub.c.ml == User.numLogins).all()