0

I have the same question solved here, but am looking for a solution using Flask-SQLAlchemy or SQLAlchemy: Select row with most recent date per user

I need to print one row for each different user that has the highest id, so this example table would produce the following output:

id  user    time    io
1   9   1370931202  out
2   9   1370931664  out
3   6   1370932128  out
4   12  1370932128  out
5   12  1370933037  in

output:

id  user    time    io
2   9   1370931664  out
3   6   1370932128  out
5   12  1370933037  in

models.py

class Example(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user = db.Column(db.String, default=None, nullable=True)
    time = db.Column(db.DateTime, default=datetime.utcnow, nullable=True)
    io = db.Column(db.String, default=None, nullable=True)

my query does not find the correct row for each user:

result = Example.query.sort_by(Example.id.desc()).group_by(Example.user)

Is there any way to fix my query or translate the SQL solution to Flask-SQLAlchemy, Thanks.

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
The Picard
  • 51
  • 4
  • What DB are you using? The linked Q/A uses MySQL, but is that true for you? – Ilja Everilä Mar 02 '18 at 07:55
  • I'm using SQLite, but I'd like it to be compatible with MySQL after I'm done developing. Right now I have the linked solution working as a raw query, but I'd really like to do it with flask-sqlalchemy to keep everything consistent. Thanks. – The Picard Mar 02 '18 at 15:45

0 Answers0