filter by row_number in sqlalchemy

Question

how can I filter the row_number ==1 in the following query:

query = session.query(Foo, func.row_number().over(
        partition_by=Foo.foo_field, order_by=desc(Foo.foo_date_time)).label("row_number"))

    query = query.filter(Foo.time_key <= time_key)
    # query = query.filter(row_number == 1)
    result = query.all()

Answer 1

It can also be done using a subquery:

sub_query = session.query(
Foo,
func.row_number().over(partition_by=Foo.foo_field, order_by=desc(Foo.foo_date_time)).label("row_number")
)
sub_query = sub_query.filter(Foo.time_key <= time_key).subquery()
query = session.query(sub_query).filter(sub_query.c.row_number == 1)

It basically generates an equivalent SQL Statement as adding the row_number as a column.

Compiling the statement.

sub_query = session.query(
Foo,
func.row_number().over(partition_by=Foo.foo_field, order_by=desc(Foo.foo_date_time)).label("row_number")
)
sub_query = sub_query.filter(Foo.time_key <= time_key).subquery()
query = session.query(sub_query).filter(sub_query.c.row_number == 1)

str(query.statement.compile())

Will generate the following:

-- SubQuery function
SELECT anon_1.time_key, 
       anon_1.foo_field, 
       anon_1.foo_date_time, 
       anon_1.row_number 
FROM (
        SELECT  foo.time_key AS time_key,
                foo.foo_field AS foo_field, 
                foo.foo_date_time AS foo_date_time, 
                row_number() OVER (PARTITION BY foo.foo_field ORDER BY foo.foo_date_time DESC) AS row_number 
        FROM foo 
        WHERE foo.time_key <= %(time_key_1)s
    ) AS anon_1 
WHERE anon_1.row_number = %(row_number_1)s

UPDATE: Keep in mind that Query.from_self method is deprecated since SQLAlchemy 1.4 and will be removed in 2.0 according to the official documentation

In case there's someone using it, you can check the migration guide for this case