Three table join with max value in SQLAlchemy

Question

I have three tables as follows (there are additional fields, but irrelevant here):

Class Application():
  id: int = db.Column(db.Integer, primary_key=True, autoincrement=True)
  name: str = db.Column(db.String(10), unique=true, index=True, nullable=False)

  appmappings: List[AppEnvMapping] = db.orm.relationship("AppEnvMapping", order_by=AppEnvMapping.deploy_date.desc(), back_populates='application', lazy='joined')

Class Environment():
  id: int = db.Column(db.Integer, primary_key=True, autoincrement=True)
  name: str = db.Column(db.String(10), unique=true, index=True, nullable=False)

  envmappings: List[AppEnvMapping] = db.orm.relationship("AppEnvMapping", order_by=AppEnvMapping.deploy_date.desc(), back_populates='environment', lazy='joined')

Class AppEnvMapping():
  id: int = db.Column(db.Integer, primary_key=True, autoincrement=True)
  app_id: int = db.Column(db.Integer, db.ForeignKey('applications.id'), index=True, nullable=False)
  env_id: int = db.Column(db.Integer, db.ForeignKey('environments.id'), index=True, nullable=False)
  deploy_date: datetime.datetime = db.Column(db.DateTime, index=True, nullable=False)

  application = db.orm.relationship('Application', back_populates='appmappings', lazy='joined')
  environment = db.orm.relationship('Environment', back_populates='appmappings', lazy='joined')

Simple pretty far, and I'm able to join them and get a full list of everything with relative ease. However, what I really need is to send a query that returns only the rows with the latest deploy_date for each app_id, env_id combo. So, for instance, if the AppEnvMapping table had the following data:

id | app_id | env_id | deploy_date
1  | 1      | 1      | 01-01-2021
2  | 1      | 1      | 02-01-2021
3  | 1      | 2      | 01-10-2021
4  | 2      | 1      | 02-04-2021
5  | 2      | 1      | 04-15-2021

Then the query should return these rows:

id | app_id | env_id | deploy_date
2  | 1      | 1      | 02-01-2021
3  | 1      | 2      | 01-10-2021
5  | 2      | 1      | 04-15-2021

The kicker here being that I need the corresponding Application and Environment data as well. I can do this with SQL, but I can't seem to figure out the syntax to do this in SQLAlchemy. The following SQL gives me the data I want:

SELECT a.id, a.name, ae.env_id, e.name, ae.daploy_date
  FROM applications AS a
  INNER JOIN appenvmappings AS ae
    ON ae.app_id = a.id
  INNER_JOIN (SELECT id, max(deploy_date) max_date
                FROM appenvmappings
                GROUP_BY app_id, env_id) maxlist
    ON ae.id = maxlist.ie
      AND ae.deploy_date = maxlist.max_date
  INNER_JOIN environments AS e
    ON ae.env_id = e.id

The closest I've gotten in SQLAlchemy is this:

maxlist = session.query(AppEnvMapping, func.max(AppEnvMApping.deploy_date).label('maxdate'). \
          group_by(AppEnvMapping.app_id, AppEnvMapping.env_id). \
          subquery('maxlist')

applist = session.query(Application). \
          join(AppEnvMapping).\
          join(maxlist, and_(AppEnvMapping.id == maxlist.c.id, AppEnvMapping.deploy_date == maxlist.c.maxdate).\
          join(Environment).\
          all()

But what I get is a full list as if I joined the tables without the subselect at all. I suspect it's because I add in join(AppEnvMapping), but if I don't add that join, the query fails with an error that the appenvmapping.id column doesn't exist, despite the relationship in the underlying classes. I've tried using the relationship names, but I can't seem to figure out what syntax to use, or even if that's feasible.

I'd love any hints anyone can provide on how to resolve this. Thanks in advance!

Three table join with max value in SQLAlchemy

0 Answers