Can't convert this SQL String (with VALUES ... AS) to SQLAlchemy Code

Question

The SQL query I have can identify the Max Edit Time from the 3 tables that it is joining together:

Select Identity.SSN, Schedule.First_Class, Students.Last_Name,
(SELECT Max(v)
    FROM (VALUES (Students.Edit_DtTm), (Schedule.Edit_DtTm),
    (Identity.Edit_DtTm)) AS value(v)) as [MaxEditDate]
FROM Schedule
LEFT JOIN Students ON Schedule.stdnt_id=Students.Student_Id
LEFT JOIN Identity ON Schedule.std_id=Identity.std_id

I need this to be in SQLAlchemy so I can reference the columns being used elsewhere in my code. Below is the simplest version of what i'm trying to do but it doesn't work. I've tried changing around how I query it but I either get a SQL error that I'm using VALUES incorrectly or it doesn't join properly and gets me the actual highest value in those columns without matching it to the outer query

max_edit_subquery = sa.func.values(Students.Edit_DtTm, Schedule.Edit_DtTm, Identity.Edit_DtTm)
base_query = (sa.select([Identity.SSN, Schedule.First_Class, Students.Last_Name,
                            (sa.select([sa.func.max(self.max_edit_subquery)]))]).
                            select_from(Schedule.__table__.join(Students, Schedule.stdnt_id == Students.stdnt_id).
                            join(Ident, Schedule.std_id == Identity.std_id)))

Answer 1

I had the similar problem and i solved using the below approach. I have added the full code and resultant query. The code was executed on the MSSQL server. I had used different tables and masked with the tables and columns used in your requirement in the below code snippet.

from sqlalchemy import *
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.types import String
from sqlalchemy.sql.expression import FromClause


class values(FromClause):
    def __init__(self, *args):
        self.column_names = args


@compiles(values)
def compile_values(element, compiler, asfrom=False, **kwrgs):
    values = "VALUES %s" % ", ".join("(%s)" % compiler.render_literal_value(elem, String()) for elem in element.column_names)
    if asfrom:
        values = "(%s)" % values
    return values


base_query = self.db_session.query(Schedule.Edit_DtTm.label("Schedule_Edit_DtTm"),
                                 Identity.Edit_DtTm.label("Identity_Edit_DtTm"),        
                                 Students.Edit_DtTm.label("Students_Edit_DtTm"),
                                 Identity.SSN                            
                                 ).outerjoin(Students, Schedule.stdnt_id==Students.Student_Id
                                 ).outerjoin(Identity, Schedule.std_id==Identity.std_id).subquery()

values_at_from_clause = values(("Students_Edit_DtTm"), ("Schedule_Edit_DtTm"), ("Identity_Edit_DtTm")
                                ).alias('values(MaxEditDate)')

get_max_from_values = self.db_session.query(func.max(text('MaxEditDate'))
                                            ).select_from(values_at_from_clause)

output_query = self.db_session.query(get_max_from_values.subquery()
                                     ).label("MaxEditDate")

**print output_query**


SELECT
    anon_1.Schedule_Edit_DtTm AS anon_1_Schedule_Edit_DtTm,
    anon_1.Students_Edit_DtTm AS anon_1_Students_Edit_DtTm,
    anon_1.Identity_Edit_DtTm AS anon_1_Identity_Edit_DtTm,
    anon_1.SSN AS anon_1_SSN
    (
        SELECT
            anon_2.max_1
        FROM
            (
                SELECT
                    max( MaxEditDate ) AS max_1
                FROM
                    (
                    VALUES (Students_Edit_DtTm),
                           (Schedule_Edit_DtTm),
                           (Identity_Edit_DtTm)
                       ) AS values(MaxEditDate)
            ) AS anon_2
    ) AS MaxEditDate
FROM
    (
        SELECT
            Schedule.Edit_DtTm AS Schedule_Edit_DtTm,
            Students.Edit_DtTm AS Students_Edit_DtTm,
            Identity.Edit_DtTm AS Identity_Edit_DtTm,
            Identity.SSN AS SSN
        FROM
            Schedule WITH(NOLOCK)
        LEFT JOIN Students WITH(NOLOCK) ON
            Schedule.stdnt_id==Students.Student_Id
        LEFT JOIN Identity WITH(NOLOCK) ON
            Schedule.std_id==Identity.std_id
    ) AS anon_1

Answer 2

I am not an expert at SQLAlchemy but you could exchange VALUES with UNION ALL:

Select Identity.SSN, Schedule.First_Class, Students.Last_Name,
(SELECT Max(v)
    FROM (SELECT Students.Edit_DtTm AS v
         UNION ALL SELECT Schedule.Edit_DtTm
         UNION ALL SELECT Identity.Edit_DtTm) s
   ) as [MaxEditDate]
FROM Schedule
LEFT JOIN Students ON Schedule.stdnt_id=Students.Student_Id
LEFT JOIN Identity ON Schedule.std_id=Identity.std_id;

Another approach is to use GREATEST function (not available in T-SQL):

Select Identity.SSN, Schedule.First_Class, Students.Last_Name,
    GREATEST(Students.Edit_DtTm, Schedule.Edit_DtTm,Identity.Edit_DtTm)
    as [MaxEditDate]
FROM Schedule
LEFT JOIN Students ON Schedule.stdnt_id=Students.Student_Id
LEFT JOIN Identity ON Schedule.std_id=Identity.std_id;

I hope that it will help you to translate it to ORM version.