how to "translate" this sql line to work on flask/sqlalchemy?

I need some help to translate a sql command to work on Flask. I am trying to filter the results of a query. This command below do what I need in mysql:

select t.date, t.name from `capacitydata`.`allflash_dev` t
inner join (
select name, max(date) as MaxDate
    from `capacitydata`.`allflash_dev` group by name)
    tm on t.name = tm.name and t.date = tm.MaxDate;

But I am trying without success to do the same on Flask. So far I got the query working, but showing all the lines

# Creating Models
class Block(db.Model):
    __tablename__ = "allflash_dev"
 
    index = db.Column(db.Date, nullable=False, unique=True, primary_key=True)
    date = db.Column(db.Date, nullable=False)
    name = db.Column(db.String(45), nullable=False)
    raw = db.Column(db.String(45), nullable=False)
    free = db.Column(db.String(45), nullable=False)
    frep = db.Column(db.String(45), nullable=False)
    util = db.Column(db.String(45), nullable=False)
    utip = db.Column(db.String(45), nullable=False)

def create_db():
    with app.app_context():
        db.create_all()

# Home route
@app.route("/")
def block():
    details = Block.query.order_by(Block.date.desc())
    return render_template("block.html", details=details)

Thanks in advance.

I tried something like this but didn't work:

details = db.select([Block.name, db.func.max(Block.date)]).group_by(Block.date, Block.name)

Solution

You can use this. Use statement with your flask sqlalchemy session / query.

from sqlalchemy import func, select
from sqlalchemy.orm import aliased

allflash_dev = aliased(Block)
tm = (
    select(allflash_dev.name, func.max(allflash_dev.date).label("MaxDate"))
    .group_by(allflash_dev.name)
    .subquery()
)
statement = select(Block.date, Block.name).join(
    tm, (Block.name == tm.c.name) & (Block.date == tm.c.MaxDate)
)

This generates the following sql.

SELECT 
  allflash_dev.date, 
  allflash_dev.name 
FROM 
  allflash_dev 
  INNER JOIN (
    SELECT 
      allflash_dev_1.name AS name, 
      max(allflash_dev_1.date) AS `MaxDate` 
    FROM 
      allflash_dev AS allflash_dev_1 
    GROUP BY 
      allflash_dev_1.name
  ) AS anon_1 ON allflash_dev.name = anon_1.name 
  AND allflash_dev.date = anon_1.`MaxDate`