Hi,
I have this function which returns a list of product variants and the first
"seasons" they were introduced in (might be multiple on the same date, so
I'm ranking by from_date in a CTE):
*In [190]: def variants_first_seasons_query():*
* ...: ranking_query = (*
* ...: db.session.query(*
* ...: db.ProductVariant.id.label('productvariant_id'),*
* ...: db.ProductSeason.id.label('productseason_id'),*
* ...: db.sqla.func.rank()*
* ...: .over(*
* ...: partition_by=db.ProductVariant.id,*
* ...: order_by=db.ProductSeason.from_date*
* ...: )*
* ...: .label('ranking')*
* ...: )*
* ...: .select_from(db.ProductSeason)*
* ...: .join(db.ProductSeason.skus)*
* ...: .join(db.ProductSKU.variant)*
* ...: .order_by(db.ProductVariant.id)*
* ...: .distinct()*
* ...: )*
* ...: ranking_query_cte = db.sqla.aliased(ranking_query.cte(),
name='ranking_query')*
* ...: return (*
* ...: db.session.query(*
* ...: ranking_query_cte.c.productvariant_id,*
* ...: ranking_query_cte.c.productseason_id*
* ...: )*
* ...: .select_from(ranking_query_cte)*
* ...: .filter(ranking_query_cte.c.ranking == 1)*
* ...: )*
* ...:*
*In [191]: q = variants_first_seasons_query()*
*In [192]: print(q)*
*WITH anon_1 AS*
*(SELECT DISTINCT productvariant.id AS productvariant_id, productseason.id
AS productseason_id, rank() OVER (PARTITION BY productvariant.id ORDER BY
productseason.from_date) AS ranking*
*FROM productseason JOIN productseason_productsku AS
productseason_productsku_1 ON productseason.id =
productseason_productsku_1.productseason_id JOIN productsku ON
productsku.id = productseason_productsku_1.productsku_id JOIN
productvariant ON productvariant.id = productsku.variant_id ORDER BY
productvariant.id)*
* SELECT ranking_query.productvariant_id AS
ranking_query_productvariant_id, ranking_query.productseason_id AS
ranking_query_productseason_id*
*FROM anon_1 AS ranking_query*
*WHERE ranking_query.ranking = %(ranking_1)s*
Later in my code I want to further filter on the seasons that I care about
in the outer query, but as there's no standard declarative model in the
selectable I don't think I can pass the model's attribute in the filter,
but instead I figure I should go through *.statement.c* of the query:
*In [193]: SEASON_IDS = [45]*
*In [194]: q = q.filter(q.statement.c.productseason_id.in_(SEASON_IDS))*
*In [195]: print(q)*
*WITH anon_1 AS*
*(SELECT DISTINCT productvariant.id AS productvariant_id, productseason.id
AS productseason_id, rank() OVER (PARTITION BY productvariant.id ORDER BY
productseason.from_date) AS ranking*
*FROM productseason JOIN productseason_productsku AS
productseason_productsku_1 ON productseason.id =
productseason_productsku_1.productseason_id JOIN productsku ON
productsku.id = productseason_productsku_1.productsku_id JOIN
productvariant ON productvariant.id = productsku.variant_id ORDER BY
productvariant.id)*
* SELECT ranking_query.productvariant_id AS
ranking_query_productvariant_id, ranking_query.productseason_id AS
ranking_query_productseason_id*
*FROM anon_1 AS ranking_query, (SELECT ranking_query.productvariant_id AS
productvariant_id, ranking_query.productseason_id AS productseason_id*
*FROM ranking_query*
*WHERE ranking_query.ranking = %(ranking_1)s)*
*WHERE ranking_query.ranking = %(ranking_1)s AND productseason_id IN
(%(productseason_id_1)s)*
*In [196]: q.count()*
*...*
*ProgrammingError: (psycopg2.ProgrammingError) subquery in FROM must have
an alias*
*LINE 5: FROM anon_1 AS ranking_query, (SELECT ranking_query.productv...*
*...*
No success (as you can see the red part above is the reason).
However I have found a workaround which produces exactly the query I'm
looking for:
*In [197]: db.session.rollback()*
*In [198]: q = variants_first_seasons_query()*
*In [199]: col = [c['expr'] for c in q.column_descriptions if
c['expr'].name == 'productseason_id'][0*
* ...: ]*
*In [200]: q = q.filter(col.in_(SEASON_IDS))*
*In [201]: print(q)*
*WITH anon_1 AS*
*(SELECT DISTINCT productvariant.id AS productvariant_id, productseason.id
AS productseason_id, rank() OVER (PARTITION BY productvariant.id ORDER BY
productseason.from_date) AS ranking*
*FROM productseason JOIN productseason_productsku AS
productseason_productsku_1 ON productseason.id =
productseason_productsku_1.productseason_id JOIN productsku ON
productsku.id = productseason_productsku_1.productsku_id JOIN
productvariant ON productvariant.id = productsku.variant_id ORDER BY
productvariant.id)*
* SELECT ranking_query.productvariant_id AS
ranking_query_productvariant_id, ranking_query.productseason_id AS
ranking_query_productseason_id*
*FROM anon_1 AS ranking_query*
*WHERE ranking_query.ranking = %(ranking_1)s AND
ranking_query.productseason_id IN (%(productseason_id_1)s)*
*In [202]: q.count()*
*Out[202]: 165*
Now my question is, how can accomplish this cleanly, without going through
*.column_descriptions*?
Any suggestions would be greatly appreciated ❤
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
