correlated subquery as column_property can't be queried independently?

[niuji...@gmail.com]

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?

[Mike Bayer]

try calling:

query(C.symbol_from_a).select_from(C)

--

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.

[niuji...@gmail.com]

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.

[Mike Bayer]

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>).

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/8549e3f3-11fb-47e1-b86a-270fecce8caan%40googlegroups.com.

[niuji...@gmail.com]

This is very helpful.

How to return a list of scalar values in this case? Now the query returns a list of tuples, and each tuple only has one value, which is what actually needed. Is there a parameter to return a series of scalar value like this?

[Mike Bayer]

if you use 1.4 /2.0 querying style you can call upon the scalars() method of the result

result = session.execute(select(MyClass.attr))

elements = result.scalars().all()

or

result = session.scalars(select(...))

elements = result.all()

otherwise you can just iterate like this:

elements = [e for e, in sess.query(MyClass.attr)]

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/b2059a01-5c3f-455c-aa3c-44590fe2f166n%40googlegroups.com.