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 [email protected]. > 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/64f373ed-c574-4fe7-b4de-f4fdb1762160%40www.fastmail.com.
