Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

flask-sqlalchemy max value of column

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?

like image 935
knk Avatar asked Apr 14 '14 18:04

knk


1 Answers

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()
like image 114
davidism Avatar answered Oct 18 '22 01:10

davidism