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 sqlalchemy+unsubscr...@googlegroups.com.
>> 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 sqlalchemy+unsubscr...@googlegroups.com.
> 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/1a45b894-93f0-4be1-b13b-30123a038ad1%40www.fastmail.com.
