Mike, thanks a lot for your help! Sorry for over-distilled example, I'm adding some context to (maybe) make the issue more searchable. Your guess is on point: I'm trying to select two querysets from the same table. One of them should be sorted by `modified_at` column while the other should use `created_at` column. Then the resulting query is augmented with row_number like `SELECT ..., row_number() OVER (ORDER BY order_by) FROM <union>` and used in `select_entity_from`.
Using constructed columns like you suggested did the trick. On Saturday, November 30, 2019 at 2:28:07 AM UTC+3, Mike Bayer wrote: > > quick and dirty I would just manufacture that column so that it has no > Python-side correspondence: > > from sqlalchemy import column > > select_entity_from( > select([Article, column('modified_at').label('order_by')]), > select([Article, column('created_at').label('order_by')]), > ).order_by("order_by") # im assuming this is what you are actually doing > > also this *might* be different if you tried github master / 1.4 where > there have been changes to how dupe columns are handled > > > On Fri, Nov 29, 2019, at 6:20 PM, Mike Bayer wrote: > > > > On Fri, Nov 29, 2019, at 1:53 PM, Mikhail Knyazev wrote: > > Hi, Mike. > I run into strange behavior, see example below. In short, ORM does not > select column from nested union when a label is assigned to the column. > This results in mixed up attributes of a mapped object. > > > hi > > the "modified_at" / "created_at" columns are being repeated in each SELECT > in a mixed way and I would guess this is confusing the ORM, which is likely > using that last column to populate "modified_at" in the entity. the > query as given doesn't seem to be using this "order_by" column and it's > also selecting dupes so I believe we have to resolve for a modified XY > problem here [1] with the modification that "user doesn't know how to do > Y" should read "SQLAlchemy can't really do Y without some trickery if at > all" .... What is the *actual* thing you need to do ? > > [1] http://xyproblem.info/ > > > from datetime import datetime > > from sqlalchemy import Column, DateTime, Integer, create_engine, select, > union > from sqlalchemy.ext.declarative import declarative_base > from sqlalchemy.orm import sessionmaker > > Base = declarative_base() > > > class Article(Base): > __tablename__ = 'article' > id = Column(Integer, primary_key=True) > created_at = Column(DateTime) > modified_at = Column(DateTime) > > > engine = create_engine('sqlite:///:memory:', echo=True) > Base.metadata.create_all(engine) > Session = sessionmaker(bind=engine) > session = Session() > > dt1, dt2 = datetime(2011, 1, 1), datetime(2012, 2, 2) > dt3, dt4 = datetime(2013, 3, 3), datetime(2014, 4, 4) > > article1 = Article(created_at=dt1, modified_at=dt2) > article2 = Article(created_at=dt3, modified_at=dt4) > session.add_all((article1, article2)) > session.commit() > session.expunge_all() > > query = ( > session.query(Article) > .select_entity_from( > union( > select((Article, Article.modified_at.label('order_by'))), > select((Article, Article.created_at.label('order_by'))), > ) > ) > .order_by(Article.id) > ) > > article1, article2 = query.all() > > print('article1') > print('\tcreated_at', article1.created_at, '\t\texpected', dt1.isoformat > ()) > print('\tmodified_at', article1.modified_at, '\texpected', dt2.isoformat > ()) > print('article2') > print('\tcreated_at', article2.created_at, '\t\texpected', dt3.isoformat > ()) > print('\tmodified_at', article2.modified_at, '\texpected', dt4.isoformat > ()) > > # article1 > # created_at 2011-01-01 00:00:00 expected > 2011-01-01T00:00:00 > # modified_at 2011-01-01 00:00:00 expected > 2012-02-02T00:00:00 > # article2 > # created_at 2013-03-03 00:00:00 expected > 2013-03-03T00:00:00 > # modified_at 2013-03-03 00:00:00 expected > 2014-04-04T00:00:00 > > Rendered SQL query: > > SELECT anon_1.id AS anon_1_id, anon_1.order_by AS anon_1_order_by, > anon_1.modified_at > AS anon_1_modified_at > FROM (SELECT article.id AS id, > article.created_at AS created_at, -- this field is not > selected by outer SELECT > article.modified_at AS modified_at, > article.created_at AS order_by > FROM article > UNION > SELECT article.id AS id, > article.created_at AS created_at, -- this field is not > selected by outer SELECT > article.modified_at AS modified_at, > article.modified_at AS order_by > FROM article) AS anon_1 > ORDER BY anon_1.id > > > > > > -- > 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 sqlal...@googlegroups.com <javascript:>. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/68ac9acc-9c06-4c6d-981d-07df7a7a62c6%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/68ac9acc-9c06-4c6d-981d-07df7a7a62c6%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > > -- > 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 sqlal...@googlegroups.com <javascript:>. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/64f373ed-c574-4fe7-b4de-f4fdb1762160%40www.fastmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/64f373ed-c574-4fe7-b4de-f4fdb1762160%40www.fastmail.com?utm_medium=email&utm_source=footer> > . > > > -- 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 sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/189cbfb3-a3cb-4f8b-ab87-9f2c804d4ac9%40googlegroups.com.