it has to do with how SQLAlchemy determines the FROM list in a select()
statement.
if you say select(table.c.id), it knows that "table" is the thing to select
"from".
however, if you say select(select(...).correlate(...).scalar_subquery()),
that's assuming it's a SELECT from a scalar subquery, which normally does not
imply a FROM clause.
In this case, that you are saying correlate(C), we could argue that *does*
imply a FROM clause of some kind, but SQLAlchemy doesn't make that leap right
now. You might have said correlate(C, D) or correlate_except(Q) and then it's
not as clear what the scalar subquery would be selected FROM.
going further, there *could* be some new kind of API where a scalar subquery
could suggest a FROM clause that should be used when this subquery is selected
FROM. that would allow your column_property() to be built up such that it
would be "self sufficient" when queried by itself. but again we don't have
that right now.
with all those options exhausted, for SQLAlchemy to be given
select(select().scalar_subquery()), and then to produce "SELECT * FROM (SELECT
....) FROM <some table>", that FROM part has to be added on explicitly, i.e.
select(select().scalar_subuqery()).select_from(<some table>).
On Wed, Nov 10, 2021, at 2:35 PM, niuji...@gmail.com wrote:
> This works! Could you explain a little about this differences by using
> select_from here? I think this is very important and useful, really want to
> learn it right.
>
> On Wednesday, November 10, 2021 at 5:55:44 AM UTC-8 Mike Bayer wrote:
>> __
>> try calling:
>>
>> query(C.symbol_from_a).select_from(C)
>>
>>
>>
>> On Wed, Nov 10, 2021, at 4:50 AM, niuji...@gmail.com wrote:
>>> class A(Base):
>>> primary_id = Column(Integer, prirmary_key=True)
>>> some_A_marker = Column(String)
>>>
>>> class B(Base):
>>> primary_id = Column(Integer, primary_key=True)
>>> referencing_A_id = Column(Integer, ForeignKey(A.primary_id))
>>>
>>> class C(Base):
>>> primary_id = Column(Integer, primary_key=True)
>>> basedOn_B_id = Column(Integer, ForeignKey(B.primary_id))
>>> symbol_from_A = column_property(
>>>
>>> sasql.select(A.some_A_marker).select_from(A).join(B).where(B.primary_id ==
>>> C.basedOn_B_id).correlate(C).scalar_subquery().label("symbol_from_A")
>>> )
>>>
>>>
>>> When I query this highlighted column_property along with the whole object,
>>> it works fine:
>>>
>>> >>> sess.query(C, C.symbol_from_A).all()
>>>
>>> However, when I query this column_property alone, it doesn't work:
>>>
>>> >>>sess.query(C.symbol_from_A).distinct().all()
>>>
>>> I noticed that somehow the correlated subquery wasn't executed property
>>> when query that column_property independently.
>>>
>>> How to solve this?
>>>
>>>
>>>
>>> --
>>> 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+...@googlegroups.com.
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy/f7a0c360-ada2-456b-b02a-498ca50334f3n%40googlegroups.com
>>>
>>> <https://groups.google.com/d/msgid/sqlalchemy/f7a0c360-ada2-456b-b02a-498ca50334f3n%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/8549e3f3-11fb-47e1-b86a-270fecce8caan%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/8549e3f3-11fb-47e1-b86a-270fecce8caan%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/b59c73fc-a147-45b7-a184-69e49125354d%40www.fastmail.com.
