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
On Jul 19, 2012, at 6:22 PM, Alessandro wrote:
> 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.
>
> --
> 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 [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
