As a complete tangent, is there a better way to specify the join
criteria, since the orm already knows the fk relationship?
Seems wrong to re-specify it here:
select([func.sum(od_alias.c.qtyordered * od_alias.c.saleprice)],
orders_table.c.orderid==od_alias.c.orderid
)
Thanks in advance...
On May 3, 8:47 am, Kent <[email protected]> wrote:
> The following script works as expected (also in 0.5.8) with
> use_ansi=True. However, with use_ansi=False, the SQL is ill-formed:
>
> ===============================================================
> from sqlalchemy import *
> from sqlalchemy.orm import *
>
> engine = create_engine('oracle://arc:a...@localhost:1521/xe?
> use_ansi=False',echo=True)
> metadata = MetaData()
> Session = sessionmaker(bind=engine)
> session = Session()
>
> orders_table = Table("orders", metadata,
> Column("orderid", Unicode, primary_key=True)
> )
>
> orderdetails_table = Table("orderdetails",metadata,
> Column("orderid", Unicode, ForeignKey('orders.orderid'),
> primary_key=True),
> Column("lineid", Integer, primary_key=True),
> Column("saleprice", Numeric, nullable=False),
> Column("qtyordered",Numeric)
> )
>
> class Order(object):
> pass
>
> class OrderDetail(object):
> pass
>
> order_mapper = mapper(Order, orders_table,
> properties=dict(orderdetails=relation(OrderDetail,
> cascade='all,delete-orphan',
> single_parent=True,
> lazy=False,
> backref=backref('parentorder',
> cascade='refresh-expire,expunge'))))
>
> # ----------- totalsale -----------
> # note, I needed to add aliases because if you join with these tables
> in the rest of the query,
> # we need it to be un-ambiguous
> od_alias=orderdetails_table.alias('od__a')
> order_mapper.add_property('totalsale',
> # totalsale is an inline view column
> column_property(
> select([func.sum(od_alias.c.qtyordered *
> od_alias.c.saleprice)],
> orders_table.c.orderid==od_alias.c.orderid
> ).label('totalsale')))
>
> orderdetail_mapper = mapper(OrderDetail, orderdetails_table)
>
> #metadata.create_all(engine)
>
> o=session.query(Order).all()
> ===============================================================
>
> Expected SQL (use_ansi=True):
> -------------------------------------------
> 2010-04-30 21:09:10,359 INFO sqlalchemy.engine.base.Engine.0x...9450
> SELECT orders.orderid AS orders_orderid, (SELECT sum(od__a.qtyordered
> * od__a.saleprice) AS sum_1
> FROM orderdetails od__a
> WHERE orders.orderid = od__a.orderid) AS totalsale,
> orderdetails_1.orderid AS orderdetails_1_orderid,
> orderdetails_1.lineid AS orderdetails_1_lineid,
> orderdetails_1.saleprice AS orderdetails_1_saleprice,
> orderdetails_1.qtyordered AS orderdetails_1_qtyordered
> FROM orders LEFT OUTER JOIN orderdetails orderdetails_1 ON
> orders.orderid = orderdetails_1.orderid
> 2010-04-30 21:09:10,360 INFO sqlalchemy.engine.base.Engine.0x...9450
> {}
>
> SQL with use_ansi=False:
> ------------------------------------------
> sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-00936: missing
> expression
> 'SELECT orders.orderid AS orders_orderid, (SELECT
> sum(od__a.qtyordered * od__a.saleprice) AS sum_1 \nFROM orderdetails
> od__a \nWHERE orders.orderid = od__a.orderid AND ) AS totalsale,
> orderdetails_1.orderid AS orderdetails_1_orderid,
> orderdetails_1.lineid AS orderdetails_1_lineid,
> orderdetails_1.saleprice AS orderdetails_1_saleprice,
> orderdetails_1.qtyordered AS orderdetails_1_qtyordered \nFROM orders,
> orderdetails orderdetails_1 \nWHERE orders.orderid =
> orderdetails_1.orderid(+)' {}
>
> Note the " AND )" before "AS totalsale" is causing the missing
> expression database error.
>
> --
> 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
> athttp://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.
