Need help in implementing a window function in sqlalchemy

Question

I am trying to implement this window function:

it is grouped by portfolio_id.
within each group, find the latest version.
returns the records with the latest version within each group.

I tried to implement it like this in SQLAlchemy:

 subquery = session.query(
        MyTable,
        func.row_number().over(
          partition_by=MyTable.portfolio_id,
          order_by=MyTable.version.desc()).label("row_number")
      ).subquery()
      LOG.info("subquery is {}".format(subquery))

      current_config = session.query(subquery).filter(subquery.c.row_number <= 1)

But the log shows that the query is this one:

SELECT anon_1.id AS anon_1_id,
    anon_1.portfolio_id AS anon_1_portfolio_id,
    anon_1.portfolio_name AS anon_1_portfolio_name,
    anon_1.version AS anon_1_version,
    anon_1.created_at AS anon_1_created_at,
    anon_1.last_updated_at AS anon_1_last_updated_at,
    anon_1.last_updated_by AS anon_1_last_updated_by,
    anon_1.config AS anon_1_config,
    anon_1.row_number AS anon_1_row_number
FROM (
    SELECT config_table_development.id AS id,
        config_table_development.portfolio_id AS portfolio_id,
        config_table_development.portfolio_name AS portfolio_name,
        config_table_development.version AS version,
        config_table_development.created_at AS created_at,
        config_table_development.last_updated_at AS last_updated_at,
        config_table_development.last_updated_by AS last_updated_by,
        config_table_development.config AS config,
        row_number() OVER (
            PARTITION BY config_table_development.portfolio_id ORDER BY config_table_development.version DESC
            ) AS row_number
    FROM config_table_development
    ) AS anon_1
WHERE anon_1.row_number <= ?

Where definitely we don't expect WHERE anon_1.row_number <= ? and we should expect WHERE anon_1.row_number <= 1. How could I fix it?

Gord Thompson · Accepted Answer · 2022-03-15 00:21:09Z

2

+50

The SQL statement in the question is the parameterized version of the statement and

WHERE anon_1.row_number <= ?

shows the where clause with its parameter placeholder. What the question does not show is the parameter value which would appear immediately afterward in the log:

2022-03-14 18:15:26,664 INFO … SELECT anon_1.id AS anon_1_id, …
WHERE anon_1.row_number <= ?
2022-03-14 18:15:26,665 INFO … [generated in 0.00033s] (1,)

That's where the 1 comes from.

answered Mar 15, 2022 at 0:21

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Need help in implementing a window function in sqlalchemy

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related