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 [email protected] <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 [email protected] <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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/189cbfb3-a3cb-4f8b-ab87-9f2c804d4ac9%40googlegroups.com.
