Hi all,
I'm using MySQL 5.1 for our database, Python 2.7.2 and SqlAlchemy
0.7.1, and I'm having trouble figuring out how to use a subquery to
get a single record from a one-to-many relationship with the main
query. Here's my code:
note_stmt = session.query(CustomerNote.note) \
.filter(CustomerNote.order_id ==
OrderModel.order_id) \
.order_by(desc(CustomerNote.created)) \
.limit(1)
q = session.query(OrderModel.order_date, \
OrderModel.reference, \
OrderModel.order_id,\
OrderItem.project_token, \
OrderItem.book_id, \
(OrderItem.gross +
OrderItem.shipping_price).label("gross"), \
OrderItem.qty,
note_stmt.subquery().alias("customer_note")) \
.join((History, History.id == OrderModel.order_id)) \
.join((OrderItem, OrderItem.order_id ==
OrderModel.order_id)) \
.filter(History.note == params["exact_note"]) \
.filter(History.type == params["type"]) \
.filter(History.station == params["station"]) \
.filter(OrderModel.state_id == params["state_id"]) \
.filter(OrderModel.client_type_id == 3) \
.q.slice(start, start + length)
retval = q.all()
The q query is the main query and there exists one-to-one
relationships with the two joins going on there. The note_stmt
subquery has a one-to-many relationship with OrderModel between the
order_id column of the two tables. I'm trying to get the latest
CustomerNote included as a column in the main query
(alias("customer_note")), but what I'm getting is some random (?) note
because I don't think the subquery is being correlated with the main
query with CustomerNote.order_id == OrderModel.order_id. I guess I
don't understand how to set that up.
Any help or pointers would be greatly appreciated! Thanks!
Doug
--
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.
