subquery as column

[Alessandro]

I have a very simple case: two mapped classes, Head and Row, linked with the "id_head" id column. This is the primary key for the Head, while "id_row" is the primary key for the Row table.

I'm not able to create the following subqueries:

select 

  HEAD.id_head,

  (select ROW.column_bla_bla from ROW where ROW.id_head=HEAD.id_head order by ROW.id_row limit 0,1) as a_column_from_row,

  (select ROW.id_row from ROW where ROW.id_head=HEAD.id_head order by ROW.id_row limit 0,1) as first_id_row

from HEAD

The subqueries are inside the selected columns.

I'm using the last sqlalchemy version with MySql.

Thanks for your help

Alessandro

PS: I can get the same result with a different subquery, but I don't like it because it seems to me more complex: a subquery get the max_row_id and the min_row_id for each head_id, then I join it with HEAD, ROW as ROW_A and ROW as ROW_B and I get the columns I want.

[Michael Bayer]

from sqlalchemy import Column, Integer

from sqlalchemy.orm import Session

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Head(Base):

    __tablename__ = "head"

    id_head = Column(Integer, primary_key=True)

class Row(Base):

    __tablename__ = "row"

    id_row = Column(Integer, primary_key=True)

    id_head = Column(Integer)

    column_bla_bla = Column(Integer)

s = Session()

a_column_from_row = s.query(Row.column_bla_bla).\

                        filter(Row.id_row == Head.id_head).\

                        order_by(Row.id_row).\

                        limit(1).label("a_column_from_row")

first_id_row = s.query(Row.id_row).\

                        filter(Row.id_head == Head.id_head).\

                        order_by(Row.id_row).\

                        limit(1).label("first_id_row")

q = s.query(Head.id_head, a_column_from_row, first_id_row)

print q

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/VPIZtHYZzOoJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Alessandro]

Yes,I know, so simple... but it didn't work for me as far I didn't set the "label" for the internal select.

I try it many times, but always without it; I didn't know it was mandatory.

Thank you.

[Michael Bayer]

SQLAlchemy has two general classes of SQL construct - the FromClause and the ColumnElement.   A FromClause is a thing that goes in the FROM list of a SELECT statement, whereas a ColumnElement goes into the columns clause, WHERE, ORDER BY, GROUP BY, and ON sections of a SELECT statement.    A FromClause represents a "set of rows" whereas ColumnElement represents "a column in a row".   

So with the SELECT statement in SQL, we have a funny overlap of these two behaviors.   A SELECT statement normally is a "set of rows" - we can SELECT from a SELECT, as in a JOIN:

SELECT * FROM

   mytable JOIN (SELECT id from othertable WHERE foo=bar) AS mysubquery ON mytable.id=mysubquery.id

Above, you can see "mysubquery" is what we often call a "derived table".   It acts just like a table in the statement but gets its data from an embedded SELECT.  So far so good.

But there's another class of "subquery" in SQL, which is when we use a SELECT statement not in the FROM clause, but in the columns or WHERE clause:

SELECT mytable.id FROM

   mytable WHERE mytable.current_other_id=(SELECT id from othertable WHERE othertable.id=mytable.other_id)

When you run a query like that, the embedded SELECT is evaluated in a "scalar" context - if that subquery returned more than one column or row, the database itself raises an error.

So in SQLAlchemy, the difference between a select() that's used as a FromClause and one as a ColumnElement often needs to be stated specifically.   If you use an expression like "somecolumn == someselect", it will figure out that "someselect" should be evaluated in a scalar context.  But there are some cases where we accept FromClause and ColumnElement objects equally, namely the select() function and the session.query() method:

s = select([sometable])

vs. 

s = select([sometable.c.x, sometable.c.y])

Query has the same thing going on .

So if "sometable" is actually a select() object itself, it makes sense that by default select() and Query() will interpret this FromClause as what it is, a FromClause which should be expanded into its list of columns.

So when you pass a FromClause to select() or Query(), in order for it to be treated as the less common scalar subquery, you need to explicitly cast it as a ColumnElement, which is achieved via the as_scalar() or label() methods:

s = select([somestatement.as_scalar()])

or 

s = select([somestatement.label("foo")])

I hope this clears up some of the rationale behind this particular API.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/sQWv1yGoC8gJ.