I have a problem with query by ORM SQLAlchemy.
I need to write subquery like this
SELECT DISTINCT ON (e.type) e.type AS e_type, e.num AS e_num
FROM e ORDER BY e.type
by ORM SQLAlchemy, but when I've used .subquery().alias("q") in my query, DISTINCT ON was compiled to DISTINCT. Why and how am I fix it?
Without .subquery():
db.session.query(E.type, E.num).distinct(E.type).order_by(E.type)
compile to
SELECT DISTINCT ON (e.type) e.type AS e_type, e.num AS e_num FROM e ORDER BY e.type
With .subquery():
db.session.query(E.type, E.num).distinct(E.type).order_by(E.type).subquery().alias("q")
compile to
SELECT DISTINCT e.type AS e_type, e.num AS e_num FROM e ORDER BY e.type
